I have two tables - one is an import from an external source which contains the master data set and the other table contains hand-entered data overlapping the data in the first table. Sometimes people make mistakes entering data from the master table and sometimes they miss a record entirely.

I have a calculated field in each table which is unique to each record, but which matches across the two tables. Is there a way to compare the records in Table B with the Records in Table A, and using the unique field determine which records are in Table A but are missing from Table B? We're mostly using FMP 12 but can go to 13 if necessary.

Any help or general pointers how to approach this would be most welcome.

Thanks

15 minutes ago, Colin Hunter said:

Is there a way to ... determine which records are in Table A but are missing from Table B?

There are several, here's one using a manual find: assuming you have already defined a relationship between the two tables, place the match field from Table B on the layout of Table A, enter find mode, enter an asterisk into the field, omit the request and perform the find.

Thank you for your reply but when I define a relation between the two tables using the unique match field and display the match field on Table A it's empty for every record. In fact I don't get data in any related field from Table B displayed in Table A, even though there are matching fields.

I now realize I missed an important fact: the Table A unique field calculation is stored but Table B is sourced via an ODBC link to an Oracle database so its unique field calculation is not stored and the relationship does not work. Is there a way to create a stored snapshot version of the unique match field in Table B or will I need a different approach?

If the matchfield in Table B is unstored, then do:

Go to Layout [ “Table B” (Table B) ]
Show All Records
Go to Related Record [ Show only related records; Match found set ; From table: “Table A”; Using layout: “Table A” (Table A) ]
Show Omitted Only

 

Thank you for your reply but I couldn't find the "Go to Related Record" command - is that in a FMP version after v13? In any case I did a work-around by creating another table with just one field and imported the calculated match field from Table B into the field in the new Table C. Since this is now a regular field it can be used to relate to the match field in Table A and I'm good to go. Importing 70,000 records from Table B takes a few minutes, even though it's just one field but it's definitely something I can live with.

I now have another question regarding my calculation of the match field but it deserves a new topic.

Again, thank you for your help.

Go To Related record is only available through scripting - but is definitely available in FMP13

Edited November 23, 20169 yr by webko