How are your relationships set up?
Might they be?
Contacts::_fkFamilyID = Families::__pkFamilyID
If so, you can add another Tutorial: What are Table Occurrences? of contacts like this:
Contacts::_fkFamilyID = Contacts|Spouse::_fkFamilyID AND
Contacts::__pkContactID ≠ Contacts|Spouse::_pkContactID AND
Contacts::constMarried = Contacts|Spouse::MaritalStatus
constMarried would be a calculation field that always returns the text "Married" or whatever next matches exactly to a "Married" marital status in the Marital staus field.
Then any data you need for a contact's spouse can be accessed by referring to fields in Contacts|Spouse.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
I appreciate the humor!!
SO, I have Contacts, Contact_Family, and Families.
Contacts::__pkContactid -< Contact_Family:: _fkContactID
Contact_Family::_fkFamilyID >- Families::__pkFamilyID
(I cant remember why it was setup that way)
What do I need to tweak in the table occurrence setup screen with this setup?
Also, for the constMarried field, what do I have to set as the calculation?
Thanks so much for all of your helpful responses on this forum!
(I cant remember why it was setup that way)
Can a contact be a member of more than one Family?
If so, it should be set up this way. If not, you don't need the join table between contacts and families.
I don't see why they would be a part of more than one family, but I don't want to go in to change it unless I would have to to make the above work.
I think I created it when I was creating a portal before and that's the only way I got it work, but I am not positive.
Any way to get the above to work with my current setup?
I've recommended that structure in quite a few threads here. The reason for such a structure is that in some circumstances, a contact could be a father of one family and a son in another. Or a child can be a member of two families--each headed by a different, separated/divorced parent.
If that's not what you need here, it adds a lot of unnecessary complications to the design of your database.
I need to be absolutely sure about this before moving forward as your "marital status" field may be better placed as a field in the join table instead of contacts.
I do not see that instance happening, as someone who is a father but also a son would only be the father of his own family, never a son of his birth family, for our purposes.
The Families table contains the family name associated with the family id, and the street info (as for our purposes, everyone in the family has the same address, or they will be a separate family if not).
I think it's best to keep the join field, as it seems like it would be a ton of work to eliminate it at this point, and I don't know what consequences there would be when eliminating it. I currently have portals, scripts to add families, etc. that rely on that join field. What are the steps I should take to un-complicate it and eliminate the join field, or should I even attempt?
Lastly, re: moving martial status to the join field, I was hoping to use this same concept, to duplicate the steps, to get parent e-mails and phone numbers on a child's page.
I was hoping it'd be a simple calculation field... guess i was wrong.
The presence of that unnecessary join table really complicates this. I agree that you can't just remove it. You'd have to carefully analyze your database and then proceed carefully with making the needed change while making lots of back ups. But this is just one example of how that added table and its relationships makes ongoing design changes needlessly more complex than they need to be.
If you move Marital Status to the Contact_Family table, you can set up the relationship I described earlier, but with the Contact_Family table instead of the Contacts table. The constMarried field would then also be defined in Contact_Family.
What are the steps to move it to the other table? or do I have to create it fresh?
Define the new field in the join table. Then you can use a looping script or a single Replace Field Contents to copy the data from the current field to the new field in the join table.
I doubt that would work. Why would you need that?
I have created a script that gets the Spouse that I need and can set the ExitScript value as the Spouse's name. If I could plug that result into a calculation field, it'd be exactly what I need.
But you can use Set Field to set a field with that value and not have to use such an indirect method to do that.
I ended up doing that, but it requires the script to be run, rather than just calculating it automatically.
Definitely a nice enough work around.
Thanks for all of the help!