Filtering out duplicates on table reports

Question

I have a table report that shows all the claims that we closed for the month prior. Sometimes we have numerous claims that share a customer name and order number because the customer may have a large number of items or they may be different claim types. We already have fields that provide the totals across all the customer's claims that have matching order numbers and those are the fields the report is pulling in. As such, I need the report to leave off duplicate claims with matching order numbers and only provide the information for the claim with the earliest record ID. That way, each customer will show only once on the report so that the totals on the report are correct.

Is this possible using a report formula or some filtering setup I am not seeing.

Thanks so much!

------------------------------
Rhonda Jones
------------------------------

markshnier__you · Answer

The low tech solution is to make a report of Type Summary (ie a Summary report) and group by Customer by Order. That way you will get 1 row per unique Customer Order.

But if you need a table report, then I suggest making a Formula Checkbox field and use a Formula Query to identify the first duplicate, setting the checkbox to true. Then filter the report to only include the first duplicate.

var text QUERY =
"{7.EX.'" & ToText([Customer Name]) & "'}"
& " AND "
& "{9.EX.'" & ToText([Order Number]) & "'}";
// note in the above, set the 7 and 9 to be the field ID's of the customer name field and the Order Number, respectively.

// next we retrieve a set of Record ID's fo that Query

var text AllRecordIDs =
ToText(
GetFieldValues(
GetRecords($QUERY),3));

// next we flag as true if the First Query result is the Record we are on.
IF(ToText([Record ID#])= Trim(Left($AllRecordIDs)), true)

// post back if there are any syntax errors and I did not actually test.

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

Forum Discussion

Filtering out duplicates on table reports