Validating fields or looking up values

[DavidT]

Hi,

We are developing a system using FM to record quality management systems for our business. We have created unique batch numbers, within which each item has its own number code. The batch id has 2 fields which identify the lowest and highest individual item number respectively.

On another layout, we use the item number to assess characteristics of that item, but we want to automate the other fields which relate to the parent batch.

For example. Batch 001 has item codes 1000 to 2000. The batch was created on 11/04/04 and originally came from site A. This is in Layout called Intake QC. On Analysis QC layout we want to input item ID 1500 and have the details of date and source come up automatically.

Any ideas how this can be done?

Thanks

FileMaker Version: 5

Platform: Windows XP

[Ender]

It sounds like you want to do a lookup based on the Item ID being within a range of item codes for a batch. If I have that right, then I can think of two possible ways to try to do it:

(this assumes two files; Batch and Item)

1. Create a relationships in Item between Item ID and Item Code Start in Batch. Create a Batch # field with a lookup Batch::Batch #, selecting the option 'If no exact match, use next lower value'.

2. Create a multi-key in Batch that keys all the values between the Item Code Start and Item Code End, then create a relationship between Item ID in Item and the multi-key Item Code Range. I'm not sure about the formula for a multi-key of a number range, there may be a topic on this somewhere.

Hope I answered the right question.

[DavidT]

Thanks Ender,

Yes you got the gist of what I need to do. I'll try it.

Many Thanks

David