Finding records not in a related table

[RedRock]

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

[Genx]

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

[Ender]

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.

[Genx]

Hmmm, yeh, i thought this was a once off thing.

[comment]

Option d)

Go to Related Record [ From table: "main" ; Show only related records ; Match found set ]

Show Omitted Only

[RedRock]

Thanks guys, option D worked a treat, much appreciated.

Regards

Paul