Custom Queries

From GAB Help
Revision as of 09:25, 2 February 2021 by Pooja Ramidi (talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

GAB Override for Lookup Control

Each lookup object will have an override hook that can be used to change the data associated with the control. This hook will fire at every point that lookup object is used in the system. You can find the hooks in the 55300 - 55386 range currently.

CustomQueries Lookup.png

When using an override hook you will have access to two passed variables. One is used to set the title and the other is used to set the file path location the cache file is located. When creating an override script, you will need to set the cache file path passed variable for the alternate data to be used. If this passed variable is not set, then the default lookup object will be used to populate the lookup control. Within your GAB Script you will create your data table as usual to get the data. Next you will use a new command that has been added to OCTSRS to save the data to the cache file. This command is in the Global Namespace and looks like the following.

F.Global.Object.ExportToMessagePackFile(FullyQualifiedFileName, dataTableName)
 

As of now we are strongly suggesting that you use the Global\Files\Cache\Custom directory to save these cache files. You may want to create a sub folder within this directory for your project for better organization. You should not use the Global\Files\Cache directory since this is the location for the default cache files. There is a process that runs through this folder to clean out older cache files. The file extension for a cache file should be “ser”.

When creating your SQL query, you need to ensure that you have the keys for that lookup object included in the query. So, for example for the Part Lookup Object the keys are PartNumberKey, PartNumberKeyRevision, and LocationCode. This is required since the lookup control will return the keys to the screen and the screen will populate. You should give the other columns in your query meaningful names.

Examples

Program.Sub.Preflight.Start
v.Global.sXML.Declare
Program.Sub.Preflight.End

Program.Sub.Main.Start
f.Intrinsic.Control.CallSub("CacheBrowserData")
'The passed variable that will set the title for the lookup control
v.Passed.Title.Set("My Custom Inventory Query")
'Where the FQL of the cache file is located for the lookup to load in the data
v.Passed.FilePath.Set(v.Global.sXML)
Program.Sub.Main.End

Program.Sub.CacheBrowserData.Start
v.Local.sSQL.Declare
f.Intrinsic.Control.If(v.DataTable.dtInventoryBrowser.Exists)
	f.Data.Datatable.Close("dtInventoryBrowser")
f.Intrinsic.Control.EndIf

f.Intrinsic.Control.If(v.DataTable.dtInventoryBrowserXref.Exists)
	f.Data.Datatable.Close("dtInventoryBrowserXref")
f.Intrinsic.Control.EndIf

f.Intrinsic.String.Build("{0}\Cache\Custom\{1}InventoryBrowserCacheData.ser",v.Caller.FilesDir,v.Caller.CompanyCode,v.Global.sXML)

v.Local.sSQL.Set("select SUBSTRING(A.PART, 1, 17) AS PartNumber, SUBSTRING(A.PART, 18, 3) AS PartNumberRevision,SUBSTRING(A.PART, 1, 17) AS PartNumberKey,SUBSTRING(A.PART, 18, 3) AS PartNumberKeyRevision, A.LOCATION AS LocationCode, A.DESCRIPTION AS Description, A.DESCRIPTION_2 AS Alt1Descr, A.DESCRIPTION_3 AS Alt2Descr, CONVERT(B.TEXT,SQL_VARCHAR) AS TEXT, A.PRODUCT_LINE AS ProductLine, A.QTY_ONHAND AS OnHand, (convert(A.QTY_ONORDER_WO,SQL_FLOAT)  + convert(A.QTY_ONORDER_PO,SQL_FLOAT)) AS OnOrder, A.QTY_REQUIRED AS Required, (convert(A.QTY_ONHAND,SQL_FLOAT) + convert(A.QTY_ONORDER_PO,SQL_FLOAT) + convert(A.QTY_ONORDER_WO,SQL_FLOAT) - convert(A.QTY_REQUIRED,SQL_FLOAT)) AS Net, A.CODE_ABC, A.BIN, A.UM_PURCHASING, A.UM_INVENTORY, CASE WHEN A.OBSOLETE_FLAG = 'Y' THEN CONVERT(1,SQL_BIT) WHEN A.OBSOLETE_FLAG <> 'Y' THEN CONVERT(0,SQL_BIT) END AS OBSOLETE_FLAG, A.CODE_SORT, CASE WHEN A.FLAG_INACTIVE = 'Y' THEN CONVERT(1,SQL_BIT) WHEN A.FLAG_INACTIVE <> 'Y' THEN CONVERT(0,SQL_BIT) END AS FLAG_INACTIVE, A.CODE_SOURCE, A.NAME_VENDOR, A.TEXT_INFO1, A.TEXT_INFO2, A.LENGTH, A.WIDTH, A.THICKNESS, A.PART AS PartRev, A.STOCK_BIN from V_INVENTORY_ALL A LEFT JOIN INV_EXTRA_TEXT B ON A.PART = B.PART;")

F.ODBC.Connection!CONX.OpenCompanyConnection

f.Intrinsic.Control.Try
	'Creates a datatable with the data from the sql
	f.Data.Datatable.CreateFromSQL("dtInventoryBrowser","conx",v.Local.sSQL)
	'saves the data to a cache file
	F.Global.object.ExportTOMessagePackFile(v.Global.sXML,"dtInventoryBrowser")
	f.Data.Datatable.Close("dtInventoryBrowser")
f.Intrinsic.Control.Catch
	f.Intrinsic.UI.Msgbox("Unable to create datatable and save to Message Pack")
f.Intrinsic.Control.EndTry

f.ODBC.Connection!conx.Close
Program.Sub.CacheBrowserData.End