GI: Backorder Report - Join POline + SOline without duplication

The issue you're encountering with your Generic Inquiry (GI) producing duplicate rows for SKUs is common when dealing with multiple Purchase Orders (POs) containing the same items. Since you have multiple POs for the same inventory item, your join between SOLine and POLine likely results in duplicates, as each SOLine can match multiple POLine rows for the same inventoryID.

Here's a strategy to avoid duplicates:

1. Group the POLine Results

You can apply grouping logic to ensure you only return the first or most relevant POLine per SOLine. In Acumatica's GI, you can use aggregates and grouping to control the results and remove duplicates.

2. Filter POLine Based on Relevant Conditions

Instead of joining all the POLine records, you can add a filter or condition to choose the most appropriate PO line to display. This could be the earliest promised date, latest receipt date, or PO in the latest status. Here’s how you can approach this:

• Earliest Promised Date: This will ensure you get the earliest PO line that promises the backordered item, which might be what your customers care about.

• Last Received PO Line: If you're looking for the most recent receipt to match against the SOLine.

Specific Steps:

1. Modify the Relation in GI:

Instead of a simple join, add a filter to select the most relevant POLine for each SOLine. You can use a Max function on PromisedDate or use RowNumber() logic to pick the earliest/latest POLine.

2. Group by SOLine Fields:

In the GI Results Grid, group the results by key fields from SOLine to ensure the rows are not duplicated.

3. Use Subquery or Aggregate Function for POLine:

• In the GI Join Tables section, modify the POLine join so that you only fetch the earliest or most relevant record for each SOLine. You can do this by selecting the Minimum Promised Date or Maximum Received Date.

Example query structure:

sql

SELECT SOLine.InventoryID, SOLine.OrderNbr, MIN(POLine.PromisedDate) AS PromisedDate FROM SOLine JOIN POLine ON SOLine.InventoryID = POLine.InventoryID GROUP BY SOLine.InventoryID, SOLine.OrderNbr

4. Consider Custom SQL in GI:

If needed, you can build a custom GI that uses SQL functions like ROW_NUMBER() to select the most relevant PO line for each SKU.

Example using ROW_NUMBER():

sql

SELECT SOLine.InventoryID, SOLine.OrderNbr, POLine.PromisedDate FROM SOLine JOIN ( SELECT *, ROW_NUMBER() OVER (PARTITION BY InventoryID ORDER BY PromisedDate) AS RN FROM POLine ) AS POLineFiltered ON SOLine.InventoryID = POLineFiltered.InventoryID AND POLineFiltered.RN = 1

5. Adjust Sorting and Filtering:

Add sorting on the PromisedDate or any other key field in your report so that the system selects the most relevant POLine for each SOLine.

😀

PS- “Used an AI tool when genereting this response”

Hey @chameera71 , i tired all the variations of what you’re suggesting and wasn’t haven’t any luck.

Grouping, will take away the duplicated lines… but increase the UnshippedQty and OrderedQty X lines.

Aggregate function didn’t change anything, on multiple date ranges.

The only way around this for now, is if i have purchasing leave old promised dates. The insert a condition POLine.PromisedDate>Is Greater Than>@Today

Not ideal though.

You’ve made it clear what you’re doing, but not necessarily (to me) the end result you want. You want to see only one PO associated with the backordered SO, but based on what? Is the PO actually linked to the SO? If so, your join is wrong. If not, then the only way you’ll get around this is by making your join more complex, based upon arbitrary conditions that I can’t recommend because I don’t understand what the end goal is.

Hey @darylbowman ,

My end goal is to give some key customers a report on their backorders, In doing so i want to insert the promised date. The only way i could find todo that was to insert the POLine.PromisedDate.

PO’s are not linked to SO’s.

This is how it should look. I can achieve this by setting the condition - POLine.PromisedDate>Is Greater Than>@Today - This is obviously not great practice, but a simple workaround to see if i could get it to show correctly.

LS35 is on Back order twice. 1 Box for each order number. This is the correct Number

But because there are multiple open Purchase Orders it will display like this. Regarldess of the promise dates.
 

If I try to group in any way it will multiply the qty for all fields. I really only want to show the customer the single line item for that order and show them one date.

I understand this all makes sense in my head as im in it, but if anything else is unclear please let me know.

I created three backordered SOs containing 1.0 ‘CABINET’ item each (total: 3.0)

I created one PO containing 1.0 ‘CABINET” item (total: 1.0)

Your GI looks like this:

But to me, this looks like it’s reflecting the fact that there is 1.0 ‘CABINET’ ordered for each SO (total 3.0), which is not the case.

Is this the result you’re expecting?

Are you creating SO with items with ‘marked for PO’? In that case you can trace them with SOLine table to POLine through SOLineSplit, if I remember correctly.

@darylbowman , for that instance that would be correct yes.

Keep in mind the GI is Customer Specific. So the GI would never be viewed like this.

My issues is the reverse. Make one SO for Cabinet, the make 3 PO’s for the cabinet with either the same or different promised dates.

Hey @Yulia Steinberg , Currently no we’re not.

My understanding was that function would be for if there isn’t already a Vendor PO in motion? Typically for these types of items, there would already be multiple PO’s on the go for this product and others like it. This is due to shipping cut off times, vendor not always fulfilling PO qty and future orders. 

I think you can link SO lines to existing PO lines

Interesting if so. I’ll test this morning and try and answer some questions i have before coming back.

I did have a short play, i need to see how this works into our workflow currently. I also noticed we dont have blanket order (purchase Orders) within our license (Aus).

Also need to figure out how todo a GI for it to pull the right Data.