1 2 3 Previous Next 36 Replies Latest reply on Oct 23, 2013 10:02 AM by RichardAdams

    Get Related Information

    RichardAdams

      Title

      Get Related Information

      Post

           I have a value list that shows all adults related to the current record. That value list is used in a dropdown to enter a name into the DadName field. That part works perfect.

           Is there a way to create a calculation field that gets CellNumber from a record where FirstAndLastName equals DadName?

           What functions would I use?

           Thanks.

            

            

        • 1. Re: Get Related Information
          philmodjunk

               Yes, but then if you have two people with the same first and last name, you may see the cell number for the wrong person appear as names are not unique. People also change their names, plus data entry errors can easily enter a name incorrectly in the table from which your value list gets a name. If you then correct that name, you can lose this connection to the record with the correct cell number.

               It's better to use an ID number unique to each person. Your value list can list the ID number in field 1 and a field for the person's full name in field 2. Then, you can select the person by name, but the value list enters their ID number. (and this is just the beginner version, more sophisticated and user friendly options that also link records by ID are also possible.)

               Whether you use a name or follow my advice to use and ID number, you can define a relationship between the two tables and then you can either display the cell phone number or you can copy it in to a field of the layout's current record. Either option is the best choice in different circumstances.

               Here's a thread that describes both options and how to select the correct one to use in your database:

          Auto Fill

          • 2. Re: Get Related Information
            RichardAdams

                 So, i thought of using id as well after i hit send (so glad you confirmed).

                 I prefer #1 or else we could just enter it manually... we want it to be dynamic.

                 So, because i already have a portal on the page, it's only pulling the information for the first record in the portal, not for the specific person specified in the DadName dropdown.

                 ...

                 It boggles my mind that a calculation cannot be set to essentially do the #1 and #2 options... it hard codes the answer into a field on the current layout, but also calculates as stuff changes so it's dynamic.

            • 3. Re: Get Related Information
              philmodjunk
                   

                        So, because i already have a portal on the page, it's only pulling the information for the first record in the portal, not for the specific person specified in the DadName dropdown.

                   If the portal uses a relationship where the dropdown's field is a match field in the relationship, your portal should only list one name in the first place.

                   And a calculation can be used, but is often not needed except for certain special purposes. (And ExecuteSQL--new to FIleMaker 12 can also be used in a calculation to access data from the correct related table.)

              • 4. Re: Get Related Information
                RichardAdams

                     Well, the main portal on the page shows a list of family members, which is the desired outcome for that specific portal.

                     For this application, I want the related record ID (the dad), to have a dynamically updated cell and email that is a part of the layout.

                     ---

                     So, you said a calculation can be used, what is the calculation? (even if it's only for special purposes).

                     (and we have fm11 so the second part wont work)

                      

                • 5. Re: Get Related Information
                  philmodjunk
                       

                            I want the related record ID (the dad), to have a dynamically updated cell and email that is a part of the layout.

                       Which is my point, to access a specific record from that set of related records can be one by adding a new relationship that matches by the selected person's ID and then it only matches to one record instead of a group of records. And two relationships linking the same two tables is quite possible so long as you use separate Tutorial: What are Table Occurrences? for the related table.

                       

                            what is the calculation?

                       The calculation relies on the same relationship. It would simply be a calculation that directly refers to a field in the related table. That's why it's rarely needed unless you are using ExecuteSQL to avoid adding additional relationship to your relaitonship map.

                  • 6. Re: Get Related Information
                    RichardAdams

                         I tried creating a separate occurrence already (as i thought the occurrence the portal uses was screwing things up).

                         So i must not have set it up properly...

                         Your most recent post gave me hope but I have not been able to set it up so I must be stuck on something.

                    • 7. Re: Get Related Information
                      philmodjunk

                           Can you describe how you set up the relationship to that added table occurrence? Feel free to upload a screen shot of the relationship map found on Manage | Database | Relationships if that helps you do that.

                      • 8. Re: Get Related Information
                        RichardAdams

                             ROCK HARBOR DATABASE is the main one... the 2 of that was for a show/hide thing i read when i first created it... ignore that.

                             Contacts2 is used for my current portal to display all family members.

                        • 9. Re: Get Related Information
                          philmodjunk

                               Contacts2 is used for my current portal to display all family members.

                               And a layout based on which table occurrence is used for that portal?

                               And where is the field formatted as a drop down where you want to select a contact and show their email address?

                               PS. the structure you have here--which I've recommended a number of times here in the forum, is designed to support the idea that a contact could be linked to more than one family. Is that what you really need for your database?

                               What does one record in Rock Harbor Database represent?

                          • 10. Re: Get Related Information
                            RichardAdams

                                 The main layout is based on Rock Harbor Database, and the portal within that layout uses Contacts 2.

                                 The dropdown field i want to choose a name from and pull up the info is on the main layout.

                                 But, I want a field in Rock Harbor Database to actually contain that information, so on a separate layout (table) I can use it.

                                  

                                 Each record in Rock Harbor Database represents 1 person... we probably do not need to have the option to put people in multiple families, but I am not in a position to be able to restructure the entire database at this point.

                            • 11. Re: Get Related Information
                              philmodjunk
                                   

                                        But, I want a field in Rock Harbor Database to actually contain that information, so on a separate layout (table) I can use it.

                                   But what you say you want to do does not require physically copying the data into a field. SImply establishing a link to the correct record makes that possible. And a layout and a table--though they may have identical names, are not the same thing.

                                   

                                        Each record in Rock Harbor Database represents 1 person...

                                   Ok. Should have looked more closely at the field names to see that Contacts and Rock Harbor Database share the same data source table.

                                   When you select a name in the drop down do you want to only see the names that your portal to Contacts 2 lists or all names in the table?

                              • 12. Re: Get Related Information
                                RichardAdams

                                     When i select a name... I want to see just related names... preferably just related names where Marital Status is not "Child" but if it's just related names, that's totally fine.

                                • 13. Re: Get Related Information
                                  philmodjunk

                                       Hmmm, but I thought you wanted to see the contact info (such as a phone number or an email address) for the person that you selected. Now you want this drop down to control what is listed in the portal?

                                       And does this mean that you want to see all family members for the selected person? If so, you should be able to just perform a find for that individual and the portal you already have in place will then show that person's family and the contact info for that person can be shown on that layout.

                                       You an set up your global field for that and use a script trigger on the field to perform a script that does the find for you.

                                       See this thread for examples of scripted finds: Scripted Find Examples

                                  • 14. Re: Get Related Information
                                    RichardAdams

                                         "Hmmm, but I thought you wanted to see the contact info (such as a phone number or an email address) for the person that you selected. Now you want this drop down to control what is listed in the portal?"

                                         No, The portal that already exists on the page i don't want touched, manipulated, changed, or anything.

                                         For a child (in our database), I want to be able to select who their Dad is (from a drop down), and have it fill in Dad Cell and Dad Email pulling it from the actual contact and entering it in that field, so I can use that information on a table layout (not a table occurrence).

                                         (I will repeat this process for Mom, Mom Cell, and Mom Email).

                                    1 2 3 Previous Next