Invoicing Problem

[Benny]

This is my first time on this forum,So bear with me guys.I have a done a invoicing system on the lines of the one shown at http://www.maclane.com/portinv.htm .It is extactly the same ,except In the Inventory file I have added a new field called "Qty" holding the stock quantity of the product.And also added a button called "Process" to the Invoice file.What I want to do is when I click this button I want to run a script that will substract the oder quantity in the line items file from the stock quantity in the Inventory file.Every way I try to do this only the first entry in the Inventory gets udated.Please not that the invoice line items are displayed in a portal in the Invoice file at the time of calulating.Thanks for your help in advance.

[Jim McKee]

Hi Benny ...

Welcome to the Forum

Basically what you are trying to do involves stepping through each line item record and deducting the Quantity from the inventory count for that item.

This can be accomplished most easily by performing a Go To Related Record [show Only Related] script step from the Invoice record. This will assemble a found set in Line Items consisting of only that Invoice's related records. You'd then call a subscript in the Line Items file to loop through the found set and perform the stock deductions based on what I assume is a relationship you've already set up between the inventoryID in Line Items and the inventoryID in Inventory. The subscript in Line Items would go something like:

Go To Record/Request [First]

Loop

SetField [inventoryID | Inventory_inventoryID::productcount, inventoryID | Inventory_inventoryID::productcount - quantity]

Go To Record/Request [Next, Exit after last]

End Loop

However, there are a couple of problems with this method:

1. if you return to the Invoice to add an item or edit the quantity for a existing line item after running the inventory update, and then run the inventory update again, this will result in some quantities being deducted twice from inventory.

2. whether or not this is a networked system, you should include some kind of validation on the quantity field in Line Items to check for sufficient stock. However, even doing this, by the time you run the inventory update, another user's Invoice may have reduced the existing inventory. So, when you run the inventory update, you also need some way to check that there is sufficient stock to lock in the quantity the customer has ordered.

The first problem can be dealt with by adding a field to your Line Items records -- let's call it "inventory_updated". This is a number field that will always be either empty or contain the number "1". When you run the inventory update procedure, all Line Items processed by the procedure will have a "1" inserted into the "inventory_updated" field. So you'd expand the subscript above to something like:

Go To Record/Request [First]

Loop

If [isEmpty(inventory_updated)]

SetField [inventoryID | Inventory_inventoryID::productcount, inventoryID | Inventory_inventoryID::productcount - quantity]

SetField [inventory_updated, "1"]

End If

Go To Record/Request [Next, Exit after last]

End Loop

Now the subscript will first check if the record's quantity value has already been deducted from inventory by looking at the "inventory_updated" field. Only in records where this field is empty, the script will deduct the record's "quantity" value from stock AND then set the "inventory_updated" field to "1" (true).

The second issue can be dealt with by adding a step to check the quantity value of the line item against existing stock when you run the update inventory procedure. If the script detects insufficient stock, it alerts the user and sets the quantity of the Line Item to zero. The user can then manually check stock quantities and determine how to handle the situation (e.g., backorder?). Your subscript would then look something like this:

Set Field [ gn1, "" ]

Go to Record/Request/Page[ First ]

Loop

If [ IsEmpty(inventory_updated) and not IsEmpty(extension) ]

#stock is sufficient for Line Item quantity ("extension")

If [ extension <= inventoryID | Inventory_inventoryID::count ]

Set Field [ inventoryID | Inventory_inventoryID::count, inventoryID | Inventory_inventoryID::count - extension ]

Set Field [ inventory_updated, 1 ]

# stock is less than the Line Item quantity ("extension")

Else

Set Field [ extension, 0 ]

Set Field [ gn1, 1 ]

End If

End If

Go to Record/Request/Page[ Next, Exit after last ]

End Loop

If [ gn1 = 1 ]

Show Message [ Buttons:

1076100140-Inventory Management.zip

[Benny]

Thanks Jim.Its perfect.You saved my life...