12 Replies Latest reply on Apr 13, 2012 9:05 AM by philmodjunk

    Relationship Question



      Relationship Question


      I have a scouting database where I put in player scouting reports. In another table I have a club team information like club name and contact information  for the club etc. I want to connect this information to the scouting report so when I select a player and put his report on a alyout that his club information like team and coach name and cell phone can be connected.


      In the relationship chart below DT is the main table that has all the player information in it. AAU Coach Directory is where I have the club information , and Event participation is where I have the player scouting reports. How do I connect the AAU COach Directory and event participation table together so I can list the inforamtion in AAU COach Directory in layouts connected to the Event Participation table?


        • 1. Re: Relationship Question

          Looks to me like you alraedy have those relationships made.

          Just put the fields from your AAU table (like coach name, cell phone, etc.) on the layout based on Event Participation.

          I am wondering however, do you have your relationships based on Unique numbers? It looks to me like some of your relationships might be based on text fields.

          That's not the best idea.

          • 2. Re: Relationship Question

            99% of the time I do it numbered based, since every team is different named that I thought I coud make this text based. It hasn't been an issue, but now when I try to put the field in from the AAU table it doesn't give any information.

            • 3. Re: Relationship Question

              Is there a way to switch the relationship from text to number and not lose any information? 

              • 4. Re: Relationship Question

                Hey Mike,

                You can do it, if you create a new relationship for an ID number for coach (or coach's club), link by it to a new number field in your DT table. Then you can use Replace Field contents, first to update your AAU coach directory table with serial numbers and then a second time to copy the values over to the number field in DT. Once you have the numbers in place, you can discard the original relationship.

                To get two different relationships between the same pair of tables, create a new occurrence of at least one of the two tables.

                And you'll likely need to update some layouts in order to get value lists, etc. that work for ID numbers instead of names.

                • 5. Re: Relationship Question

                  For your relationship:
                  In your DT table you have a Player ID.
                  In your Event Participation you also have that PlayerID.
                  Now your PlayerID is linked to a AAU Coach Name.

                  If those fields are filled in. I mean if you take a player in the DT table. And fill in an existing CoachName, and that Coach Name exists in your AAU Coach Directory table, and that has information filled in like Cell phone number etc.
                  You should be able to get that information in your Event Participation layout if you set a Player ID there.

                  I made a little test:


                  • 6. Re: Relationship Question

                    I want to change the relationship form text(coach) to a number. The only issue is I have over 1000 records how do I do this without having to fill the team or coach information over for all of these records. DOes making a new relationship and setting up fields for ID umbers and replacing the relationship allow me to do this without a hitch?

                    • 7. Re: Relationship Question

                      If you are careful to do things in sequence, yes.

                      First set up the auto-entered serial number fields and the matching number fields.

                      Then use replace field contents to update your existing records with serial number values.

                      Then use replace field contents a second time (or a looping script) to copy the value from the serial number field to the match field in th erelated table.

                      Now you have your key fields set up for the number based relationships and you can update your existing relationships to switch them from names to numbers.

                      Final step will be to review each layout, looking for value lists that enter names to link your tables. These will need to be updated to use a value list that enters an ID number instead of a name. Depending on the format selected (drop down vs. pop up), you may need to further edit the layout so that you can see the name from the related table when you exit the field.

                      Throughout this process, make lots of back up copies so that you can toss out a file and try again with a back up if one of the replace field contents or looping script operations doesn't correctly update your data as you expected.

                      • 8. Re: Relationship Question

                        1.)"First set up the auto-entered serial number fields and the matching number fields."

                        OK I already have the serial numbers setup, for some reason I didit when creating the table but thought that linking the name instead of numbers would help so this has already been done

                        2.) Then use replace field contents to update your existing records with serial number values.

                        3.)"Then use replace field contents a second time (or a looping script) to copy the value from the serial number field to the match field in th erelated table."

                        I'm lost on this step I dont think I know how to do this to copy the value using the replace function. Again it may be easy and I probably am missing something.


                        Can you explain again how to update the records when I have the serial numbers already created from the the team(coach) table? Obviously I can do it one by one and I know you explained it to me, just couldnt understand and grasp it , I know shocking that I dont understand something lol 

                        • 9. Re: Relationship Question

                          Hey Mike, I will try to help.

                          I would set up a looping script. And I'll try to explain it as completely as possible.

                          First thing you need is the ID field in the "AAU Coach Directory" But you already have that. Make sure it's all unique (Auto Enter Serial Number)

                          Then you need a CoachIdFk number field in your DT Table. Here we will store the Id of the coach.

                          Keep your relationship between the Coach name fields for now. We will use it to get the Coach ID.

                          Then create a new script.

                          The first step could be:
                          - Go To Layout "DT"  - That is if you are not already there. But it can't hurt to add it.

                          - Show All records  -  again, can't hurt to add.

                          - Go to Record / Request (First).

                          - Loop

                          - Set Field [DT::CoachIfFk; AAU Coach Directory::CoachId]  -  This means that you will put the Coach ID from the AAU Coach Directory table in your DT table in the CoachIdFk field.

                          Go to Record Request (Next)  - Exit after last  - Make sure you select "exit after last" otherwise it will go on forever  :)

                          End Loop

                          This should fill all the correct Coach ID Numbers in the DT table.

                          You should check to see if it looks correct. When it does you can change the relationship between DT and AAU Coach Directory and base it on the number fields.

                          • 10. Re: Relationship Question

                            If you already have a serial number field loaded with serial numbers, you can skip to step 3 the step where you copy the serial numbers to the related records.

                            • 11. Re: Relationship Question

                              Ok so I have the serial numbers already, so how do I go about copying them to the other table? This probably is a very easy answer, but I can'tpicture them.

                              • 12. Re: Relationship Question

                                Go to a layout for the related table (we'll call that the "child table" and the table with the auto-entered serial number, the "parent" table).

                                Make sure that fields from the Parent table correctly display data on this layout. You can add a field or two as a test if you need to.

                                Put the child table's match field for the serial number on this layout.

                                Select Show All Records

                                Click in the field and select Replace Field Contents from the records menu.

                                Use the calculation option to enter this expression:


                                and do the replace field contents.

                                This "batch updates" all the records in the child table with serial numbers from the related parent table records.

                                Now you can switch your layout from names to serial numbers.