Crystal Report Arrays

[BarbaraBartlow]

I have a PeachTree (Btrieve) database in which the user has recorded two salesman's initials if the commission on a sale is to be split. For example there may be salesmen BB and BH. If the commission is to be split, she has set up a thrid salesman called 'BB/BH' and attributed the sale to 'him'. I am now trying to develop a report showing the commission for each true sales person. What I had thought would work would be to set up arrays - one containing the salesman initials, one containing his accumulated sales, and one containing his accumulated commissions and then print the arrays as the end of the report. (below you will see additional arrays; don't be distracted, there are just certain sales that need to be broken out for a different commission rate - that's not the problem.) The problem I'm having is just finding the proper way to set up the subscript I guess. I want the logic to say, if the salesman is already in the array (have encountered previously) then add his sales/commissions to the corresponding element in the other array(s). If the salesman is not in the array, then increase the subscript by one (Redim preserve the array) and put him and the sales and commissions in the appropriate arrays. Code below only shows this portion of code, because I have not successfully gotten my first salesman in an array yet. I repeatedly get and error on the first Redim Preserve statement salying "an arrays dimension must be an integer between 1 and 1000". What am I doing wrong that "I" in the code below is not an integer to Crystal. What is the proper way of doing this.

A further question is, once I get past the issue of getting salesmen into the array, if I issue a statement like "If salesman IN arrayname" and I get a true result, how can I identify the subscript for where that salesman is in the array?

Thanks for any help offered.
Here's the code:

EvaluateAfter({@Slash});
Shared NumberVar Array SalesArray;
Shared NumberVar Array CommArray;
Shared NumberVar Array SGSalesArray;
Shared NumberVar Array SGCommArray;
Shared StringVar Array SalesmanArray;
Local NumberVar I;
Shared NumberVar Sales1;
Shared NumberVar Comm1;
Local NumberVar SGSales1;
Local NumberVar SGComm1;
Shared NumberVar Sales2;
Local NumberVar Comm2;
Local NumberVar SGSales2;
Local NumberVar SGComm2;
Shared StringVar Salesman1;
Shared StringVar Salesman2;
Local NumberVar SlashPosition;
Local NumberVar FoundPosition;
Local BooleanVar Found;
SlashPosition := INT({@Slash} - 1);
Found := False;
Salesman1 := " ";
Salesman2 := " ";
IF NOT (Salesman1 IN SalesmanArray)
THEN I := Int(UBOUND(SalesmanArray) + 1);
ReDim Preserve SalesmanArray;
ReDim Preserve SalesArray;
ReDim Preserve CommArray;
ReDim Preserve SGSalesArray;
ReDim Preserve SGCommArray;
SalesmanArray := Salesman1;
SalesArray := SalesArray + Sales1;
CommArray := CommArray + Comm1;
SGSalesArray := SGSalesArray + SGSales1;
SGCommArray := SgCommArray + SGComm1;

 

[synapsevampire]

Post what's in @SLASH...

In an attempt to distill your post a bit, it appears that you're trying to split amounts across different entities, there are none in the above so you're looking for an incomplete solution and this will likely net additional questions, not a solution:

Also what percentage will be applied to dual (or more???) entities for the amounts involved, is it always an even distribution?

I would build out an array of ALL Salesmen, and then I would use the equivalent array subscript to sum the amounts into the other arrays, which appears to be your thinking here as well.

So everything makes sense except I, which is the ley, and the thing you decided NOT to share the source of (@SLASH).

To simplify this, you can place the differing salesman into their own array using:

stringvar array LatestSalesPeople := split({table.field},"/")

Now this is already subscripted for you, no reason to keep pointers for the slash, etc., and you can use a loop to extract them.

whileprintingrecords;
stringvar array LatestSalesPeople := split({table.field},"/");
stringvar array SalesPeople;
stringvar array Otherstuff;
numbervar x:= ubound(LatestSalesPeople);
numbervar counter;
for Counter := 1 to x do(
if not(LatestSalesPeople)[counter] in SalesPeople then
redim preserve Salespeople[ubound(SalesPeople)+1];
SalesPeople[ubound(Salespeople)] =: LatestSalesPeople[counter];
//Then you would set all other array values as well as in:
redim preserve Otherstuff[ubound(SalesPeople)+1];
OtherStuff[ubound(OtherStuff)] =: {table.value}/ubound(OtherStuff);
//The aove line divides the value by the number of
// sales people
<...etc...>
);
"blah"

The blah is there because you cannot have an array as the output of a formula.

Hopefully this theory will get you over the edge, you seem very bright and a competent coder, the trick is to flesh out requirements more fully in the future.

-k

 

[IdoMillet]

Consider a completely different approach, if you are allowed to create a new table in that database.

The table will look like this:
------------------------------------
Ref_Code Sales_Rep_Code Fraction
BB BB 1.00
BH BH 1.00
BB/BH BB 0.50
BB/BH BH 0.50
------------------------------------

Now, all you need is to join the Sales_Rep code to the Ref_Code and multiply the commission and sales amounts by the "Fraction". Note that in the case of BB/BH each sale would result in 2 records in the result set, one for BB and one for BH...

A simple CrossTab would then finish the job easily without any need for array gymnastics.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[BarbaraBartlow]

Synapsevampire,

I know the code is incomplete. I will know how to finish it when I get this part to work without error. @Slash is just a shared variable I created previously and tells me where the '/' is in the salesman name. I know that works fine to pull out the first salesman; and the second in a pair. I've printed both successfully. Your right, I probably should have included it in this section of code, but that doesn't really solve my problem. That is not the issue. The problem is why do I get an error on the redim preserve statement?
I don't see where my code is fundamentally different from mine. What am I missing? The error is telling me I is not an integer. Why original code was identical you yours and it told me was not an integer. I'm using Crystal Reports version 9. Does your code actually work in that?

IF NOT (Salesman1 IN SalesmanArray)
THEN I := Int(UBOUND(SalesmanArray) + 1);
ReDim Preserve SalesmanArray;

for Counter := 1 to x do(
if not(LatestSalesPeople)[counter] in SalesPeople then
redim preserve Salespeople[ubound(SalesPeople)+1];
SalesPeople[ubound(Salespeople)] =: LatestSalesPeople[counter];

And I still have my second question, what if I do find the salesman already in the array? How do I now the subscript to accumulate this sale into the rest in the corresponding array.

With respect to IDMillet's suggestion. No I can't create another table and that wouldn't help if I could. There is no problem knowing the commission percentages for the salesman. The only problem is as I read through and print invoices, how to accumulate ALL sales for each salesman when some are split and each salesman only gets credit for half the sales.

Barbara

 

[IdoMillet]

Look at my suggestion again. It solves the accumulation problem WITHOUT using arrays and complex code. It would also execute faster.

If you can't add a table to your DBMS, you can probably link (note: not import) to your DBMS from MS Access, add the helper table there, and run the report against MS Access.
Another option is to use a UNION view that creates the records of the helper table on the fly.

Another option it to add to the existing report a helper table from MS Access. This means your report would be using two data sources. Since Crystal allows two data sources (as long as the join across them is a single-field join, this would work. It would be slower though.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

I think you should reconsider a suggestion like Ido's. If you have an employee table that contains all the employee names and/or initials, you could do a left join from the {employee.initials} to {sales.refcode} and then use formulas like:

if {employee.initials} = {sales.refcode} then {table.commission} else
if instr({sales.refcode},"/") > 0 and
{employee.initials} in split({sales.refcode},"/") then
{table.commission}/ubound(split({sales.refcode},"/"))

Then you could just insert a crosstab, use {employee.initials} as the row and the above formula as the summary.

Or you could insert separate running totals per salesperson which sum the above formula and in the evaluation section, you would use a formula:

{employee.initials} = "BB"

-LB

 

[synapsevampire]

If you do find them in the array then you would use an additional loop on the arry to determine where in the array it is. But you need to get the basics working and understand this array theory being passed first.

The point of having a table is moot if you're not allowed to build one, and I appreciated the post based on advanced array handling.

The code I had supplied is different from what you are attempting to use, if you intend to leverage mine, please post again using your table.field name and any specific errors and observations.

So use:

whileprintingrecords;
stringvar array LatestSalesPeople := split({table.field},"/");
stringvar array SalesPeople;
stringvar array Otherstuff;
numbervar x:= ubound(LatestSalesPeople);
numbervar counter;
numbervar ArrayLoc;
nubervar CurrLoc;
for Counter := 1 to x do(
if not(LatestSalesPeople)[counter] in SalesPeople) then
redim preserve Salespeople[ubound(SalesPeople)+1];
SalesPeople[ubound(Salespeople)] =: LatestSalesPeople[counter];
if LatestSalesPeople)[counter] in SalesPeople then
for ArrayLoc := 1 to ubound(Salespeople) do(
if LatestSalesPeople)[counter] = SalesPeople[ArrayLoc] then
CurrLoc:=ArrayLoc
);
//Then you would set all other array values as well as in:
if not(LatestSalesPeople)[counter] in SalesPeople then
redim preserve Otherstuff[ubound(SalesPeople)+1];
OtherStuff[ubound(OtherStuff)] =: {table.value}/ubound(OtherStuff);
//The aove line divides the value by the number of
// sales people
<...etc...>
);
"blah"

Then you can display in the report footer:

whileprintingrecords;
stringvar array SalesPeople;
stringvar array Otherstuff;
SalesPeople[1] & Otherstuff[1]

Or use a loop, or...

This is mainly theory, I suppose I should work out how to generate 3 dimensional arrays in a generic sense in Crystal and create a FAQ if I get the time.

-k

 

[BarbaraBartlow]

Hi again synapsevampire,

I have attempted to duplicate your code exactly, using my array names. Now I get an error saying I need an ELSE statement. Why what is wrong with my syntax? I don't fully understand when to use ";" and when not to.

Toward the end of your logic wither I don't understand or I don't agree that you should divide table.vale by ubound(otherstuff). It is divided by the number of people who split that one individual sales, not how many you have saved in the array already. I think the proper value is ubound(latestSalesPeople).

Meantime what is wrong with my syntax? Why should I need and else?
Barbara

 

[BarbaraBartlow]

oops I forgot to copy in my current code:

WhilePrintingRecords;
Shared StringVar Array LatestSalesPeople := split({Employee.EmployeeID},"/");
Shared NumberVar Array SalesArray;
Shared NumberVar Array CommArray;
Shared NumberVar Array SGSalesArray;
Shared NumberVar Array SGCommArray;
Shared StringVar Array SalesmanArray;
Local NumberVar X := UBound(LatestSalesPeople);
Local NumberVar Counter;
Local NumberVar ArrayLoc;
Local NumberVar CurrLoc;
Local NumberVar SplitCount := UBound(LatestSalesPeople);

For Counter := 1 TO X DO(
IF NOT(LatestSalesPeople[Counter] in SalesmanArray)
Then
ReDim Preserve SalesmanArray[UBound(SalesmanArray)+1];
ReDim Preserve SalesArray[UBound(SalesmanArray)+1];
ReDim Preserve CommArray[UBound(SalesmanArray)+1];
ReDim Preserve SGSalesArray[UBound(SalesmanArray)+1];
ReDim Preserve SGCommArray[UBound(SalesmanArray)+1];
SalesmanArray[UBound(SalesmanArray)] := LatestSalesPeople[Counter];
IF LatestSalesPeople[Counter] IN SalesmanArray
Then
For ArrayLoc := 1 TO UBound(SalesmanArray) DO(
If LatestSalesPeople[Counter] = SalesmanArray[ArrayLoc]
Then CurrLoc := ArrayLoc
);
SalesArray[CurrLoc] := SalesArray [CurrLoc] + {@InvAmount}/SplitCount;
CommArray[CurrLoc] := CommArray[CurrLoc] + {@InvComm}/SplitCount;
SGSalesArray[CurrLoc] := SGSalesArray[CurrLoc] + {@SGInvoiceAmt}/SplitCount;
SGCommArray[CurrLoc] := SgCommArray[CurrLoc] + {@SGCommission}/SplitCount;
);
"blah"

 

[synapsevampire]

Sorry, going from memory in that code, try:

WhilePrintingRecords;
Shared StringVar Array LatestSalesPeople := split({Employee.EmployeeID},"/");
Shared NumberVar Array SalesArray;
Shared NumberVar Array CommArray;
Shared NumberVar Array SGSalesArray;
Shared NumberVar Array SGCommArray;
Shared StringVar Array SalesmanArray;
Local NumberVar X := UBound(LatestSalesPeople);
Local NumberVar Counter;
Local NumberVar ArrayLoc;
Local NumberVar CurrLoc;
Local NumberVar SplitCount := UBound(LatestSalesPeople);

For Counter := 1 TO X DO(
IF NOT(LatestSalesPeople[Counter] in SalesmanArray)
Then
(
ReDim Preserve SalesmanArray[UBound(SalesmanArray)+1];
ReDim Preserve SalesArray[UBound(SalesmanArray)+1];
ReDim Preserve CommArray[UBound(SalesmanArray)+1];
ReDim Preserve SGSalesArray[UBound(SalesmanArray)+1];
ReDim Preserve SGCommArray[UBound(SalesmanArray)+1];
SalesmanArray[UBound(SalesmanArray)] := LatestSalesPeople[Counter]
);
IF LatestSalesPeople[Counter] IN SalesmanArray
Then
(
For ArrayLoc := 1 TO UBound(SalesmanArray) DO(
If LatestSalesPeople[Counter] = SalesmanArray[ArrayLoc]
Then CurrLoc := ArrayLoc
);
SalesArray[CurrLoc] := SalesArray [CurrLoc] + {@InvAmount}/SplitCount;
CommArray[CurrLoc] := CommArray[CurrLoc] + {@InvComm}/SplitCount;
SGSalesArray[CurrLoc] := SGSalesArray[CurrLoc] + {@SGInvoiceAmt}/SplitCount;
SGCommArray[CurrLoc] := SgCommArray[CurrLoc] + {@SGCommission}/SplitCount;
);
);
"blah"

 

[BarbaraBartlow]

Hi again synapsevampire,

Well I got past the syntax errors now. But there is still womething wrong with our logic. By end of report UBound(SalesmanArray) is only 2 and the first array element is blank; the second one contains my last salesman. There are about 10 salesman listed in the detail report (not counting split codes).

I don't see the logic problem, but I'm going through and changing the last value to trace what I'm doing (ie changing "Blah" to various variables one at a time). Is there any quicker way to debug in Crystal?

Barbara

 

[synapsevampire]

Where are you placing the formula?

Each iteration of this formula will handle the current employee so it should be in the details section or a group footer/header of the salesman depending upon the underlying data..

There may be logic bombs in here as I just fleshed out the theory, if so I would be displaying ubound(SalesmanArray) in the details to watch what is happening as it goes along.

-k

 

[BarbaraBartlow]

Yeah Thanks.
I've been working away and displaying various variables and have figured out that I've got it right now except that it adds in the last invoice for each salesman twice - so my totals in the arays are one invoice high for each salesman. How many times does a for loop execute? For example if it evaluates to For I := 1 to 1 does it execute once or twice??

My variable that does all these calculations is on the detail line. I'm thinking of moving it to the salesman total line and using different variables (summaries of table.field rather than table.field) to solve my problem above.

You have been a great help. I still think I don't get Crystal logic as to when I am to use a ";" at the end of a line and when not to.

Barbara

 

[synapsevampire]

FOR I := 1 to 1 would execute once.

Sounds like you're close, if it's executing twice for the same salesman, perhaps you have 2 rows for a salesman.

-k