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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.