1 2 3 Previous Next 33 Replies Latest reply on Dec 5, 2013 11:41 AM by raphthepenguin

    Problems with one to many relationships


      Hey there,


      I am working on a people Database and once again I am stuck... I am trying to have an extra layout that is showing family members of a person, but also allow me to assign family members (as there is no other place for that in the database).


      First - Do relationships ALWAYS have to be shown in a portal? Or is it possible to have a relationship based layout?


      I made all the table occurences and fields. Then I tried my way through scripting:

      New window

      Go to layout...

      Now here I found the first problem... after trying different ways of doing my script. I have come so far that a new record (family) is created... The problem is that this is always the case when I click the button for the script. Meaning, even if the person already has a family it is again assigned a new one.

      The kf field is not filled and I think it might have to do with that. So it doesnt really have the relationship.

      How can i solve that. Before kf always somehow was filled (with the portal?)


      Then how can I have a search field where I can look for other people that I can assign to the Family?

      And finally How can I have it so if I go on a other family members list, it is the same family without having to assign them again?


      I know this are lots of questions I would really appreciate help?



        • 1. Re: Problems with one to many relationships

          To address your first problem you will need to add to your script a way of checking whether the person already exists and if so whether an associated family already exists, then branch the script according to the outcome of your check. You probably also need to make sure you are using a sound data structure.

          • 2. Re: Problems with one to many relationships
            Stephen Huston

            Regarding the issue of viewing related records on a layout without a portal:

            • Yes, you can place related fields directly on a layout without using a portal, however the resulting field will show only the value in the first related record via that portal.
            • What determines the first record is:
              a) If the relationship itself is sorted (as defined in the relationship graph) the first  record in that sort order which has a valid relationship will supply the value.
              b) If the relationship is not sorted, the value from the related record with the earliest creation timestamp will appear.
            • 3. Re: Problems with one to many relationships



              So if I understand right in my case I would be better of with a portal? As a family is likely to have more then one related records!


              How could I make a search button to add to the portal rows?

              • 4. Re: Problems with one to many relationships

                I am a little  confused because I never had to fil in a kf. Somehow the relationship always took care of that without my doing...

                So since that is not happening though I tried to just fill it with a script

                something like that


                If [IsEmpty (Contacts::_kf_famID)]

                     Go to Field [contacts_FAMILY::_kp_famID]

                     New Record

                     Go to Field [Contacts::_kf_famID]

                     Set Field [contacts_FAMILY::_kp_famID]

                End If


                So if a no kf is existing (no family related) a new record (new family) is build. That field is all auto filled and works fine.

                Then I thought I could connect via script the to keys (kf and kp).

                Script debugger is running the whole thing without any errors, but the field for the foreign key kf is always left empty and therefore on the next click a new record is build!


                What did I do wrong?


                • 5. Re: Problems with one to many relationships

                  From what I understand, I think you could solve both problems with the same adjustment to your database. You have probably got a self-join relationship that matches Contacts::_kf_famID = contacts_FAMILY::_kp_famID


                  If you set this realtionship to allow creation of related records this will have two effects:


                  1.     Your portal, based on this reltionship, will now have a blank row where you can add family members.

                  2.     When you do, the kf field will be automatically entered.

                  • 6. Re: Problems with one to many relationships
                    Stephen Huston

                    I'm not clear what you mean by "a search button to add to the portal rows."


                    How are you currently linking individuals to family records?

                    • 7. Re: Problems with one to many relationships



                      Note - This doesn't answer your specific questions, and may not be relevant or useful, in which case apologies for the intrusion. 


                      I also need to record family links, and fairly early on I found that simply having a family key to group records was too limiting, as families are rarely "self-contained" in this way.  I use a system of paired "relationship" records, which say, for example, that Homer is Bart's father (rec 1), and Bart is Homer's son (rec 2).  This gives the flexibility to define complex and interlinking families.  If this sounds like it may be useful, take a look at this post, which explains it in more detail.


                      Happy to help further if it's relevant.  If it's not, I'm sorry for the distraction!



                      • 8. Re: Problems with one to many relationships

                        Thanks guys for your help in this...


                        So I changed the layout from being a list view to a form view with a portal, as this makes it possible to show more then JUST the first related record!


                        I tried the option of allowing relationshipbased changes of records. The problem is that it will create a new record for the entered name.

                        What I try to do is that the shown family member allready is excisting and I "link" them together.


                        That is why I was thinking about it following way.

                        On the record I have a button called "family" if I click it a new window opens up showing the portal of that records related family. Then there would be a search function that allows me to search through existing records and add family members to the portal list.


                        I hope you understand my need and where I am stuck now!

                        Thanks alot!



                        A general question to how to work this forum. This post says its replied specific to Stephen. But its actually to both of you, do I have to link that somehow or is it fine the way it is?

                        • 9. Re: Problems with one to many relationships

                          On first look it looks complicated, but it seems like a valid option! Thank you. I have wrestled with the other option for a while now so Id like to keep going that direction for now. But I definately keep your post in mind if I am not coming to a satisfiying result I will have to try it out!

                          • 10. Re: Problems with one to many relationships

                            One way you could do wehat you want to do is to have two portals, one showing non-connected records, the other showing only family connections (obviously you would establish these two relationships first). Initially the first portal would show all records, unless they are already connected. You could then mark family connected records in the non-connected portal to move them accross into the family connections portal. The marking could best be achieved with a boolean field which drives the two relationships.

                            • 11. Re: Problems with one to many relationships

                              Indeed... unless you are talking genetics/geneology, families are an arbitrary grouping which need to be articulated for each relationship that exists. Sometimes I call them 'teams' or 'partnerships... cause it gets really confusing is you have a table called 'relationships'.


                              With genealogy, the relationships between all people are satisfied by recording Mother and Father. You get siblings when both your parents are the same as others' parents. You get children when you become their parent. Your grandparents are your parent's parents... and so on. Henry VIII may have had 6 wifes and 3 legitimate children... but each of those children had different parents and would be seen as his children but with no other technical relationships with his other wives. The problem comes when human intervention introduces new relationships... step-mum, half-sister etc. For these you need the relationships table...


                              - Lyndsay

                              • 12. Re: Problems with one to many relationships

                                Sorry, I do not really understand.

                                • 13. Re: Problems with one to many relationships
                                  Stephen Huston

                                  I think what "keywords" is suggesting is 2 portals, which could be side-by-side on a layout. One would show linked records. The other would rely on a separate relationship of ≠ based on the same key fields, or it could be a relationship with X links to all records, but filter the portal to show only those lacking the matching key.


                                  Then you place a button in the unlinked portal row to add the link value to the foreign key field of the portal row record, linking it. It would jump from the unlinked portal to the linked portal when the record is committed.

                                  • 14. Re: Problems with one to many relationships

                                    wow... this looks like so far being the easiest solution.


                                    Another approach i was looking at all day today would be from the FTS. They have an example where you can add people to a meeting via a person picker.

                                    So I was trying to rebuild the whole thing so you could add people to a family via a person picker...

                                    But it is really complex and so far I have not had a full success...

                                    1 2 3 Previous Next