Hello.
I struggle with this still… How do I get rid of duplicate results? In this particular BAQ…
Everything is fine: NoDuplicate.baq (27.4 KB)
Add OrderDtl and it repeats every line, so instead of 4 lines it is x3 and returns 16:
RepeatedLines.baq (30.1 KB)
I’ve messed around with the joins between OrderHed and OrderDtl but nothing has fixed it.
Any tips?
Thank you.
OrderHed → OrderDtl is a one to many relationship.
You need to filter out what you don’t need.
Either through join conditions or criteria.
Are there other ways to manipulate the join conditions or just here?:
No I meant what you match on.
I think my issue is I am connecting Sales Orders with Purchase Orders through a custom field that links the two by putting the corresponding SO # in the UI of the PO. I am just at a loss as to why it doesn’t duplicate until I attach OrderHed with OrderDtl. Is there a way to do a more in-depth dive into matching?
Because for each OrderHed line you may have MULTIPLE OrderDtl line(s).
What are you attempting to do and what is a template of the result set you are after?
OrderDtl should be your primary table then…link that to the PO detail so you get the line level matching. Make OrderHed a secondary table…
OrderDtl >> PODtl
OrderDtl >> OrderHed
Good morning…
Because for each OrderHed line you may have MULTIPLE OrderDtl line(s).
Is there a way to cancel them out?
What are you attempting to do and what is a template of the result set you are after?
I have been tasked with making a report that shows certain sales orders linked with their purchase orders. We don’t have Buy to Order, but instead a custom field in the Purchase Order entry that the entry person puts the SO # in to link the two together. If by template you mean what the ideal results look like, it would be this:
OrderDtl should be your primary table then…link that to the PO detail so you get the line level matching. Make OrderHed a secondary table…
OrderDtl >> PODtl
OrderDtl >> OrderHed
I tried the suggestion above but ran into an issue where I realized I had to use our custom field to link the sales orders to the purchase orders so I substituted PODetail with PORel, as that is where our custom field is located.
I loaded the BAQs and I agree with @jmpconj. You should begin by looking at your joins.
I messed with the joins but I was unsuccessful in reducing the duplication.
DuplicatesOrderDtl.baq (25.0 KB)
Then I’d suggest switching to OrderRel as your primary. That should give you the one-to-one record match (to PORel) that you need.
As you see the LINE number is different and that is why it looks like multiples. What would you like to see instead? Joining to another table will not remove duplicates if you have that line number in there as that field is not a duplicate on any of the above in the image. I see no duplicate data… If you wanted you could create a Report with the PO Num, SUM Qty on each Part @ Price to reduce only if you either remove the line numbers or turn them into an aggregate. You need to decide what you are really after but there is no duplication above that I see as the LINE is different on each ROW.
inner join Erp.PORel as PORel on
OrderDtl.Company = PORel.Company
and OrderDtl.OrderNum = PORel.Number01
I’d bet on this join. Add PO line and PO release to your select list to provide the joined detail that isn’t currently visible. And, if you’re linking a PO to specific sales order lines, possibly even sales order releases, that detail should be included in the joins.
Still will not remove anything if he is listing EACH ROW’s LINE COLUMN… Look @ image.
But does it highlight a cause? For example,
Order | Line | Release | PONum | POLine | PORelease
------+------+---------+-------+--------+----------
1 | 1 | 1 | A | 1 | 1
1 | 1 | 1 | A | 1 | 2
1 | 1 | 1 | A | 2 | 1
There’s a reason, it’s just a matter of identifying what that is and making decisions to reduce query scope or revisit how things are linked or something like that. Or, retaining detail in the results because someone’s bound to link multiple PO’s / PO lines / or PO releases to a single sales order and that might be important to know.
Yes, if he were utilizing the PORel table and had multiple releases and the join was not defined properly he might end up with duplicated lines in that scenario, but from his image that did not look like that was in his dataset.
Apologies, John, @kananga, he does say that would be the ideal and yes he will need to decide how to aggregate his PORel data as that will generate a ROW of data for each release even though he may only want one line of data per ORDER LINE this is unclear in his image.
@mlp what LINE are you looking for? PORel line or Order line?
You can subset the data with a subquery against PORel to only generate ONE PORel LineNum per OrderNum by using the PORel as primary like John listed. I did not look at/load the BAQ to verify what your original query looked like, but John looks to be defining the best starting join for you.
Hello. Sorry for the delay.
The screenshot I showed was what I wanted. What was happing is those were turning into 169 lines. So the lines were multiplying themselves.
Then I’d suggest switching to OrderRel as your primary. That should give you the one-to-one record match (to PORel) that you need.
This worked. Thank you. May I ask more detail about your deduction?
Lucky guess…LOL. 
Seriously it’s just something I came across with BAQs linking SO/PO tables. We do a lot of dropship business so it’s come into play for us often. Release is the lowest level of detail so you just have to connect them that way.
PORel if matching the OrderRel in 1-to-1 relationship would get you results you were looking for. You might however still have multiple PORel to more than one OrderRel, but you should only get the single matching items as you were matching on other parent tables previously you were getting duplicate rows for each line in the Order/PO depending on the duplicate item.