Question: Warehousing Database

[swhugh]

Hello,

I'm trying to expand my "products" database so it can keep track of the quantity of items in different location. I'm pretty new w/ FMP, so I was just using the sample files and pick some information here and there.

The structure is that we have many products that are being kept in many different locations. We also move the products from one location to another location quite often.

So the table I've set up is:

1. Product Table (Product ID, total stock)

2. Product Location (Location ID, Product ID, stock here)

3. Locations (Location ID)

4. Transfer (Transfer ID, Location From, Location To) Location From and Location To is a lookup that is connected to the Location table.

5. Transfer Line (Transfer ID, Product ID, Qty Moved)

The layout I have in mind is similiar to a quoting layout, where on the top will be the "Location TO" drop down list and the "Location From" Drop down list.

In the middle is a portal containing the Product ID drop down list and the Qty Moved input field.

I'm thinking that all I need is a button and a script to tell FMP to move the products & qty on the portal from one location to the other by using Set Field and setting the Location TO as a parameter for the button.

The problem is that it doesnt work. (Well the problem is that I dont know why it doesnt work). My script is as follows:

- Enter Browse Mode

- Go to layout (ProductLocation)

- New Record Request

- Set Field (ProductLocation::Location ID;Get(Script Parameter)

- Go To portal Row (Next, Exit after last)

- Set field(Product Location::Product ID, Transfer Line::Product ID)

- Set field(Product Location::Stock, ProductLocation::Stock+Transfer Line::Qty moved)

The problem is that the script will only set the location ID field, and not the product ID and Stock field.

Another problem is that the script wont check if this item-location combo is already in the Product Location Table, and so creating a redundant entry.

I also think that a similiar script for substracting the stock for the originating warehouse is required.

But I dont know how to do all this, and I'm out of idea and resources.

Can someone please help me make sense of all this?

Thank you