Difficult Grouping Problem

[Former Member]

Hi I have a report that looks like this:

          item to change                                        item value

Dr. John (Group 1)

          Wrong Client                                               10      

          Service Code                                                5

          Service Code                                                5

          Duplicate Entry                                             4

          Service Code                                                5

Dr. Michele

          Wrong Client&Service Code                       10&5

          Location$Service Code&Duplicate Entry    1&5&6

What I want to do is group based on the item to change and then show the count of how many each item to change shows up in the group. However, for fields with multiple items to change I want to break them up based on the '&'. So my report would look like this:

                                                 count

Dr. John

          Wrong Client                      1

          Service Code                      3

          Duplicate Entry                   1            

Dr. Michele

          Wrong Client                       1

          Service Code                      2

          Location                              1 

          Duplicate Entry                   1                                                                       

[abhilash_kumar · Accepted solution]

Hi Nate,

CR cannot 'create' a new row however, you can insert a new line character and 'make it look like a row'. Here's what you need to do:

1) Create a formula with this code and place this on the Details Section:

shared stringvar array items;
shared numbervar array cnt;
numbervar x;
if instr({Item_to_change},'&') = 0 then
(
    If Not({Item_to_change} IN items) then
    (
        x := x + 1;
        redim preserve items[x];
        redim preserve cnt[x];
        items[x] := {Item_to_change};
        cnt[x] := 1;
    )
    Else
    (
        local numbervar i := i + 1;
        for i := 1 to ubound(items) do
        (
            if items[i] = {Item_to_change} then
            (
                cnt[i] := cnt[i] + 1;
                exit for;
            );
        );
        0;  
    );
)
Else
(
    local stringvar array temp := split({Item_to_change},'&');
    local numbervar j;
    local numbervar i;
    for j := 1 to ubound(temp) do
    (
        if temp[j] IN items then
        (
            for i := 1 to ubound(items) do
            (
                if items[i] = temp[j] then
                (
                     cnt[i] := cnt[i] + 1;
                     exit for;
                );
            );
        0;
        )
        Else
        (
            numbervar z := z + 1;
            redim preserve items[z];
            redim preserve cnt[z];
            items[z] := temp[j];
            cnt[z] := 1;            
        );
    );
0;
);
'';

Replace {Item_to_change} in the code above with the corresponding database field for Item_to_change.

2) Create a new formula with this code and place it on the Group Header for Doctor:

shared stringvar array items := '';
shared numbervar array cnt := 0;
numbervar x := 0;
numbervar z := 0;

3) Create a formula to display the Items and place this on the Group Footer for Doctor:

shared stringvar array items;
local numbervar i;
for i := 1 to ubound(items) do
(
    local stringvar s := s & items[i] & (if i <> ubound(items) then chr(13));
);
s;

Right-click this field > Select format field > common tab > check 'Can Grow'

4) Create one last formula to display the counts and place this beside formula #3 on the same section:

shared numbervar array cnt;
local numbervar i;
for i := 1 to ubound(cnt) do
(
    local stringvar s := s & totext(cnt[i],'#') & (if i <> ubound(cnt) then chr(13));
);
s;

Right-click this field > Select format field > common tab > check 'Can Grow'

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

I love you, you are awesome!

I think there is a small bug in the code though. When I have a group that look like this:

6

6

4&12

6

4

6

It only starts counting from the 6&12 line, and it doesn't count the 12, so my output looks like this.

     item_value     Count

          4                    2

          6                    2

Hmm when I follow the code it seems like when there is a '&' to split on and the number is not already in the 'items' array then since you used a new variable 'z' the items array be overwritten. I am going to try to come up with a solution, and maybe you can too! Thanks for all the code to get me started!

Hmm I am confused again. What is the first formula you gave me returning? Sometimes it returns 1 and sometimes 0, and I can't figure out why.

[abhilash_kumar · Accepted solution]

Hi Nate,

I figured out what's going wrong with the code:

Please modify formula #1 to:

shared stringvar array items;  
shared numbervar array cnt;  
numbervar x;  
if instr({Item_to_Change},'&') = 0 then  
(  
    If Not({Item_to_Change} IN items) then  
    (  
        x := if items[1] = '' then x + 1 else ubound(items) + 1;  
        redim preserve items[x];  
        redim preserve cnt[x];  
        items[x] := {Item_to_Change};  
        cnt[x] := 1;  
    )  
    Else  
    (  
        local numbervar i := i + 1;  
        for i := 1 to ubound(items) do  
        (  
            if items[i] = {Item_to_Change} then  
            (  
                cnt[i] := cnt[i] + 1;  
                exit for;  
            );  
        );  
        0;    
    );  
)  
Else  
(  
    local stringvar array temp := split({Item_to_Change},'&');  
    local numbervar j;  
    local numbervar i;  
    for j := 1 to ubound(temp) do  
    (  
        if temp[j] IN items then  
        (  
            for i := 1 to ubound(items) do  
            (  
                if items[i] = temp[j] then  
                (  
                     cnt[i] := cnt[i] + 1;  
                     exit for;  
                );  
            );  
        cnt[j];
        )  
        Else  
        (  
            numbervar z := ubound(items) + 1;  
            redim preserve items[z];  
            redim preserve cnt[z];  
            items[z] := temp[j];  
            cnt[z] := 1;              
        );  
    );  
0;  
);  
''; 

Highlighted in Red are the changes I made.

Hope this helps.

P.S: Ignore the 1s and zeroes. The 1s are the first counts of that Item whereas the zeroes are simply there to have the same return types in the if else loop.

-Abhilash

[Former Member · Accepted solution]

Ok great! It is almost perfect now. The only small problem I have is for Dr. Michelle:

          item to change                                         item  value                                

Dr. Michelle

          Wrong Client&Service code                         10&5

          Location&Service Code&Duplicate Entry     1&5&6

This ouputs an extra zero above the output like so...

                                             count

Dr. Michele

                                                 0

          wrong client                    1 

          Service Code                  2

          Location                          1

          Duplicate Entry               1

Other than this small error everything is working perfectly. Thanks for all the help.

****

Ok I think I figured it out. I added some code to line 49 in formula 1, so it is now.

numbervar z := if items[1]= '' then z+1 else ubound(items) +1;

Thanks for all the help!

[abhilash_kumar · Accepted solution]

OK, I see where's it's going wrong.

Modify Line # 49 in the formula above to:

numbervar z := if items[1] = '' then z + 1 else ubound(items) + 1;

-Abhilash

[Former Member · Accepted solution]

I have another question for you Do you have code for how to sort the 'count' or formula #4 in descending order and keep the appropriate 'item' string attached?

[Former Member · Accepted solution]

I figured it out I feel good!!!!

I combined formulas 3 and 4 and added a bubble sort:

My Formula:

shared numbervar array cnt;

shared stringvar array items;

local numbervar i ;

local numbervar j;

local numbervar tmp;

local stringvar tmpstr;

Booleanvar swapped := true;

while swapped = true do

(

     swapped := false;

     for j:=2 to ubound(cnt) do

          (

           if( cnt[j-1] < cnt[j]) then

               (

                    tmp := cnt[j-1];

                    tmpstr := items[j-1];

                    cnt[j-1] := cnt[j];

                    items[j-1] := items[j];

                    cnt[j] := tmp;

                    items[j] := tmpstr;

                    swapped := true;

                 );

               );

);

for i := 1 to ubound(cnt) do

(

     local stringvar s := s & items[i] & ': ' & totext(cnt[i], '#') & (if i <> ubound(cnt) then chr(13));

);

s;

[Former Member · Accepted solution]

Ok I have another question for you. I have placed a total at the bottom of each group so going back to my original example it looks like this:

                                   count

Dr John

     wrong client               1

     service code              3

     duplicate entry           1

     Total                          5

Dr. Michele

     wrong client               1

     service code              2

     location                      1

     duplicate entry           1

     Total                           5

Is there any way to sort the group's so the person with the highest total is first? I realize these are both the same, but if Dr. Michele had a total = 10 I would like her to be first.

[abhilash_kumar · Accepted solution]

I would recommend posting that as a new discussion, per SCN's rules.

-Abhilash

[Former Member · Accepted solution]

Ok I will do that, I didn't realize that was a rule.