A log data step is one that either logs data to a custom table that has been pre-defined or that updates data in an existing table. The log data step will not create a database table that doesn't already exist.

 


LogData Step Definition

 

 

 

 

The fields are described below:

Step Name - reference used when building the step and defining the step sequence. For example WHouse

Step Description - Since there is no user interface on this step type this can be the same as the step name

Next Step - here the user will be able to select the name of the next step. So for example, after warehouse, they will want to go to Item

Log Data Table - The user will select the database table to log data to or update.

Insert/Update - This option defines if the log step will add a new row to the Log Data table or update an existing row

 

Map to Insert- If you have selected the Insert option then the Map button opens the standard mapping form as below allowing you to map steps to columns in the selected database table.

 

Form to map Steps to Columns for an Insert

 

Note that it is not only possible to map steps to column names, you can also map fixed values or standard values.

Examples of these would be that if you wanted to log a Process Status of 5 for a row, you could simply map a fixed value rather than creating a defaulted input step with a value of 5.

Standard values are useful to log things like the user login name or even the function area name.

 

Map to Update - If you have selected the Update option then clicking on the Map buttons opens a different form, this time with two tabs as shown below. The update mapping tab is exactly the same as the mapping for an Insert step where the user selects which columns to update and which step values to log when updating. The Condition Mapping tab is where things change. The key thing when doing a SQL update is to ensure that only the correct row/s are updated. If only a subset of rows are to be updated then you need to define the conditions to select the rows to update.

 

Update Condition Mapping

 

In the example above, only rows where the PONumber column is equal to the scanned PO step value and where the Process Status is equal to 5 will be updated. The form defaults with the option to use one OR in the update logic (for example PO = {PO} OR ProcessStatus = 5. If another OR is required, the Add Group button will add one in.

 

Of course, the SQL statement for multiple conditions can be tricky to build in the manner shown above so in addition to doing mapping to build the WHERE clause we also allow users to the view the SQL statement that will be used and if required, modify it. If a user selects the View SQL Button, they will see a form similar to the one below. This form allows them to edit the SQL statement and replace the system generated SQL with their own. When the form is opened the Generated Text is the same as the Saved Text. The user can edit the Saved Text and then save to replace the SQL generated by TransLution with their own. If they make any mistakes, they can at any point copy the Generated Text back and overwrite the custom Saved Text.

 

 

 

 

 

Log Data Step - Table Structure

The table that the log data step will log data to MUST have the following columns in addition to the columns that will be mapped for logging:

ID - a unique key ID. The Column name does not have to be ID - it can be anything and we recommend using a name that fits with the table name e.g. PalletID on a table for Pallet data

JOB_ID (int) - The scanner job ID will be logged here

Scanner (varchar) - The scanner name will be logged here

ScanTime (datetime) - This is a time stamp field


There are also optional columns for Logdata with an Update:

If the columns AUDIT_APP_USER_ID, AUDIT_TIME and AUDIT_ACTION exist in the table being logged to then they will be updated when the row is updated. This allows you to record who made the last change to the logdata table. If the columns don't exist they will be added by TransLution the first time the function executes.