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
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
Code: Select all
code = GetLookupCode()
if code is not None:
# We found a code. Set it.
Context.SettlementAgents[0].LookupCode = code
Code: Select all
for row in result.Rows:
if row.[lucodeColIndex].Value ...
# Do something...