Hi guys,

I am relatively new to the FM world - so please forgive any very obvious errors...

The problem I face is probably one that I guess has been solved many times before.

What am I really trying to do:

When a user enters a sales invoice line they need to be able to select a product code for that line.

Because there are so many product items they need to be able (on a separate layout) to search for a range of stock times according to parameters that they enter and then select the most appropriate item (and return that item code to the invoice layout (which has the line items in a portal).

Background:

I have a table called "products" which stores details of products and services our company sells

(in my prototype I have only put in some 200 items which is enough to see the sort of problems more than a screen of data will cause). In my products table I have three fields:

ProductType

ProductFamily

ProductRange

which are used to sub group the products records - the content of ProductFamily is dependent on ProductType and ProuctRange is dependent on ProductType - NB all 3 fields are not always used - that is to say every product has a ProductType some of those have a productFamily and some of those have a productRange.

My problem is as follows:

Using "perform find" within a script does not allow me to pass calculated fields as the comparison string (nor does the custom dialog help)- in any event you really want to use drop downs so the user doesn't have to type in

Using a multi-predicated self join for each of producttype, productfamily and productrange doesnt work because it has to be an AND relationship (with exact matches) and therefore requires values for all of the fields otherwise you get an empty result set.

Any help would be extremely welcome - at the moment this has completely stymied the project but it must have been dealt with before.

Thanks

Simon

Take a look at the BPI sample file that comes free with FM. Look at the Sales Order and inventory layouts. They may give you some ideas.

Also, your script, I believe, should be

Enter Find Mode() /* set pause off

Set Field(:CalculatedField)

Perform Find()

HTH

Thank you for taking the time to post a response to what was clearly a poor issue (and clearly mystifying to all other FM users)

- the only reason I posted was because a member of the support team at filemaker uk said you couldnt pass variables to searches (finds) which you clearly can...

I am so grateful for your response and have already included a variation in my app

Many thanks