Conditional Set Field for PO-Inventory

[swhugh]

Hello,

I'm trying to setup a script so I can Set the value of a field that is on a separate layout. But first I need FMP to search if the value I'm going to use alredy exist on that table.

The application is for a PO-Inventory system.

My situation is such that I need to track expiration dates of a product, as well as its location, and quantity in stock. We have multiple locations, and normally these items are bought in batches. Items in a batch has the same expiration date.

I've setup a PO, PO Line, Product, Batch, Location and ProdBatchLocation Tables. (Diagram attached).

The area I'm having problem with is inputting values from PO Line to ProdBatchLocation. I've setup a script to set the fields (Set Field) in ProdBatchLocation. But fmp changes the value of Batch and Location ID in ProdBatchLoc everytime we have the same Prod ID. (Please see diagram, it's difficult to explain without visual help).

I'm sur this should have been easy, but I just cant figure out how.

Thanks in advance for your help. I appreciate it.

[Genx]

I'm trying to setup a script so I can Set the value of a field that is on a separate layout. But first I need FMP to search if the value I'm going to use alredy exist on that table.

The area I'm having problem with is inputting values from PO Line to ProdBatchLocation. I've setup a script to set the fields (Set Field) in ProdBatchLocation. But fmp changes the value of Batch and Location ID in ProdBatchLoc everytime we have the same Prod ID. (Please see diagram, it's difficult to explain without visual help).

It's hard to make sense of which part you're actually having problems with.

[swhugh]

Genx, Sorry. As I said it's difficult to explain what's going on... and Thanks for your reply.

Simply put, the problem was whenever I input a product that's already in the ProdBatchLoc Table, it will update the Batch ID and Loc ID values.

What it should have done was to search and check if the combination of Prod ID, Batch ID and Loc ID already exist. If it does then update the value. If it doesnt, create a new value.

Anyhow, I'm attaching changes I've made that SEEMS to address the issue. I'm not 100% sure it works (It seems to work, but I dont know why it works : )

I'm thinking of a follow up problem: What happens if items are shipped in partial shipments....

Thanks again.

NB: I cant find the attach button, so I cant attach my update. But the change is basically I added another instance of ProdBacthLoc called "ProdBatchLoc 2". The Product ID, Batch ID, and Batch ID field is connected via 3 many-many relationship to PO Line. Creation in ProdBacthLoc 2 is allowed through this relationship.

[mr_vodka]

You have to use "Full Reply" to see the option to attach.