Add LotDescription field in BAQ

[pilio.lee]

Hello everyone,

I’m trying to add a LotDescription field to my On hand Report. So I added PartLotDescription via PartLot. However, after that, only parts with lot numbers are displayed.
How can anyone help me with this?
It seems like a very simple problem, but I am a very beginner. Help me.

[gpayne (Greg Payne)]

@pilio.lee change the join to include all rows from part

[pilio.lee]

Thank you! As you said, parts without lot numbers were also displayed. But another problem arose. The same lot number of one part appears in multiple lines.

[gpayne (Greg Payne)]

@pilio.lee Brandon posted a good technique to find the issue here.

BAQ duplicate Row investigation pro tip

So I know we’ve all been there banging out heads against the wall trying to figure out why we are getting duplicate rows. I found myself doing the same thing this morning, and was starting to walk down the road of recreating the query in SQL (time suck) then I remembered a technique that I had used in the past to figure it out relatively quickly. Simply add the SysRow ID of every table that you are using in your query. (Obviously this won’t work with grouped queries/subqueries). Once you add th…

[pilio.lee]

By the way you told me, I found that a duplicate was happening.
How do I solve this? Thank you in advance

[gpayne (Greg Payne)]

@pilio.lee So that it a bit more complicated. Start with the Lot that is being duplicated. Is there one row that is different? If so criteria could work. If it is a part bin then you may need to make a subquery to pass only one row back to the main query.

Looking at how you have Part and then PartLot you could have multiple lots which would be normal. You may need to get quantities by lot or you will need to build all of the lot descriptions in a subquery as one long description.

[miltonware]

Hi Guys
to remove duplicate results I suggest try BAQ >Subquery Options - Result Set Rows - select Distinct

[pilio.lee]

I’ve tried as you said, but still the duplicates appear. But thanks for the advice.

[cchang]

Try bringing all the fields for both Part and PartLot and then compare the duplicate rows if any of the fields differ between the two duplicates.

[miltonware]

Hi pilio.lee,

1. check your results to make sure that they are truly duplicate. NB if any column value is different then it will cause the row to display.
   2 You may not need the sys row ID
2. I suggest at >Phase Build temporally remove the the PartCost Table and run Analyze again, (why? because PartCost table will show a results row for each part cost there is, even if the cost is not shown as a selected display field). To stop this you will need to look at the PartCost criteria or the Part -PartCost link so that part cost is restricted to only one value per part.
   Milton