Transaction line quantities unassigned to Lot # within SO lot quantity saved search

I half-suspect that there is a trivial solution to this that I just don’t have enough experience to spot. I’m trying to run a Saved Search to show the Quantities of assigned and unassigned Lot #s within SO transaction lines, split by Sales Order / Document ID. In other words, each row would basically represent:

Sales Order # Transaction Line Sequence Number Item ID Line Quantity Lot # Lot Quantity

I’d like to see output that looks like the following mock-up:

Sales Order # Transaction Line Sequence Number Item ID Line Quantity Lot # Lot Quantity
SO101 1 ATG001 20 A1 10
SO101 1 ATG001 20 B1 7
SO101 1 ATG001 20 —– (not assigned to a lot) 3
SO101 2 MOS053 8 7890 2
SO101 2 MOS053 8 5328 6
SO101 3 NAR020 10 Etc etc etc

 

The problem I’m running into: I cannot figure out how to surface the unassigned entry as its own row, where unassigned (the quantity out of the current transaction line that is not yet assigned to a Lot) is essentially treated as its own unique Lot # within the transaction line. This is especially problematic when a single transaction line has a subset of its quantity set to a Lot # and the remainder unassigned.

My best attempt within a Saved Search so far has looked like the following:

Type: Sales Order Saved Search

Criteria:

  • Type: is Sales Order
  • Status: is any of Sales Order:Partially Fulfilled, Sales Order:Pending Billing/Partially Fulfilled, Sales Order:Pending Fulfillment
  • Main Line: false
  • Tax Line: false
  • Item : Type: is any of Inventory Item
  • Shipping Line: false

Results:

  • Document Number: {tranid}
  • Item ID: {itemid}
  • Quantity: {quantity}
  • Line Sequence Number: {linesequencenumber}
  • Line Unique Key: {lineuniquekey}
  • Transaction Serial/Lot Number: {serialnumber}
  • Transaction Serial/Lot Number Quantity: {serialnumberquantity}

I can see several ways to calculate the total unassigned quantity for the transaction line within each row of the saved search (for example, getting the total assigned quantity using something like sum/* comment */({serialnumberquantity}) OVER(PARTITION by {lineuniquekey}) and then creating a separate formula field to subtract that out of the total line quantity, OR using a separate Summary search).

But this doesn’t get me to where I want to be: with a separate row for the unassigned quantity of each transaction line.

Can anyone see a way to accomplish that in a single saved search? Or would I need to split this work across two saved searches?

Many thanks in advance!

Add Comment
1 Answer(s)

Edited title to fix mistake: Meant to say “Transaction line quantities unassigned to Lot #”, not “Transaction line quantities unassigned to SO”

Rookie Answered on March 6, 2023.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Become a Sponsor   Become a Sponsor