Using data from an array to calculate?

[Dbarca64]

Hello,

I am new to the forums. I was hoping someone could help me with this Crystal report issue.

I have 3 data fields in a MySQL database that are stored as comma delimited array. These three fields represent the product grade, the quantity and the dollar value of the input side of a work order.
(grade array) = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
(volume array) = 391, 1548, 0, 2548, 241, 0, 0, 0, 0, 0
(cost array) = 1300.000, 1290.000, 1290.000, 650.000, 360.000, 0.000, 0.000, 1200.000, 0.000, 0.000

What I need to do is calculate Position1 391*1300 - Position2 1548*1290 - through Position10

I also need to do an if then else with the first set of data where
if G-ARRAY = 1 then "FAS"
ELSE IF G-ARRAY = 2 THEN "1F"
ELSE IF G-ARRAY = 3 THEN "SEL"
OR
If G-ARRAY = grade.table.ID then grade.table.gradename

I also need to use the results of these calculated fields to calculate the difference between the input of the work order and the output of the work order.

The output I am looking for would be like this:
GRADE VOLUME PRICE VALUE
FAS 391 $1300 $508.30
1F 1548 1290 $1996.92


Thanks in advance!

 

[Madawc]

The SPLIT command would break up the field into individual items. Also ULINE will tell you how many parts, if this varies.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[CoSpringsGuy]

Your request is confusing ...
will the Grade array always be 1 through 10?




_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Dbarca64]

Sorry if I confused you maybe this will help?

Yes, Grade, Volume and Cost will always have 10 positions.
Grade is the only constant where Grade can pull the value from another database table.

// Get Grade Name from grade_table
if grade ={tblproduct.GradeID} then {tblgrade.Name} else "FooBar"

// Display Values of Grade, Volume and Cost
If Volume >0 then
Grade + " " + Volume + " " + Cost

//Calculate total cost
If Volume >0 then Volume * Cost

 

[CoSpringsGuy]

Try this .. You will of course need to replace {@GArray}, {@VArray} and {@CArray} with your grade array, cost array and volume array...
NOTE: I was unclear what you meant by "I also need to use the results of these calculated fields to calculate the difference between the input of the work order and the output of the work order." So if you want to clarify that I will see if I can incorporate that as well



create a formula called grade

numbervar x;
stringvar GradeOutput := "GRADE";
stringvar VolumeOutput := "VOLUME";
stringvar CostOutput := "PRICE";
stringvar ValueOutput := "PRICE";
numbervar Array GArray; redim GArray[10];
numbervar Array VArray; redim VArray[10];
numbervar Array CArray; redim CArray[10];
stringvar array Grade := ["SAF","1F","SEL","?","?","?","?","?","?","?"];
numbervar array value; redim value[10];
For x := 1 To 10 Do
(
    GArray[x] := tonumber(split({@GArray})[x]);
    VArray[x] := tonumber(split({@VArray})[x]);
    CArray[x] := tonumber(split({@CArray},",")[x]);
    value[x] := (VArray[x] * CArray[x])/1000;
    GradeOutput := GradeOutput & chr(13) & grade[x];
    VolumeOutput := VolumeOutput & chr(13) & totext(VArray[x],"#");
    CostOutput := CostOutput & chr(13) & "$" & totext(CArray[x]);
    ValueOutput := ValueOutput & chr(13) & "$" & totext(value[x]);
);
GradeOutput & chr(13) & "Totals"

create a formula volume

stringvar VolumeOutput;
numbervar Array VArray;
VolumeOutput := VolumeOutput & chr(13) & sum(Varray)

create a formula called cost

stringvar CostOutput;
numbervar Array CArray;
CostOutput := CostOutput & chr(13) & sum(CArray)

create a formula called value

stringvar ValueOutput;
numbervar Array Value;
ValueOutput := ValueOutput & chr(13) & sum(Value)

Place these for formulas in order in the detail section side by side.
Right click each and select format field, then the common tab and check the box next to can grow
you may also want to left justify Grade, middle justify volume and right justify both cost and value




_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Dbarca64]

CoSpringsGuy Thanks for the input!

I dont think I need to "hardcode" the grades there is a table where the number value of the array is equal to the indexID and name of grade can be displayed through in if statement.
I commented that part out. It does run without any errors but I am only getting GRADE and Totals in the grade field, and one entry for 0.00 in both Value and Volume fields.

I guess I need to further explain what the expected output looks like and what the input looks like.
I have these field in this database table "purchlot" grade, volume and cost. When I insert these fields into the report the display like the example below.
(grade array) = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
(volume array) = 391, 1548, 0, 2548, 241, 0, 0, 0, 0, 0
(cost array) = 1300.000, 1290.000, 1290.000, 650.000, 360.000, 0.000, 0.000, 1200.000, 0.000, 0.000

The output I need is going to be this below
Grade Volume Cost Extended Price
1 391 1300.00 $508.30
2 1548 1290.00 $1996.92
4 2548 650.00 $1656.20
5 241 360.00 $86.76
8 0 1200.00 $ 0.00 (if found price)
________________________________________________
Totals 4728 $4248.18

Math with these numbers?
We process what the vendor "sold us" our volumes do not match their bill of lading at the end of the transaction we are going to pay our vendor based on what our volume findings.
"our" findings are
Grade Volume Cost Extended Price
1 1013 1300.00 $1360.90
2 1428 1290.00 $1842.12
4 450 650.00 $580.50
5 1520 650.00 $988.00
6 38 360.00 $13.68
8 219 1200.00 $262.80
____________________________________________
Totals 4668 5004.0

I need the answers to volume difference and dollar difference.
"our" process numbers grade and volume must be multiplied by the purchase prices for the grade value.
Division of the dollar variance in excess of 4% to our disadvantage will cause a "claim" for a credit.

 

[CoSpringsGuy]

Well I am thoroughly confused but let me address the small part I think I do understand first... (You didnt answer if the grade array (which is not really an array as far as crystal is concerned by the way) is always 1,2,3,4,5,6,7,8,9,10)
The reason the Grade column showed only "Grade" and "Total" is because of the lines you commented out. We need to create your If then statement to create a new array with the appropriate grades and then the code i sent you will work as far as what I thought you were trying to do.
Reading through the rest of your post, I think I am missing the mark on what you are trying to do. Probably an industry misunderstanding. So I guess I will just ask some questions.

In your expected output, why is there only 8 items? Is that because there is no cost associated with 9 and 10?

Math .. we can do whatever math with the numbers you want but, unfortunately for you, that is going to require me understanding exactly what you are trying to do. Not sure what questions to ask to gain that understanding though...

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

another confusion I had by the way was with the value you recieved when multiplying volume by cost. I had to divide the vlaue by 1,000 to get the number you had in your example. Does that mean that, for example in the first example, 1300 / 1000 or $1.30?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

I modified the GRADE formula (below) to only output those entries which have a volume of greater than 0 and I created a select expression to properly code the Grade. I also fixed the column hear for EXTENDED PRICE to match your latest post. Now the output (once you fill in appropriate entries in the select statement) should match the output in your example. Based on the information available that you have shown me, how would I know what the bill of laden transactions are to check against the "our" value? Am I missing something?

numbervar x;
stringvar GradeOutput := "GRADE";
stringvar VolumeOutput := "VOLUME";
stringvar CostOutput := "COST";
stringvar ValueOutput := "EXTENDED PRICE";
stringvar Array GArray;
numbervar Array VArray; redim VArray[10];
numbervar Array CArray; redim CArray[10];
numbervar array value; redim value[10];
stringvar array Grade; redim Grade[10];

For x := 1 To 10 Do
(
if  tonumber(split({@VArray})[x]) > 0 then
    (
    redim preserve GArray[x];
    Select trim(split({@GArray},",")[x])
        Case "1" : grade[x] := "SAF"
        Case "2" : grade[x] := "1F"
        Case "3" : grade[x] := "SEL"
        Case "4" : grade[x] := "4?"
        Case "5" : grade[x] := "5?"
        Case "6" : grade[x] := "6?"
        Case "7" : grade[x] := "7?"
        Case "8" : grade[x] := "8?"
        Case "9" : grade[x] := "9?"
        Case "10" : grade[x] := "10?"
        Default :  grade[x] := "Unknown";
    VArray[x] := tonumber(split({@VArray})[x]);
    CArray[x] := tonumber(split({@CArray},",")[x]);
    value[x] := (VArray[x] * CArray[x])/1000;
    GradeOutput := GradeOutput & chr(13) & grade[x];
    VolumeOutput := VolumeOutput & chr(13) & totext(VArray[x],"#");
    CostOutput := CostOutput & chr(13) & "$" & totext(CArray[x]);
    ValueOutput := ValueOutput & chr(13) & "$" & totext(value[x]);
    );
);
GradeOutput & chr(13) & "Totals"

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

that was odd... either I completely missed you post about the grade translation or it posted out of order

I dont think you will be able to use an if then like you mentioned. Well before I say that tell me how the tables purchlot, tblproduct and tblgrade are linked...

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection