How to return a row matching two or more fields

Anvil Q&A
1

I’m having a hard time figuring out how to return a row from data tables that exists, AND matches 2 or more values.

image
image1126×457 14.4 KB

I want to query the table to see if “po” == 1075713712 AND GTIN==20793519277110. IF FOUND, then I want to update only this row with a new quantity.

The query will change each time depending on whish PO/GTIN I’m working on.

I was trying this, but it doesnt seem to work correctly.

searchResults = app_tables.completedlineitems.search(
  q.any_of(
    po=q.like(PO_IN),
    GTIN=q.like(GTIN_IN)

Once I match this one row, I will update the QTY.

2

q.any_of will return all the rows that satisfy any of the criteria.
q.all_of will return all the rows that satisfy all of the criteria.

So you could just replace any_of with all_off.

But in a simple case like this, you don’t need to use the q queries, you can simply do:

searchRseults = app_tables.completedlineitems.search(
    po=PO_IN,
    GTIN=GTIN_IN
  )
if len(searchRseults) == 1:
  row = searchRseults[0]
  row['qty'] += 1
2 Likes
3

nice! I was hoping to use a simple approach.

I will try that now and see how it works.

What are we using this for? row = searchRseults[0]

4

@stefano.menci is trying to determine if there is more than one result for the two pieces of data you are searching for in the table.

You could also use .get() if you are expecting your table to only have one unique instance of those two columns.

it would be like:

row = app_tables.completedlineitems.get(
    po=PO_IN,
    GTIN=GTIN_IN
  )

if row is not None:
  #do something to or return the unique row

app_tables.table.get() will raise an exception if there is more than one result for where those two pieces of data intersect (PO_IN and GTIN_IN in this case). You could explicitly catch this exception if you wish.

From the labels of the columns I am assuming it depends on the system that this data comes from, and if the same exact item is allowed to be purchased on multiple lines of your PO.
Essentially you need to plan and be prepared to handle whether your data will be unique or not.

1 Like
5

this is what I have so far, about to test.

This is an app that we will use to scan POs, and then scan the Case GTIN/UPC to ensure everything gets packed on the order.

So I have a ACTIVElineitem table, and completed table.

With each scan, I’m subtracting cases from ACTIVE, then adding them to completedtable. once Active qty == 0, Im deleting that row.

def search_active_scan_items_GTIN(GTIN_IN, PO_IN):
# Minus 1 Case from Scan Line Items - ACTIVE
searchActiveResults = app_tables.scanlineitems.search(po=PO_IN, GTIN=GTIN_IN)
if len(searchAvtiveResults) == 1:
print(“SCAN FUNCTION: Found active row in DB. Subtracting 1 Case”)
row = searchActiveResults[0]
row[‘CASE_QTY’] -=1
getSKU = row[‘SKU’]

searchResults = app_tables.completedlineitems.search(po=PO_IN, GTIN=GTIN_IN)
if len(searchResults) == 1:
  print("SCAN FUNCTION: Found Completed row in DB. Adding 1 Case")
  row = searchResults[0]
  row['CASE_QTY'] +=1
  
searchCompletedResults_CreateRow = app_tables.completedlineitems.search(po=PO_IN, GTIN=GTIN_IN)
if len(searchCompletedResults_CreateRow) == 0:
  print("SCAN FUNCTION: Row NOT FOUND in Completed DB. Adding NEW ROW")
  row = searchCompletedResults_CreateRow[0]
  #row['CASE_QTY'] +=1
  app_tables.completedlineitems.add_row(GTIN=GTIN_IN, SKU=getSKU, CASE_QTY=1,po=PO_IN)
    
# Looking for rows with CASE_QTY=0. If found, deleting row.
if app_tables.scanlineitems.get(CASE_QTY=0):
  row.delete()
6

Thanks for the explanation. I understand it now and will build out some error handling/exception catching.

These are PO’s, and Im loading them one at a time for barcode scanning into the active table. So they will have the same PO#, with different SKU / QTY and GTIN.

7

Stuff like the above should probably be refactored to be more like this, for speed and readability:

for searchResult in app_tables.completedlineitems.search(po=PO_IN, GTIN=GTIN_IN):
  print("SCAN FUNCTION: Found Completed row in DB. Adding 1 Case")
  searchResult['CASE_QTY'] +=1
  break

the app_tables.table.search() result will always be an iterator, regardless of if it is empty, meaning if it is empty the block of code inside of it will never be run, and if it is not empty, after running the first item, break will stop the iteration from continuing past one item.

The reason this is faster is that opening and iterating over one item in an iterator is faster than branching the code with an if statement.

Also the last bit:

Should also probably be a .search(), since you want to delete all of the rows that are not being needed any more, and not just one.

2 Likes