Cycle Count BAQ For Historical Data

Kinetic ERP
1

Hello. I wrote a BAQ to get a list of all part numbers per warehouse that would be counted in a cycle count. It was created because we were having trouble with parts not being added to cycles automatically even though their last counted date and ABC code would have made them eligible to be counted in the most recent cycle. This was resolved, but when I run my BAQ, I’m seeing something weird and I’m hoping someone can explain it to me. In the Entry Date column (from CCTag), it shows multiple dates for the same part/cycle period/cycle sequence. The employee entering the count is entering it on a single date. Can anyone think of a reason it would record multiple dates?

image

2

My guess is a join or relationship in your BAQ is the issue. It looks like its showing you every “Entry Date” against each part, instead of the one date that is tied to that part.

What’s your table relationship between CCTag and whatever you’re joining it to?

3

Join type is: Matching rows from CCPeriodDefn and CCTag

4

Right, so you’re going to get a result for every entry date that matches the Plant, Year, and Seq from CCPeriod… regardless of PartNum.

image

Can you join CCTag on CCDtl so you can also add PartNum?

5

CCPeriodDefn → CCTag should work if you join on Company, Plant, PeriodYear=CCYear, PeriodSeq = CCMonth.

6

For CCPeriodDefn → CCTag, I do have the table relations you recommend. That was my original setup.

image
image1478×195 11 KB

I added a join from CCDtl to CCTag as recommended, but now the Entry Date column is blank. This is my table relations for that join:

image
image1483×181 9.78 KB

7

For CCDtl → CCTag, that looks completely wrong (it looks like the preprogrammed index for CCTag is erroneous, so that’s not really on you). The real join between those tables is on Company, Plant, Warehouse, CCYear, CCMonth, CycleSeq, FullPhysical and PartNum.

The query as a whole should be CCPeriodDefn → CCDtl → CCTag.