Jump to content

Finding records not in a related table


RedRock
 Share

This topic is 5656 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

Hi Folks,

I hope somebody can offer some assistance here.

I have 2 tables, related by product code.

I start with exactly the same records in each table, then some get deleted in the second table.

I need to find out which records were deleted.

The main table is static historical data, whereas the second table is a working table.

All I need to do is by using the data in the main table, and a find or relationship, determine which records are in the main table and not in the second table.

Probably sounds easy, but I just can't figure out how to do it.

Any suggestions would be greatly appreciated.

Thanks

Paul

Link to comment
Share on other sites

You could do this one of two ways (that come to mind) as far as im concerned...

Way a) Assemble a giant multi-key:

- Create a global field in your foreign table

- Write a script to pull all your product keys into the global field, seperated by the pilcrow (CR LF character)

- Relate this to a table occurance of your history table via the global to the foreign key

- Goto related records and omit the foundset

- The remaining records have been deleted

Option : (perhaps easier).

- Create a calculation field with the following calculation over the relationship:

Count(RelatedTable::ForeignProductKey)

Make sure it is unstored and in the calc window untick the box that says something along the lines of "don't evaluate if fields are empty"

- Run a find over this field for the value 0.

- The records returned are missing in your foreign table.

Any problems / need some clarification or an example just yell, someone will put one together for you.

Good luck

~Genx

Link to comment
Share on other sites

Option c) Don't use duplicate tables. Instead, mark the records as Deleted in Status field and filter any Finds and Relationships to omit those as necessary.

Link to comment
Share on other sites

This topic is 5656 days old. Please don't post here. Open a new topic instead.

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
 Share

×
×
  • Create New...

Important Information

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