Highlighting Duplicates 1

[bv0719]

Need to find a way to highlight the items in a report that are duplicates. It's OK that they are duplicates, because they fall into separate groups based on an item type.

Database records for an item are assigned a P or S depending on the datatype (Purchases / Sales). See example below of formula to title the group based on the item type.

if {itm.typ}= "P" then "Purchases" else
if {itm.typ}= "S" then "Sales"

Items in the database can appear in the report in both the P group and the S group since they can be both purchased and sold. I want to be able to highlight the items that appear in both groups. The report looks like this:

Item class Group:
Item type group (see formula above):
Item ID

The item type group and Item ID are displayed for both P and S type items in the footer. The only group in the header is the item class group. The details section is hidden. The report contains values for qty on hand, weigh, cost, etc for each item ID.

Thoughts on how to select the duplicates is appreciated.

 

[CrystalizeCanada]

You could try two formulas:
This can be placed in the details (it will return a zero for each record so it can be suppressed)
//PURPOSE OF FORMULA: Build up a list of purchase item ids and a list of sales item ids
//TIMING OF FORMULA:
WhileReadingRecords;
//NOTES: Formula assumes that ItemId is a string field. If it is a number then the variables PItems, SItems and ItemId need to be changed to numberVar.
//VARIABLE DECLARATIONS:
stringVar array PItems;
stringVar array SItems;
stringVar ItemType;
stringVar ItemId;
//VARIABLE ASSIGNMENTS:
ItemType := {ITM.Type};
ItemId := {ITM.ItemId};
if ItemType = 'S' then
(
redim preserve SItems[Count(SItems) + 1]; //create a slot
SItems[Count(SItems)]:= ItemId; //store the value in the slot
);
if ItemType = 'P' then
(
redim preserve PItems[Count(PItems) + 1];
PItems[Count(PItems)]:= ItemId;
);
//FORMULA RESULT:
0

And this has to be placed in the details
//PURPOSE OF FORMULA: To display something if it is a duplicate. Alternative you could create a formatting formula which would be similar.
//TIMING OF FORMULA:
WhilePrintingRecords;
//VARIABLE DECLARATIONS:
stringVar array PItems;
stringVar array SItems;
//FORMULA RESULT:
if {ITM.ItemId} in PItems AND {ITM.ItemId} in SItems then 'Duplicate' else '';

This works for me to identify duplicates.

Gordon

Gordon BOCP
Crystalize

 

[bv0719]

Wow... Quite the detailed response. Thank you so much for taking the time to offer a solution. Question though, if the details are hidden how will the formulas affect the group results? I don't want to suppress any records I just want to highlight the records that are duplicates. The itm.typ group uses a formula as mentioned above to name the group based on the type, P or S. In design mode this group is in group footer 2. Group footer 3 (item id group) is above group footer 2. Group footer 1 (merch class group) is at the bottom and is hidden.

Example below:

Merch Class group ABC (group header 1)

Item ID 1 Item Name 100 (qty) (the item id's are all in group footer 3)
Item ID 2 Item Name 150 (qty)
Item ID 3 Item Name 0 (qty)
Item ID 4 Item Name 0 (qty)
Item Type Group Purchases (group footer 2)
Item ID 1 Item Name 10 (qty) (This one should be highlighted since it is also in purchases) - again, group footer 3 for all item id's
Item ID 3 Item Name 15 (qty) (This one should be highlighted since it is also in purchases)
Item ID 4 Item Name 12 (qty) (This one should be highlighted since it is also in purchases)
Item ID 5 Item Name 0 (qty)
Item ID 6 Item Name 5 (qty)
Item Type Group Sales (group footer 2)

Merch Class group DEF

Item ID 10 Item Name 11 (qty)
Item ID 11 Item Name 14 (qty)
Item ID 12 Item Name 1 (qty)
Item ID 13 Item Name 0 (qty)
Item Type Group Purchases
Item ID 10 Item Name 0 (qty) (This one should be highlighted since it is also in purchases)
Item ID 13 Item Name 60 (qty) (This one should be highlighted since it is also in purchases)
Item ID 14 Item Name 15 (qty)
Item ID 15 Item Name 0 (qty)
Item ID 16 Item Name 0 (qty)
Item Type Group Sales

 

[CrystalizeCanada]

I'm assuming that a particular Item say Item 1 shows no more than twice in the records - once for purchases and once for sales.
If it shows more than once send a sample of the data (just the raw data not the groups) as the formulas will likely need tweaking.

Assuming that is correct and you have hidden the detail placing the second formula in the detail would mean you wouldn't see it since it is hidden.
So we wouldn't place it there in this situation.
You can place the second formula in the Group Footer 3 where you have your Item Ids.
If it results in Duplicate then you are getting the correct result.

BTW If you get an error when you place the first formula in your report then you have collected more than 1000 items into the array and there is a limit of 1000 entries in an array. You can add a filter to reduce the number of records to bring this under 1000 for testing purposes. If it works then you can add a another array for each 1000 entries but it gets a little tedious and repetitive to do if its a lot of items.

Gordon BOCP
Crystalize

 

[bv0719]

The items will show once in each or once in one and not in another.

Received error "An array's dimension must be an integer between 1 and 1000" when attempting to run the formula.

I'll reduce the range the report is searching for testing purposes.

Had a feeling that creating an array was the only way to make this work. Are there other alternatives being that this is quite an involved formula and is limited to 1000 records in the array?

 

[CrystalizeCanada]

You have a lot of items so this is going to be an annoying formula.

One alternative would be to create a sub report placed in the detail that is used to check if there is a match.
Alternatively and a better choice in terms of performance is a SQL Expression.

Under formula fields you have the SQL Expressions heading. It is sometimes missing for various reasons, if you are using a command for example so it may not be there.

The idea is that for each record the SQL expression would check for a duplicate. From what I can see you only really need to check for the sales records to see if there is a matching purchase for the same Ttem Id.

The syntax you use here depends on the flavour of SQL you are using. The example below should work for both T-SQL (SQL server) and PL-SQL (Oracle)

CASE WHEN ITM.TYPE = 'S' THEN (SELECT COUNT(I.ITEMID) FROM ITM I WHERE ITM.TYPE ='P' AND I.ITEMID = ITM.ITEMID) ELSE 0 END

This will result in 1 when you have a purchase to match a sale and a 0 when you don't.
SQL Expressions are sometimes tricky to get working - I haven't tested the above line however something close to that would work in your situation.

Gordon BOCP
Crystalize

 

[bv0719]

I'll see if I can get the expression right. Tried using the example above but got a -201 error (syntax error). Below is what I used based on your sample.

CASE WHEN itmldg . ilgdtyp = 'S' THEN (SELECT COUNT( itmldg . ilgiid ) FROM itmldg (**this is the table name and may be the cause of the error) WHERE itmldg . ilgdtyp ='P' AND itmldg . ilgiid = itmldg . ilgiid ) ELSE 0 END

 

[CrystalizeCanada]

When using an SQL Expression there are no spaces between the table name and the field name so itmldg . ilgdtyp has to be itmldg.ilgdtyp
I'm assuming you are using a T-SQL or PL-SQL so if not the syntax may be different.

But first thing I would do if I get a syntax error is just try out a super simple example so I would start with:

CASE WHEN 1=1 THEN 'hello' ELSE 'goodbye' END

If that works I would progress to

CASE WHEN itmldg.ilgdtyp = 'S' THEN 'sales' ELSE 'purchases' END

Again if that works progress onto the next thing

(SELECT COUNT(itmldg.ilgiid) FROM itmldg I WHERE itmldg.ilgdtyp ='P' AND I.ilgiid = itmldg.ilgiid)

Then I would put them together

CASE WHEN itmldg.ilgdtyp = 'S' THEN (SELECT COUNT(itmldg.ilgiid) FROM itmldg I WHERE itmldg.ilgdtyp ='P' AND I.ilgiid = itmldg.ilgiid) ELSE 0 END

If you can identify at which point you are getting an error I may be able to help guide you




Gordon BOCP
Crystalize

 

[bv0719]

Good point. I'll try that. Thanks again for your assistance.