How-To: Using Lookup Tables in Automation Snippets

Questions about and code samples for automation process code snippets within Select.
Post Reply
BobRichards
Posts: 1133
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

How-To: Using Lookup Tables in Automation Snippets

Post by BobRichards » Thu Jan 28, 2021 5:17 pm

Automation snippets are free to search through Lookup Tables in their logic. This will show how to do a search by filtering both in the database (to speed the transmission of data) and in the results returned to the snippet (in case your search brought back too many matching rows of data).

Our view of any lookup table is similar to how to the data is represented in when editing the table in Select. By knowing the table name, we can tell Select to get me all the rows that meet certain search criteria (a database operation). If Select returns zero rows then the search didn't find any matches. If Select returns one row then use it. However if Select returns multiple rows, then the database search criteria may not have been sufficient and you must do additional filtering in Python.

In this example, we are going to search the Settlement Agents lookup table for a match to our criteria. If we get that match, then we will set the existing SA contact to that lookup code. To do this, we need the information from columns: "City", "Lookup Code", "StateOfBus". The spelling of the column names is case sensitive.

The first step is to determine the column index (0 or greater) for columns the Python snippet will handle directly. This query does not return row data. We can't use the row data until we know which columns hold the information we are looking for. We will get the table schema and learn from it the necessary columns.

Code: Select all

from System import *
from SoftPro.ClientModel import *
from SoftPro.Select.Client import *
from  SoftPro.OrderTracking.Client.Lookups import *

# Snippet context is Order (runs  "Every time an order is saved")

def GetLookupCode():
    # Get the ILookups service.
    lookupsMgr = Context.Root.GetService(ILookups)

    # Create a LookupQuerySpec with the table name and get a list of the columns.
    spec = LookupQuerySpec()
    spec.Table = 'Settlement Agent - TX.AUS'
    # We only want the list of database columns - NOT the actual data - so get the schema.
    spec.SchemaOnly = True
    schema = ILookups.QueryTable(lookupsMgr, spec)

    # Determine the column numbers for the values we want in a row.
    lucodeColIndex = None  # "Lookup Code" column
    for index, item in enumerate(schema.Columns):
        if item.Name == 'Lookup Code':
            lucodeColIndex = index
            
    # If we couldn't find required column in the table, do nothing.
    if lucodeColIndex is None:        
        return None
At this point we know which column to find our data in. Now its time to search for data. In this first example, we know there should only be a single matching row. If we get a different result then we should not return a settlement agent lookup code. This also requires we create a filter expression. More information on creating filter expressions is located in the SelectSDK help file under topic How-To / Lookup Tables / Filter a Query. We only want the rows with settlement agents in "Austin" that can do business in "TX".

In our example code below, our logic says we must enforce that if there are more than one rows returned (or none), do nothing. To make sure there is not more than one row, we tell Select to return at most 2 rows (spec.MaxRows = 2). Without the MaxRows property, Select could return a huge number of matches and that would take lots of wasted time and CPU usage that we don't need. This is a very efficient way to prevent receiving more rows than you could use. Or code will return the lookup code if it found just one row. Otherwise it will return None.

Code: Select all

    #  Create another LookupQuerySpec with the table name and and provide row search criteria.
    spec = LookupQuerySpec()
    spec.Table = 'Settlement Agent - TX.AUS'
    spec.Filter = '[City] = @theCity and [StatesOfBus] like @states'
    spec.FilterParameters.Add('theCity', 'Austin')
    spec.FilterParameters.Add('states', '%TX%')
    spec.MaxRows = 2
	
    # Query the table. Get back an "ILookupTable" object with zero or more rows.
    result = ILookups.QueryTable(lookupsMgr, spec)

    if result.Rows.Count == 1:
        # Found a single match. Return lookup code value from the first row.
        return result.Rows[0][lucodeColIndex].Value
    else:
        # Zero or multiple matches. Do nothing.
        return None
All that's left is to tack the calling code to the bottom.

Code: Select all

code = GetLookupCode()
if code is not None:
    # We found a code. Set it.
    Context.SettlementAgents[0].LookupCode = code
If you plan on getting multiple rows of data back from the search and need to filter the data further, just use a "for" loop the search through each row of data. In our case, you can filter the data and return the first lookup table settlement agent lookup code that meets you need. You can add additional column index lookups to the schema search to get any column of data you need.

Code: Select all

for row in result.Rows:
    if row.[lucodeColIndex].Value ...
    	# Do something...
Bob Richards, Senior Software Developer, SoftPro

Post Reply