Finding Related Records

I have two tables (TRANSACTIONS and HOLDLINES). Each table contains the field CUSTOMER_ID. I want to determine if both tables contain the same customer_ID. Options:

(a) Save the ID and then Go to Layout B and perform a find

(b) Create a join table and use the Count function

(c) Create two Lists and compare List A to List B

(d) Use Execute SQL

I’m leaning toward option (a) because FMP quickly executes finds and no additional schema is needed. However, I’m wondering if there are other options.

Suggestions?

Do you want to answer the question, "For this specific CUSTOMER_ID in TRANSACTIONS, does the HOLDLINES table contain any records with the same ID?"

If so, I would simply create a relationship between the two tables based on CUSTOMER_ID, and then use the GTRR (Go To Related Record) script step (with the option to match only the current record).

Or you could just use the Count() function in table 1 to count the # of related records in table 2.

Would those options work in a many-to-many relationship without a join table? Both tables will contain multiple records for the same customer.

How about a SQL UNION that queries records from both tables, assuming each table has the same structure.

Not sure what you mean by “same structure.” The only common field is Customer_ID.

Sorry, then a UNION won't work.

As to the question of whether to add a relationship or not... my thinking is that if the relationship is an important part of the database design, then you should include it, as it will make future developers (including yourself!) more aware of what is related to what.

If this calculation is rarely used, or not very important, then I might just do it purely in a script, so as to not clutter up the relationship graph.

One problem with Execute SQL is that it's brittle in terms of field names - in most of FileMaker, if you rename a field, all references to it are automatically updated (because FM uses an internal ID for the field). If you are hard-coding field names in SQL, these are easy to break by accident. Just another factor to consider when thinking of pros/cons.

Select all DISTINCT customer Id table A, store in var $a
Select all DISTINCT customer Id table B store in var $b

common custID = BE_ValuesUnique(BE_ValuesTimesDuplicated ( list($a;$b) ; 2 ))

missing custid in B : BE_ValuesFilterOut ($a;$b)

probably the fastest way

I am comparing customers in each table, and also comparing inventory items for the selected customer. Therefore, I wrote a script that (1) executes a find to determine if the current customer exists in Table B. If so, the script then (2) uses the FilterValues function to create a list a common items between Table A and Table B. Then, the script (3) loops through the found set and processes the items. I am going to test the script today.

My approach would much much faster

you write selet that will return this

Table A :
CustA INV01_5 INV02_6 INV02_7
CustB INV01_8 INV02_3 INV02_2
.
.

Table B :
CustA INV01_5 INV02_6 INV02_7
CustB INV01_8 INV02_4 INV02_2
.
.

You put al in a list,
You keepn only the unique rows

With

BE_ValuesTimesDuplicated ( list($a;$b);1)

and bam, you get
CustB INV01_8 INV02_4 INV02_2
CustB INV01_8 INV02_3 INV02_2

so you knwo CUST B has a mismatch

I do this everyday with 100 of thousands records, its very fast

One problem — the List function does not create a list of field values for the entire found set. Therefore, I am going to try creating a “List of” summary field. The other option is a custom function from the Brian Dunning site.

use SQL

@Glitchtraker and everyone else — thank you for the suggestions. In the end, I wrote a script that executes a find to verify the customer exists in Table B, and then if so, compares Item List A to Item List B. I will test the performance to see if changes are warranted. Thanks again.

You've apparently already solved this, but in the spirit of sharing......

  • A summary LIST field will automatically gather the selected field from all records in the current found set.
  • A script step that writes that summary data into a global field
  • That global field is used to match against the secondary table occurrence. (a unique characteristic of FileMaker - the ability of a LIST in a parent field in a relationship, being able to match each individual matching child table records).
  • If the relationship is set at "=", the matching records will be reflected in the child table of the relationship
  • If the relationship is set to "≠", the NON-matching records will be reflected in the child table of the relationship.

You can run this drill from either side of the relationship, using the reverse TO structure.

2 Likes