Match Field Based on Calculation... ARGH

[spankalee]

Hey fmforumers...

Ok, I have a 3 database problem here that I'm trying to solve with scripts, but I'm stuck.

The three DBs in question are Inventory, Shipments, and ShipmentDetails. These are incoming shipments and the Inventory file has a field for each product that shows the number of units on order.

Shipments has a field call Received that's either "yes" or "no". ShipmentDetails and Inventory both have a ProductCode field. I want Inventory::OnOrder to be the sum of quantity in shipment details of shipments that haven't been received yet.

I figured I'd need a match field that is ProductCode & "-" & Received. In Inventory it's simply ProductCode & "-No". In ShipmentDetails the calculation would be ProductCode & "-" & Shipments::Recieved. But as you know that calculation can't be stored and a relationship between Inventory and ShipmentDetails based on that won't work.

What I'm trying is to have a Received field in ShipmentDetails that looks up it's value from Shipments. But how do I get it to update when Received in Shipments changes?

I'm trying to use a script that is run whenever you click on the received field in Shipments but I can't get it to work. Relookup contents doesn't work because I'm looking up across a relationship, and I can't quite figure out how to update all related records.

Am I even on the right track? Is there an easier way besides running a script every time Received changes?

Thanks

[Ugo DI LUCA]

Hi Spankalee,

There's something wrong in here for sure, but I'm unsure it has to do with FileMaker. Please accept my apologizes in advance if I'm wrong, but the way you structured the flows is incorrect in my opinion.

The kind of mechanism you're trying to build would work in an invoicing system, where you'd be sure that a "paid" flag would apply to all lines related to this invoice.

This is not the case for the Shipment Module.

Actually, the very reason why you'd ever build a Shipment File is to check wether or not all items supposed to be shipped where received or not.

This means you can't have a "mark" in the Shipment File. You'd rather have this "mark" in any line of the Shipment Details file, where you'd check a "Yes/No" at the moment of the Shipment control.

Often, these kinds of controls are made manually by the warehouse team, which would deliver the "Shipments documents" with either a "All received" stamp or "Items missing" Stamp (with some identificant for those items missing).

Then, still in my humble opinion, you'd need to develop 2 alternative methods.

- One, for those "All Received" documents, where you'd open the Shipment File (or just input its ID in some global field) and run a script that loops into the lines to mark all related records with a "Yes".

- Another, for those "Missing Items", where you'd individually mark those missing items from a portal in the Shipment File.

Finally, I'd suggest that you script the Inventory update rather than having some related calculations, which should be your enemies, specially in an Inventory system where you certainly ask for a quick response time when searching for availabilities.

HTH