Logic Extender
This mechanism allows adding more data (fields) to the existing grids within the app showing the results of a custom made Sql statement. Practically any field within the Visual database can be added to the default grids within the enabled modules.
The Lookup Column Name is the field passed to the SQL script as a parameter. You can use any available field as a parameter by adding an '@' prefix. To view the complete list of available fields, open the 'Lookup Column Name' drop-down list from the grid. The Logic Extender supports multiple parameters, separated by commas (without any spaces). The SQL script can return any number of columns but should return only one row. If it returns more than one row, only the values from the first row will be added to the main grid within the selected module.
To delete an Extended Logic rule, select the row and press Delete. Alternatively, you can uncheck the 'Is Active' column. In this case, the rule will be preserved but will not run.
The columns added with the Logic Extender will appear in the grid within the selected module the next time you refresh the data. There is no need to close the app.
Examples:
-
Generic query:
Select Column1, Column2 from Table where Id = @LookupColumnName
-
Last "IN" Transaction info for a given Part Id:
--declare @PART_ID varchar(30)
--set @PART_ID = 'A8000'
SELECT TOP (1) TRANSACTION_DATE AS [Last IN Trx Date], TRANSACTION_ID AS [Trx Id], CASE CLASS WHEN 'R' THEN 'Receipt' WHEN 'A' THEN 'Adjust-in' ELSE (CASE WHEN CUST_ORDER_ID IS NULL
THEN 'Issue-Return' ELSE 'Shipment-Return' END) END AS [Trx Type]
FROM INVENTORY_TRANS
WHERE (TYPE = 'I') AND (PART_ID = @Part)
ORDER BY [Last IN Trx Date] DESC, [Trx Id] DESC
-
Query with multiple paramters
SELECT BASE_ID, LOT_ID, SPLIT_ID, SUB_ID, ENGINEERED_BY, COMMODITY_CODE
FROM WORK_ORDER
WHERE (BASE_ID = @WORKORDER_BASE_ID) AND (LOT_ID = @WORKORDER_LOT_ID) AND (SPLIT_ID = @WORKORDER_SPLIT_ID) AND (SUB_ID = @WORKORDER_SUB_ID)
In this case the Lookup Column Name has the following value: WORKORDER_BASE_ID,WORKORDER_LOT_ID,WORKORDER_SPLIT_ID,WORKORDER_SUB_ID (you need to manually write this value into the drop-down list)
You can change the names of the fields returned by the query to friendly labels. Remember to use [ ] if the labels have spaces.
Please send your questions or comments to info@bms365.com