1 Reply Latest reply on Apr 30, 2014 11:44 AM by philmodjunk

    Performing find on related record

    rustyt

      Title

      Performing find on related record

      Post

           Hi
            
           I can't get my head around how to perform a find from a field from a related table.
            
           Situation is this - I have three tables - sites, developments, organisations.
            
           [U]Site[/U]
           pkid
           name
           fkorganisationid
           g_siteowner (autoenters siteowner)
            
           [U]Developments[/U]
           pkid
           fksiteid
            
           [U]Organisation[/U]
           pkid
           name
           type (siteowner / developer etc...)
            
           On the sites layout,  site::fkorganisationid is selected from TO organisation_siteownertype.   This provides my filtered list to only show organisations of the type 'site owner'.  The relationship is set between site::g_siteowner and organisation::type.
            
           On the developments layout, I display the organisationsiteowner::name from a separate to from a second TO organisation_siteowner. This is related to site from site::fkorganisationid.
            
           On the developments layout I want users to be able to perform a find based on the organisationsitewoner:name field.  Ideally, I want them to select the organisation name from a value list that is filtered to only show organisations that have previously been selected as site owners.  I don't want to use my existing value list I had created to display organisations of site owner type as this would also include all organisation of that type and not only those that have been selected.
            
           My problems are two fold.
            
           1 - How to best set up the value list to provide the correct filter list.
           2 - The more pressing issue is how to perform a find on a field related from another table
            
           Any pointers would be much appreciated.
            
           Thanks
            
           Russell

        • 1. Re: Performing find on related record
          philmodjunk

               What does 'g_' stand for in the g_siteowner  field? Many developers use that to identify a field with global storage. Is this a global field?

               1) is a case of setting up a conditional value list. Option 1 of the first link in the following set of links describes one simple way to set up such a list by using a calculation field that is empty unless the record is for a siteowner as the source of values for the value list.

               2) We always have to be careful in this situation. What exactly do you want to find when you select an organization from this value list? Do you want to find all Developments records that have at least one related Organization record for this organization? Or do you want to pull up that organization record? Or do you have something else in mind?

               My links on conditional value lists:

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.