In my customer file I have over 5000 customers with the standard information such as; Name, Address, Phone, Fax, etc…

In my Invoice file I have all the customer orders (using a “Line Items” file).

What I want to be able to do in my customer file is “Find all Customers” who did not buy a specific item.

Such as:

1. Find all Customers in the state of… IL, IN, OK, TX & CO and

2. Omit from the above list all Customers who have purchased “Product #55347.

I don't want to do contact customers who have already purchased a specific item.

Your help will be appreciated,

Harry

You need to perform your find in the Line Items file.

I've never had the patience to sit down and read, but the "omit" feature shoudl be what you are looking for.

I hope someone here can shed some light for some of us not willing to read the manual nd tell us how to use the "omit" feature in multiple finds.

You want to do the select from your Customer file, where the State field is kept. The information you want is stored two "relationships" away, in your Line Items file, and the only way to get there is via your Invoice file.

Here is a way to make it work. It is not pretty, but it avoids adding too many fields and avoids doing lookups.

In your Customer file:

(1) I assume you already have a Relationship established with your Invoice file , based on name or customer ID.

(2) New Field to create: Item to Push , Global , Text -- this is the field in which you will enter the Item Number you want to sell

(3) New Script to create:

Copy (select , Item to Push)

Perform Script (subscript , External:Invoice.fp5) --- this external script is located in your Invoice file and is explained below

Enter Find Mode( )

Set Field (Invoice:Marker Flag , "X") --- this field is added to Invoice , below

Omit Record --- because you don't want anyone who has already bought this item

Perform Find ( )

In your Invoice file:

(1) I assume already have a Relationship established with your Line Item file , based on Invoice Number.

(2) New Field to create: Marker Flag , Text , Indexed

(3) New Script to create:

Show All Records

Replace (no dialog , Marker Flag , "") - this clears the field from last time you ran script

Enter Find Mode ( )

Paste (select , Line Item:Item Number) --- this pastes the Item number you want to sell that you entered above

Perform Find ( )

Replace (no dialog , "Marker Flag" , "X") --- at this point you return to the Customer file script that started everything.

Note that this will give you everyone from all states who has not purchased the item. After you have run it once , you can simply "stack" a manual query:

Command-F (Wintel:CNTL-F) to enter find mode

Put an "X" in the Invoice:Marker field (you will have to add this field to a layout)

Put a state in the State field

Press Command-D (Wintel:CNTL-D) to duplicate this request & change the state --- repeat until you have all your states.

Press enter.

If you will do this exact query often , immediately create a script that simply contains Perform Find (restore requests) and run it after running the Customer script described above.

Bruce,

This looks great! I've been fumbling around with this thing for months and have gotten nowhere. I'll give this a try tonight. Man I've needed help with this. thank you so much...

Harry