Jump to content
Sign in to follow this  
Colin Hunter

Finding records missing from table B which are in table A

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Edited by webko

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.