We have a dashboard for shipping using an updatable query in which we have a field to hold unique serial numbers for each returned record. Problem is when records get scanned in we often get duplicates in there which is costing in fines. Is there some way to create a calculated field in the query to identify duplicates we could use to highlight in the dashboard? The field that holds the serial number is ShipDtl.Character01.
I saw something on this from a previous post but did not quite understand how to do it.
Another way might be to use a calculated field showing Row number (no need of a subquery) using ROW_NUMBER() like:
ROW_NUMBER() OVER (PARTITION BY JobOpDtl.Company, JobOpDtl.JobNum, JobOpDtl.AssemblySeq ORDER BY JobOpDtl.OprSeq )
In this example, Partition by is like Group by you would use in the subquery… so here I wanted the duplicates of the grouping of company, jobnum, assemblyseq.
The result would be an integer value showing a different row number for each occurance of your duplicate grouping.
Then in the dashboard, just highlight the row when row number > 1.