I'm currently working on a database for a small event-planning business, specializing in green (environmentally friendly) weddings. In such affairs, it's important to understand who is related to whom, and how. Thus I've established a PPLinx (person-to-person links) table which contains 4 fields of interest: • PerSeq A (person sequence number for Person A, let's say Alan, the groom) • PerSeq B (person sequence number for Person B, let's say Betty, the mother of the groom) • Role A (the role that Person A plays in the relationship, in this case "son") • Role B (the role that Person B plays in the relationship, in this case "mom")
So I'm looking at Alan's record, in which there's a picker portal containing the names of potential matches. On each line of that portal is a "Set" button. Above the portal is a global "Default Role" field I can use to prepare to say that Alan is somebody's "son". When I click the "Set" button on Betty's line, it creates a new PPLinx record and fills in Alan's code (let's say 123) for PerSeq A, Betty's code (let's say 124) for PerSeq B, "son" for Role A, and the computed value "mom" for Role B. So far, so good. In a different portal in Alan's record (one wherein Alan's PerSeq = PPLinx::PerSeq A), I peer into PPLinx to see whom he's connected to. Sure enuf, that portal now shows "Betty: mom". Yay! Now we jump to Betty's record. Alas, it does not show "Alan: son". That's because Betty's ID is 124, which cannot find a match with a 124 in PerSeq A. 1st attempt at a fix: I created within PPLinx a "Both IDs" field, a calculation concatenating PerSeq A, a return character, and PerSeq B. I re-aimed the link from the "People" table so that People::PerSeq was now looking for a match with PPLinx::Both IDs rather than merely PPLinx::PerSeq A. Voilà! Alan's record still shows "Betty: mom", and Betty's record now shows ... um, "Betty: mom". Shoot. That's because the portal DISPLAYS Person B and Role B. I guess I could expand the portal so it shows BOTH Person A (+ Role A) AND Person B (+ Role B), but this seems remarkably inelegant, especially since whatever person I'm looking at would show up as Person A part of the time and Person B part of the time. What I'd really like is to have the portal for Alan not show any info on Alan at all, only info about all the people he's related to. Another possibility is to have the "Set" button create TWO records within PPLinx, 1 with Alan as Person A and Betty as Person B, and a 2nd with the order reversed. Again, this seems inelegant, especially given that there will come a time when these relationships change (notably "groom" —> "husband" and "bride" —> "wife"), and I'd like to have to fiddle with only a single PPLinx record to make this happen. Has anyone else encountered this situation? If so, how did you handle it?
