asked on

Performing find in another file

Hi,

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.

Regards,
Martin

ASKER CERTIFIED SOLUTION

Tocacar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

webwyzsystems

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.

Member_2_908359

if the only purpose is to count the related records, then Webw... solution is the best by far as it will be dynamic.

Tocacar

I agree, webwyzsyst..'s solution is the best option.

StraySod

ASKER

Thank you

StraySod

ASKER

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.

Tocacar

Thanks StraySod, I'm glad it was useful.