Grouping based on parts of a List Field

[Rob7]

Hi All,
I have run into a situation where I need to group by pieces of a list field. This is a sales commission report and in the invoice record, there are two list fields, one for the sales rep code and one for the actual commission dollar amount. I found a formula to split the codes out into five individual fields and the same with the corresponding commission amounts.

My problem now is how to group by a particular code because the code could occur in any of the five "split out" fields. And I need to keep the corresponding commission amount with the sale rep code for totaling by rep code.

I have created a field that concatenates the rep code and the commission amount but trying to group on that was not any help.

The report looks like this right now:

Inv_No Inv_Dt Inv_Amt RepCode_1 Commission_Code 1
RepCode_2 Commission_Code_2
' '
RepCode_5 Commission_Code_5

Any help would be greatly appreciated.
Rob

 

[synapsevampire]

Try posting technical information instead:

Crystal version
Database/connectivity
Example data
Expected output

We don't know what formula you used to break out the fields, we don't know is in those fields, and the bit about grouping by one of the 5 values is a bit vague.

Try posting what you currently have and what you need, it generally works much betetr than trying to describe it.

-k

 

[lbass]

You need to show samples of the original fields and also the content of the formulas that were used to split the fields.

-LB

 

[Rob7]

Crystal version is XI, the data base is Progress. The data record line has the invoice number, data and amount. Pretty standard stuff. Then there are these two list fields. The rep code filed would have for example CW~STT~LT~~ if there were three sales guys included on the commission of the sale. The commission amount field is set up the same 100~300.5~50~~. So this is all actually on the same record line. The formula I used to split out was Split({InvcHead.SalesRepList}, '~')[1] (this is in formula field) which in the example from earlier would show "CW". I do the same formula for the commission and get "100". The following are two records from the data table:

Record 1
Inv_No 10021
Inv_DT 10/06/2003
Inv_Amt 51,660
RepCode STT~CW~~~
Comm_Amt 2789.26~206.26~~~

Record 2
Inv_No 10017
Inv_Dt 09/20/2003
Inv_Amt 41,427.50
RepCode CW~STT~99~~
Comm_Amt 912.5~1095~300~~

 

[Rob7]

Sent the prior before I was really ready to.

So with this set up, I want to group by the RepCode and show the invoice numbers the rep worked on and commission amounts.

 

[synapsevampire]

Much nicer description, unfortunately that's not how databases or groupings work. Both the coder and the dba involved with this need to change occupations, far away from IT.

You might be able to emulate a groping by using arrays. I suspect this might be a bit lofty for your level though.

Or you might put the data into a normailzed form by parsing it out.

Array overview:

whileprintingrecords;
stringvar array currentnames:=Split({InvcHead.SalesRepList}, '~');
stringvar array currentamounts := Split({InvcHead.SalesAmount}, '~');
stringvar array Names;
numbervar array Amounts;
numbervar Counter;
numbervar counter2;
For counter := 1 to ubound(currentnames) do(
if not(currentnames[counter] in Names then
(
redim Names preserve(ubound(names)+1);
redim Amounts preserve(ubound(Amounts)+1);
Names[ubound(Names)]:= currentnames[counter];
Amounts[ubound(Names)]:= currentamounts[counter];
);
else
if currentnames[counter] in Names then
For counter2 := 1 to ubound(Names) do(
if currentnames[counter] = Names[counter2] then
Amount[counter2]:=Amount[counter2]+currentamounts[counter]
);
);

I haven't tested, but the theory is to create an array of names and an array of summed values with the same subscript.

Hopefully this theory helps, I'm afraid I have to leave but play around with it.

-k