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.
-- 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.