Row count on linked dashboard query

Kinetic ERP
,
1

I have a dashboard for job status with 3 queries. A job header query which publishes out to a job operation and job material queries.
In the job material query I’m listing all the parts on the BOM and show the purchase order number for any buy parts. In the case where there are 2 (or more) POs I get a duplicate line for each PO. I’m good with that but want to highlight the second row using view rules. So I created a calculated field in my BAQ for Count(1) Over(Partition By JobMtl.MtlSeq Order By JobMtl.MtlSeq). In my BAQ it works and lists all rows with a 1 and duplicate rows with 2, etc.
The problem is when I add the query to the dashboard I got row counts in the 100s and sometimes 1,000s. I suspect there’s something about the query being linked in the dashboard that is causing the numbers to be so randomly high.
Is there a better way of flagging duplicate rows in a dashboard?

2

You’re missing the job number.

let me expand. You are counting the number of times a duplicate material sequence is showing up. So if you have 15 mtlseq 10’s on a job, (because there are 15 assemblies) you will get a count of 15.

So look at the rows you are getting, and determine what your window is actually segregating.

3

I think that’s it. If I’m right my row count is returning every mtlSeq on every open job. Can I partition by more than 1 field? e.g. Count(1) Over(Partition By JobMtl.JobNum, Partition By JobMtl.MtlSeq Order By JobMtl.MtlSeq)

4

Yeah, as many as you need.

5

The sytax is wrong though. You only put Partition by once.

Count(1) Over(Partition By JobMtl.JobNum,  JobMtl.MtlSeq Order By JobMtl.MtlSeq)
2 Likes
6

Yes! that’s why I was getting a syntax error. Thanks