3 Replies Latest reply on Oct 8, 2014 11:26 AM by philmodjunk

    Finding Data across Multiple Records in Multiple Fields (Advice Sought)



      Finding Data across Multiple Records in Multiple Fields (Advice Sought)


      Although I’ve had filemaker for a long time, I’m still quite the novice at parts of it and after searching the forums I have not found an answer to what may be a very simple problem;

      I have two related tables; Table1 is an extensive list of detailed information about a legacy system. Among its fields, it has a report_numeric_ID , report_name and 52 text fields (labeled field_name_01 through field_name_52).

      Table2 contains the field_names that populate some or all of the 52 fields in Table1 (across 204 records currently) Table2 contains the field_name, a field_type, and a field_description. These field_names are then populated via dropdown lists in Table1’s field_01 through field_52 with the related information (type/description) then being automatically filled into Table1.

      What I am trying to do now is create a new layout within Table2 the purpose of which is to be able to move through the records containing the field_names and have filemaker then search through all of the records in Table1, listing the report_numeric_ID and the report_name. The purpose of this is to be able to list all the records that have the currently selected field in it, regardless of whether the field name occurs in field_name_01, field_name_02,  or whichever field it’s in right up to field_name_52).

      Thus, if field_name “location” appears in six records, regardless of whichever field (01 – 52) filemaker can find it and present report_numeric_ID and the report_name as follows.

      Field_Name: “location”

      -- 52, Grant Claim Report

      -- 64 Contractor List Report

      -- 122 Combined Grant Report

      --149 Grants in Delphi

      -- 229 Recovery Report

      -- 334 Remaining Funds by Sector

      I have used the “list” command, but it only reports back when the field_name appears in field_name_01, but not when it appears in another field_name_# (02 through 52).

      My question is what approach might work to generate this information? As noted prior, I’m more or less a novice at this level of Filemaker and I have not worked with scripts and thus would appreciate more verbose answers. Thank you in advance.