In need of some advice...

[eboria]

I'm putting together a preventive maintenance database for my company using FMP 5. So far it's pretty straight-forward: I have a file with Equipment info (models, serial numbers, etc.), I have a file for Work Orders which uses look-ups to get the equipment info. that part was easy.

 

Now I'm trying to set up Maintenance Instructions for scheduled preventive maintenance. Each machine has a different set of instructions for scheduled maintenance - daily, weekly, monthly, etc. Ideally, I'd like to enter the equipment's description on the work order, then select an assignment - general maintenance, daily PMS, weekly PMS - and have the proper instructions appear on the work order.

 

Is there a way to do a look-up based on two criteria instead of just one? If I set up a new file for maintenance instructions, how do I set up that kind of relationship? I know I can set up a basic list of all of the maintenance information, but there are about a hundred machines, each with multiple instructions and I know the people who will actually be using this database day-to-day are not going to want to scroll through a bunch of lists to get the information they want.

[AHunter3]

Is there a way to do a look-up based on two criteria instead of just one?

 

You can set up an auto-enter calculation that is based on multiple criteria instead of just one. Lookups are only one type of auto-enter option.

 

 

Case (Count (Table X::Serial)>0, Table X::Part Number; Count (Table Y::Serial)>0, Table Y::SKU Number)

 

 

 

If you need them to refresh when some field in the local table gets changed, the way that lookups do, use Evaluate, which lets you specify a local field as the "trigger" field to prompt a re-evaluation, and uncheck "do not replace existing field contents".

[Ender]

In FM6 and below, you can get a multi-criteria relationship by concatonating fields in a calc, on both the parent side and the child side. The calc might look something like:

 

MatchKey (calculation, text result) = Equipment Model & " " & Frequency

 

Use those match keys for the relationship.