Matching a field to a list...?

[stubanz]

I have a bunch of records that I want to trim down by seeing if they are all using current products. Basically, in the record is a text/numerical field which indicates which product the customer (record) is using, in this form: XXX-####-XX, and I want to have each record matched to a list of current products, and if the product is current, I want to keep it, and if the product is not in the current list, I want to omit it. Is there any way to do this in FMP?

[aaa]

Where is your current product list? If in other file then you can creat relation to this file.It is simple in FM

[stubanz]

Yes, the current product list is in an excel spreadsheet. It is relatively short, and I was planning on simply entering the data into the database list to which I wanted to mach by hand. Should I instead make a "current product" database?

[aaa]

It seem that having different table for this will be better.

But if you will have value list for current product list in the same table, you can do your goal too, if you use ValueListItems() and Position() or PaternCount() functions.

[stubanz]

Okay, I now have a separate table with two columns, one with the product number (XXXX-##-XX) and the other with the common name of the product. How do I get a search to see if the data in one field in the other database matches any of the first column in the new DB?

[aaa]

Create relation from first file to second by Product_Number. Then in first file create Field, which=1 if IsEmpty(Relation::Product_Number) and =0 in other case.

So all records with Field=1 is your needing records.

[stubanz]

Okay, I see what you are saying here, and I have defined the relationship, but do I have to make a script with a looping IsEmpty?, or is there a way to define the field to automatically fill in the correct 1 or 0 depending on whether the product number is on the current product list?

[aaa]

I am sorry: if not IsEmpty.

I you want to see only your needing records you must create script such:

Enter Find mod

Set Field[Field;1]

Perform find

Then create button for this script.