Deleting records from a found set

[FileMakerHarry]

Hi Board!

I have two tables, one that I have produced a found set from, which has 312 records.

This is 'Table 1'

I have another table ('Table 2') that just contains one field, which is also a field in Table 1. It has 65 records.

I want to delete, from Table 1, any record that ISN'T in Table 2.

That would leave me with 65 records in Table 1, having deleted 247.

What's the process?

I've got as far as having the 312 in a found set, and a table with 65 records, some of which match those in 312, but no relationships or anything like that.

Thanks for your help and information!

Thank you,

FMH!

[comment]

Please don't use abstract names like 'Table1' and 'Table2' - they make it difficult to follow your explanation.

Now, assuming you do have a relationship between the two tables, based on matching the two fields (it will be much more difficult without it), and starting with a found set in Table1:

Go to Find mode;

Enter * into the related Table2::Matchfield field (you'll need to have it on the layout, if you're doing this manually)'

Click the 'Omit' button

Select 'Constrain Found Set' from the Requests menu.

If scripted, that would be:

Enter Find Mode[]

Set Field [ Table2::Matchfield ; "*" ]

Omit Record

Constrain Found Set

[FileMakerHarry]

OK, thanks. I do not currently have a relationship between the two, so i'll put that in.

While I'm omitting the records from the 'found set' i need to delete them from the entire database. Does this script you've written show me the 65? Can it show me the 247 so i can 'Delete Found Set'?

[FileMakerHarry]

Right, so to recap:

I have a Table, 'Products' with a field 'Job Number' and 'Product ID'.

Product ID is unique. Each Product ID is an individual number.

I have another table, called 'Errors'. This only has one field, 'Product ID' and is also unique.

I have linked Errors::ProductID = Products::ProductID

[comment]

While I'm omitting the records from the 'found set' i need to delete them from the entire database.

That's not exactly what I understood the first time around. I thought you wanted to omit from the current found set in Products all records that have a related record in Errors. The resulting found set would include records that:

1. Are members of the original found set in Products;

AND

2. Do not have a related record in Errors.

This, IIUC, is the set you wish to delete.