1 2 Previous Next 16 Replies Latest reply on Oct 9, 2013 1:17 PM by philmodjunk

    Calculation To Get Spouse



      Calculation To Get Spouse


           So, currently my database has all individuals in Contacts.

           There is MaritalStatus which has Married/Single/Child.

           Individuals are grouped into the same families in the Families database.

           I am assuming I will need a calculation to get a spouse for an individual...

           in English... If MaritalStatus is "Married", get the other person with MartialStatus "Married" in the same Family.


        • 1. Re: Calculation To Get Spouse

               Well, I needed more than a calculation to get MY spouse...surprise

               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

          • 2. Re: Calculation To Get Spouse

                 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!

            • 3. Re: Calculation To Get Spouse

                        (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.

              • 4. Re: Calculation To Get Spouse

                     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?

                • 5. Re: Calculation To Get Spouse

                       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.

                  • 6. Re: Calculation To Get Spouse

                         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.


                    • 7. Re: Calculation To Get Spouse

                           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.

                      • 8. Re: Calculation To Get Spouse

                             What are the steps to move it to the other table? or do I have to create it fresh?

                        • 9. Re: Calculation To Get Spouse

                               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.

                          • 10. Re: Calculation To Get Spouse

                                 In a calculation field, is there any way to use a function such as Get(ScriptResult) ??

                            • 11. Re: Calculation To Get Spouse

                                   I doubt that would work. Why would you need that?

                              • 12. Re: Calculation To Get Spouse

                                     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.

                                • 13. Re: Calculation To Get Spouse

                                       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.

                                  • 14. Re: Calculation To Get Spouse

                                         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!

                                    1 2 Previous Next