AnsweredAssumed Answered

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

Question asked by BrienMiller on Oct 6, 2014
Latest reply on Oct 8, 2014 by philmodjunk


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.