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

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

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.

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

Option d)

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

Show Omitted Only

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

Regards

Paul