finding with multiple keys.

[deltaman1]

I am using FM4.0 , windows 2000 pro.

I have setup a SalesLine file, where each item sold is a seperate record, appearing as a line.

I have a seperate file for customer data.

I have a separate file for Inventory data.

Since different customers get different discounts depending on the inventory type they purchase, I have a seperate file for Discounts.

*********

I want the sales person to be able to enter the customer unique number and the computer whizzes all the desired customer data into the SalesLine record. I got this working, piece of cake.

I want the sales person to be able to enter the Inventory unique number the the computer whizzes all the desired Inventory data into the SalesLine record. I got this working, piece of cake.

***

Except: I want a script or relationship to go to the Discount File and use 2 keys, the CustomerType, and InventoryType, ( 2 seperate fields in the Discount File) and that will get me the one record in the Discount File that has that discount for that item. I then want to bring that discount back into the SalesLine field [discount] to be used to calculate that items net cost to the customer.

This is very easily done manually, but I want the computer to do the work, remembering that my salesman has already found the customer type and the inventory type, and they are in their fields in the SalesLine as we speak. ( so to speak).

I can't figure out how to use those two keys, and find the discount in my Discount file, by script, or relationship.

*********

(g) when I get this working, I have to program the bookeeping system, where I will need to key on 4 or 5 fields in a record.

I call them keys, how about __found fields__, 4 or 5 layers deep?? In other FileMaker Files.

Manually, we would do: open Discount File, find customers of type ( 11 ) now in that found set, further find all inventory of type (17); that will show a discount of (.40 ). paste to SalesLine record. I don't want to do it manually, that's why I am using a computer (g).

********

I have read the posts here for hours, mostly they wind up with the keyboarder having to find things manually. I want the computer to do this work. Any suggestions ?? I love Filemaker, maybe I can't see the forrest for the trees.

Thanks for any help, Ken Campbell, Iowa, Delta Inc.

[andygaunt]

So, you have seperate discounts for EVERY single item in your discounts file, for EACH customer?

OK. Well. If you create a calculation field in your discount file (lets call it zc_DiscountMatch, that is ;

Customer Number & ":" & Inventory Number (result of text)

Now, in your line items, repeat the calculation above (obviously using the fields in the line items database).

Now, create a relationship between these two items, then on your discount field, go to the options and check the looked up value. Choose the new relationship and then select the discount field.

This should, when the sales person creates the new record and has both the customer and the inventory lookup the discount.

HTH

[deltaman1]

Hello, your suggestion worked perfectly; thank you very much. I had been under the impression that since this is accomplished very easily manually ( although not fast enough for commercial use) that there would be a command for scripting multiple finds. Strange, since I think each field can be indexed by preference. I wonder if FilemakerPeople are aware how important multiple keys are to the user of a database ?? In fact, that is their prime use, at least as far as serious applications.

**********

We don[t have a discount code for each seperate customer and each inventory item., but instead a discount file which cross references according to customer TYPE and inventory discount TYPE. The types themselves are in each customers record, and in that inventory items record.

There are currently roughly 30 customer types and 30 inventory types., or about 900 possible discounts for any one sales line. that is why I want the computer to find the dang discount, not the keyboarder.

Thanks again for your help. Ken Campbell, President, DeltaInc.

[Vaughan]

"I wonder if FilemakerPeople are aware how important multiple keys are to the user of a database ??"

They are aware -- that's why we can use them in FMP!

However, multiple keys are not the same as multiple finds. Multiple finds are also possible, BTW, just not as intuitive as they could be.