AnsweredAssumed Answered

Performing find on related record

Question asked by rustyt on Apr 30, 2014
Latest reply on Apr 30, 2014 by philmodjunk

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

Outcomes