[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

Performing find in another file


I am a FM newbie and I would appreciate if you could answer following question for me:

Is it possible to write a script in FM for a certain file, which will loop throuhg every record in a table, check if certain field value is present in another table in another file?

Eg. I have a file A.fp7 with table A with field a_field, file B.fp7 with table B and field b_field.
Now I want to loop through every record in A table, take value from a_field and check how many b_fields in B = a_field. The script should return the number of found records from B table in every loop iteration.

Hopefully it's understandable, if not, please feel free to ask further questions. If you know how to solve it, please explain in detail or post a sample script, I've never wrote a script in FM before. Thank you in advance.

1 Solution
The looping script could be something list this:

//First get all the records in Table A and sit on the first record
Go to layout 'Table A'
Show All Records
Go to Record (first)

//Now get all the records in Table B and sit on the first record
Go to layout 'Table B'
Show All Records
Go to Record (first)

Go to layout 'Table A'

set variable $comparefield = Field from Table A
Go to layout 'Table B'

   //If the value in comparefield matches, save the result (builds a return delimited list of matches)
   set variable $result = Case (
                                    $comparefield = Field from Table B ; $result = $comparefield & " from Table A matches
                                    record no " & Get ( RecordNumber) & " in Table B" & Case ( not IsEmpty ($result); "ΒΆ" & 
                                    $result )
   Go to Record (next, exit after last)
   End Loop

Go to layout 'Table A'
Go to Record (next, exit after last )
End Loop

Set Results Field in Table A = $result
Commit Record

//End Script
I haven't tested this out, it's just the basic logic of how the looping script might work.  You should be able to relatively easily debug this if you have Filemaker Pro Advanced using the script debugger. I hope it helps.

Here's an alternative to a looping script. This one creates a relationship and a calculation field in A, automatically counting how many matching Bs there are without looping. As B table changes, so does the result of your calculation - no need to run scripts after changes in B.

Starting in File A
1. Go to File menu -> Define ->File References, New
2. Browse to File B, and add it in.
3. Go to Manage Database
4. Go to Relationships
5. Add new table on the graph, select the file reference in the pull down, and you should see your B file, select it and then click on the table. Now you should see a table for A and a table for B in your relationship graph.
6. If you cant see Table A in your graph view, add it now.
7. Click on Field A in table A and hold. Drag the relationship line and let go when cursor is overtop Table B, Field B.
8. Name your relationship.
9. Go to Define Fields
10. Select Table A
11. Add new field: totalOccurence_calc
totalOccurrence_calc = Count (Relationshipname::field B)
12. Put this field on the list view for A records.

Now you have the total number of Bs that occur for every A when browsing list view in A.
if the only purpose is to count the related records, then Webw... solution is the best by far as it will be dynamic.
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

I agree, webwyzsyst..'s solution is the best option.
StraySodAuthor Commented:
Thank you
StraySodAuthor Commented:
I decided to reward Tocacar since the solution was almost exactly what I needed. I didn't need to count only related records, I needed to use some text functions to decide whether the two fields are equal. In fact it should be similar, not equal, that's why counting related records wasn't sufficient.

Thank you all for your comments.

The only important modification to Tocacars script was, that I added 'Go to record (first)' before the inner loop, to always check the records in second table from the very beginning.
Thanks StraySod, I'm glad it was useful.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now