Updating Quantity Records

Describes the scripting process of updating Quantity records and avoiding record locks.

Every process that moves inventory gets a record of that movement in Trayse Inventory. Since Trayse Inventory takes a two-prong approach to managing quantity values, a record is created in the Transaction table and a record is updated in the Quantity table.

The process is a series of modular scripts and organized in the TRANSACTION folder of the script workspace.

Transaction scripts, organized by the three main functions

There are three main scripts and subscripts are listed underneath with a slight indentation. The three main scripts, in the order they are called:

1. create transaction record (transactionType)

2. set the balance quantity fields (parameters)

3. update quantity record (targetField, calculatedResult)

A visual flow for creating Transaction records and updating Quantity records

The process begins outside of the transaction scripts, with a script that calls the "create transaction record (transactionType)" script. At the time of this writing, these scripts include:

1. cancel po

2. cancel sales order and transfer order

3. complete replenishment process

4. complete ship process and create invoice

5. create new assembly order from item (input)

6. delete lot

7. edit item quantities

8. edit lot quantities

9. edit po - subtract (transactionType)

10. edit po - update edited po line

11. po release to vendor (method)

12. post pick

13. post putaway

14. post receipt

15. release pick records to (nextProcess)¬

16. release sales order

17. release transfer order

18. start or complete assembly

Sales Order Example

As an example, let's run through a Sales Order. In this example, the Sales Order has three line items.

Adding, editing, and deleting line items while the Sales Order is in a New status does not initiate the transaction.

Clicking the "Release Sales Order" button calls the "release sales order" script and will initiate the transaction process.

Initiating the transaction process from a Sales Order

Each of the scripts perform a number of steps, all of which are important, but not necessarily pertinent to this topic. Therefore, it may be helpful to reduce these script to their bare essentials.

release sales order

Set Variable [ $transactionType ; Value: "Sold" ]
Set Variable [ $c ; Value: ValueCount ( List ( ORDERLINE::ID ) ) ]
Set Variable [ $i ; Value: 1 ]

Loop
    Exit Loop If [ $i > $c ]
    Go to Object [ Object Name: "p.Order Line"]
    Go to Portal Row [ Select: Off ; With dialog: Off ; $i ]
    
    Perform Script [ Specified: From list ; "create transaction record (transctionType)" ; Parameter: $transactionType ]
    
    Set Variable [ $i ; Value: $i + 1 ]
End Loop

create transaction record (transactionType)

Set Variable [ $transactionType ; Value: Get ( ScripParameter ) ]

If [ ]

::: SALES ORDER
Else If [ $transactionType = "Sold" ]
    Set Variable [ $qtyUpdateType ; Value: ORDER::orderType ]
    Perform Script [ Specified: From list ; "   get order variables for transaction" ; Parameters: ]

End If

There is a lot of duplication in the top part of this script. Many conditions point to the same subscript. Nonetheless, this repetition is easier to read than conditions that contained multiple OR operators to point to the subscript.

get order variables for transaction

Set Variable...
Set Variable [ $quantityID ; Value:... ] (see note below)

Set Variable [ $results ; Value: JSONSetElement (...)  ]

Exit Script [ Text Results: $results ]

The purpose of this script is to capture the necessary data into a variable and package it as a JSON object. That object will be unpacked with we return to the "create transaction record (transactionType)".

The $quantityID is essential.

Later in the process we will navigate to this record and update it along with the warehouse and item totals.

It is usually the primary pick record for that SKU at a particular warehouse, though occasionally, it is the primary receiving record.

create transaction record (transactionType) - Revisited!

Once we have the JSON object, we return to "create transaction record (transactionType) script, unpack the JSON object into variables, and create a new transaction record, setting the field with the variables.

Set Variable [ $results ; Value: Get ( ScriptResult ) ]
#primary keys
Set Variable [ $itemID ; Value: GetAsNumber ( JSONGetElement ( $results ; "itemID ) ) ]
Set Variable [ $locationID ; Value: GetAsNumber ( JSONGetElement ( $results ; "locationID" ) ) ]
etc.

Go to Layout [ "TRANSACTION_d" (TRANSACTION) ; Animation: None ]
New Record/Request
Set Variable...

Set Field [ TRANSACTION::warehouseID ; $warehouseID ]
Set Field [ TRANSACTION::locationID ; $locationID ]
etc.

Perform Script [ Specified: From list ; "set the balance quantity fields (parameters)" ; Parameter: JSONSetElement... ]

JSONSetElement is limited in the number of values it can hold. Get ( UUIDNumber) is too long and therefore packed as a JSONString and unpacked using FileMaker's native function GetAsNumber ().

set the balance quantity fields (parameters)

While the majority of the Transaction fields were populated in the previous script, "create transaction record (transactionType)", the quantity fields were not. These values are the same as those found in the Quantity table, but named slightly different. For example:

QUANTITY::onHand

TRANSACTION::balOnHand

"bal" stands for "balance".

In this script, "set the balance quantity fields (parameters)", the Transaction quantity fields are set. Remember that our script initiated from a Sales Order line. It is a single SKU at a single warehouse. As part of the process, we also have a locationID (in our case, for the primary picking location). We need to find the record prior to the one we just created.

Set Variable [ $parameters ; Value: Get ( ScriptParameter ) ]
etc.

Enter Find Mode  [ Pause: Off ]
Set Field [ TRANSACTION::itemID ; $itemID ]
Set Field [ TRANSACTION::warehouseID ; $warehouseID ]

If [ not IsEmpty ( $locationID ) ]
    Set Field [ TRANSACTION::locationID ; $locationID ]
End If

Perform Find [ ]

There is a condition in the script that checks if the found set is 0. In that case, there isn't a previous record to update. This should never happen if every SKU has an opening balance in the Transaction records. But in case that is overlooked, the condition uses the ExecuteSQL () to get the quantity values from the Quantity table, based on the $quantityID that was retrieved earlier in the process.

After the variables are declared, the fields are set.

Set Variable [ $onHand ; Value: GetNthRecord ( TRANSACTION::balOnHand ; $nextToLastRecord ) ]
Set Variable [ $allocated ; Value: GetNthRecord ( TRANSACTION::balAllocated ; $nextToLastRecord ) ]
Set Variable [ $available ; Value: GetNthRecord ( TRANSACTION::balAvailable ; $nextToLastRecord ) ]
Set Variable [ $onOrder ; Value: GetNthRecord ( TRANSACTION::balOnOrder ; $nextToLastRecord ) ]
Set Variable [ $onBackorder ; Value: GetNthRecord ( TRANSACTION::balOnBackorder ; $nextToLastRecord ) ]

Set Field [ TRANSACTION::balOnHand ; $onHand ]
Set Field [ TRANSACTION::balAllocated ; $allocated ]
Set Field [ TRANSACTION::balAvailable ; $available ]
Set Field [ TRANSACTION::balOnOrder ; $onOrder ]
Set Field [ TRANSACTION::balOnBackorder ; $onBackorder ]

It is possible to perform the calculations inside the Set Field script step. However, setting a variable first enables the developer to see the value in the Data View before the field is set. This can be helpful in tracking down bugs.

If you add any quantity fields (e.g. inRepair, inTransit, scrapped, etc.), you'll need to add those to this part of the script.

If this SKU is in lots, then the steps above are repeated with the Find criteria including a $lotID.

In the Transaction table, lots only affect the balance on hand, balance allocated, and balance, available.

Enter Find Mode  [ Pause: Off ]
Set Field [ TRANSACTION::itemID ; $itemID ]
Set Field [ TRANSACTION::warehouseID ; $warehouseID ]
Set Field [ TRANSACTION::lotID ; $lotID ]

Perform Find [ ]

Set Variable [ $onHandLot ; Value: GetNthRecord ( TRANSACTION::balOnHandLot ; $nextToLastRecord ) ]
Set Variable [ $allocatedLot ; Value: GetNthRecord ( TRANSACTION::balAllocatedLot ; $nextToLastRecord ) ]
Set Variable [ $availableLot ; Value: GetNthRecord ( TRANSACTION::balAvailableLot ; $nextToLastRecord ) ]


Set Field [ TRANSACTION::balOnHandLot ; $onHandLot ]
Set Field [ TRANSACTION::balAllocatedLot ; $allocatedLot ]
Set Field [ TRANSACTION::balAvailableLot ; $availableLot ]

At this point, we've duplicated values from the previous, relevant record. Now we need to update the appropriate quantity fields. Not all the quantity fields need to be updated. In our example of a Sales Order, allocation needs to increase and available needs to decrease.

Each section is marked with a section banner. E.g.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

::::::::::::::::::::: S A L E S O R D E R ::::::::::::::::::::

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

Each section can have multiple conditions. Sales Orders, for example, could be canceled or create a backorder. To keep this simple, our example will only update the allocated and available quantity fields.

#Allocated(+), Available(-)
Set Field [ TRANSACTION::balAllocated ; TRANSACTION::balAllocated + $qty ]
Set Field [ TRANSACTION::balAvailable ; TRANSACTION::balAvailable - $qty ]

That completes the creation of the Transaction record. The remaining two scripts will update the Quantity record.

We navigate to the desired Quantity record via Go to Related Record (GTRR).

Go to Related Record [ Show only related records ; From table: "QUANTITY_transaction" ; Using layout: QUANTITY_d (QUANTITY) ]

Error checking

Set Variable [ $parameters ; Value:...  ]
Perform Script [ Specified: From list: "   prep quantity values - sales order (qty, lotID)" ; Parameter: $parameters ]

prep quantity values - sales order (qty, lotID)

This and all the scripts like it have one purpose: define two variables:

1. $targetField

2. $calculationResult

These variables are passed the final script.

The context of the Quantity record is key. The context for Sales Order is the picking location. Therefore, the variables can be defined thus

Set Variable [ $targetField ; Value: GetFieldName ( QUANTITY::allocated ) ]
Set Variable [ $calculationResult ; Value: Evaluate ( $targetField + qty ) ]

Perform Script [ Specified: From list: "update quantity record (targetField, calculatedResult) " ; Parameter:...]

Every quantity field that needs to be updated goes thru the same process. In our example there are just two:

1. QUANTITY::allocated

2. QUANTITY::available

The process repeats itself for the warehouse total and then the entire total for that item. The target field is the only line to adjust:

WAREHOUSE TOTAL: Allocated(+), Available(-)
Set Variable [ $targetField ; Value: QUANTITY_isWarehouseTotal::allocated ]

//

ITEM TOTAL:: Allocated(+), Available(-)
Set Variable [ $targetField ; Value: QUANTITY_isItemTotal::allocated ]

update quantity record (targetField, calculatedResult)

The last script in the process is a generic script (a.k.a. indirection) and works for all Quantity records, regardless of context. Context is specified in the previous script when defining the $targetField. The script is short enough to show in its entirety:

Set Error Capture [ On ]
Set Variable [ $parameters ; Value: Get ( ScriptParameter ) ]
Set Variable [ $targetField ; Value: JSONGetElement ( $parameters ; "targetField" ) ]
Set Variable [ $calculatedResult ; Value: JSONGetElement ( $parameters ; "calculatedResult" ) ]

#check for record locking; loop until it is available
Loop
    Set Field by Name [ $targetField ; $calculatedResult ]
    Set Variable [ $error ; Value: Get ( LastError ) ]
    Exit Loop If [ $error <> 301 // 301 = Record in use by another user ]
End Loop

Exit Script [ Text Result: ]

The Quantity record is designed to only be updated by the script. This rule may be broken during development, but great care should be taken by the developer to not put a record lock on a Quantity record.

Presuming that development is not occurring, the default behavior is that a lock will occur only when another script is writing to a record. The chances of this are small, but if it happens, a queue will form and as soon as the first script releases the record lock, the second script can update the Quantity record.

Final Thoughts

Managing Transaction and Quantity records is the most complex part of Trayse Inventory. The goal in writing the process in this manner is to provide a great amount of flexibility while reusing as much code as possible.