convert string to number 4

[peterRed]

The below formula works where the first 2 number fields are multiplied by the 3rd string field
{cv_accomodation_segment.DURATION} * {cv_accomodation_segment.NUMBER_OF_UNITS} * $ToNumber({cv_accomodation_segment.HOTEL_NIGHTLY_RATE})

When trying to use the same 3rd field as a number field on its own
ToNumber({cv_accomodation_segment.HOTEL_NIGHTLY_RATE})
so that a summary may be calculated i receive an error??? Appreciate any comments

 

[dgillz]

please explain your comment "so that a summary can be calculated".

Also, please post the error message you are getting. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[peterRed]

Thank you for replying
i am trying to total the string field, hence the formula
ToNumber({cv_accomodation_segment.HOTEL_NIGHTLY_RATE})that worked.
However when repeating the same but with a different string field
ToNumber ({ACCOMMODATION_SEGMENT.HOTEL_GUARANTEE})and after placing the above field on the report i receive the error message "the field is non numeric". yet using the first string field it worked..
thanks for you help

 

[dgillz]

The means the second field is not convertable to a number. Please look at the {ACCOMODATION_SEGMENT.HOTEL_GUARANTEE} field using the field explorer, and make sure the icon in the upper right is depressed. This will show the data type and length.

Alternatively, just place this field in the details section of the report and see what the data is.

There could be null field values here as well. Under file, report options, make sure the "convert null field values to default" option is checked. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[peterRed]

thankyou, it worked.
There was the additional problem of one record having text in the field which was trying to be converted to number, I think it kept causing the error

 

[Ngolem]

*******************************************************************
The below formula works where the first 2 number fields are multiplied by the 3rd string field
{cv_accomodation_segment.DURATION} * {cv_accomodation_segment.NUMBER_OF_UNITS} * $ToNumber({cv_accomodation_segment.HOTEL_NIGHTLY_RATE})

*******************************************************************

the formula really did not work either it is just that you did not notice the times the formula failed since Crystal would have just skipped over those values when there were nulls or the string was not numeric by causing the whole formula to fail.

Setting the convert nulls to default value as suggested will handle nulls as long as the default is the correct number (for example do you want nulls converted to zero's or the value of 1 ... also do you want these instances to be flagged so you can review them later???....automatic conversion of nulls will not do this for you)...also what happens if you have 2 variables that could be null...perhaps that should not have the same default value.

My approach to handling this problem would be to allow the nulls to be not converted so I could identify this bad data (or rather not numeric data) but test for the null and if it is non-numeric...something like this

@Calculation

WhilePrintingrecords;
booleanVar dataflag := false; // to identify bad data
NumberVar result := {cv_accomodation_segment.DURATION} * {cv_accomodation_segment.NUMBER_OF_UNITS} ;

if not isnull({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) and
isnumeric ({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) then
result := result * {cv_accomodation_segment.HOTEL_NIGHTLY_RATE}
else
(
result := result * (whatever default value you want);
dataFlag := true; //somewhere in the report you would use
// this (eg. you could color a row of data)
);
result;

*****************************************
This should give you the flexibility to do anything you want.
hope this helps




Jim Broadbent

 

[peterRed]

Thank you Jim, it works perfectly. What lead to all the above was using the 3rd field which is a string field as a number field on its own
ToNumber({cv_accomodation_segment.HOTEL_NIGHTLY_RATE})
so that a totals summary may be calculated i receive an error. i think the error was caused if text instead of numbers were typed into the field ie US$100. Are you able to help on this? thank you.
Peter

 

[synapsevampire]

If you're looking for a means to extract a value from a string, try:


Numbervar X;
stringvar TheField := "$US100";
// replace the $US100 above with your field
Stringvar TempString := "";

for x := 1 to len(TheField) do(
if isnumeric(mid(TheField,X,1)) then
TempString := TempString+mid(TheField,X,1)
);
val(TempString)

You may have to account for decimals by adding in a:

or
mid(TheField,X,1) = "." then

If you turn on the convert null field values to default as dgillz suggested, then you can abandon the isnull() check entirely, and just use an if isnumeric() check with an else clause to do the formula as described above to make sure there aren't numbers embedded.

The downside of the formula is that it checks for any numbers, so:

1DS300 will net:

1300

Hopefully that isn't a problem for you, and I would suggest generating a report of all rows which fail isnumeric() so that a data entry person can make corrections.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[peterRed]

thankyou,
i added the following
Numbervar X;
stringvar TheField := "{cv_accomodation_segment.HOTEL_NIGHTLY_RATE}";
// replace the $US100 above with your field
Stringvar TempString := "";
for x := 1 to len({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) do(
if isnumeric(mid({cv_accomodation_segment.HOTEL_NIGHTLY_RATE},X,1)) then
TempString := TempString+mid({cv_accomodation_segment.HOTEL_NIGHTLY_RATE},X,1)
);
val(TempString)

It is resulting with ignoring the existing 2 decimal places that are (sometimes) included by combining and adding 2 decimal places to the number ie my number US$18.00 results with 1800.00. any ideas?? thanks

 

[synapsevampire]

The field that you put in should NOT be in quotes, that's a literal string now, not a field. Fortunately you replaced it's usefulness in the formula anyway, here's a revised version:

Numbervar X;
stringvar TheField := {cv_accomodation_segment.HOTEL_NIGHTLY_RATE};
Stringvar TempString := "0";
for x := 1 to len(TheField) do(
if isnumeric(mid(TheField,X,1))
or mid(TheField,X,1) = "." then
TempString := TempString+mid(TheField,X,1)
);
If isnumeric(TempString) then
val(TempString)
else
0

Notice that I now check for TempString being numeric because it's conceivable that someone might have placed periods (just one or more decimal places) without any numeric values in this field, which renders the field unusable, methinks.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[peterRed]

Thank you k very much appreciated it worked.
peter

 

[peterRed]

I have grouped my report by Department, Cost Centre and Name and included the above numeric fields. Is there a way i can sum the value of a formula for a group. the formula field is sum of @AccomValue
where AccomValue is
whilePrintingrecords;
booleanVar dataflag :=false;// to identify bad data
NumberVar result:={cv_accomodation_segment.DURATION} * {cv_accomodation_segment.NUMBER_OF_UNITS};
if not isnull({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) and
isnumeric({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) then result:= result
* ToNumber ({cv_accomodation_segment.HOTEL_NIGHTLY_RATE})
else (result:=result)* (0);
dataFlag:=true;
result;

 

[Naith]

You can sum this formula by accumulating the values of the result variable in another variable.

i.e.

whilePrintingrecords;
booleanVar dataflag :=false;// to identify bad data
NumberVar result:={cv_accomodation_segment.DURATION} * {cv_accomodation_segment.NUMBER_OF_UNITS};
NumberVar ResultTotal;
if not isnull({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) and
isnumeric({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) then result:= result 
* ToNumber ({cv_accomodation_segment.HOTEL_NIGHTLY_RATE})
else (result:=result)* (0);
dataFlag:=true;
NumberVar ResultTotal := ResultTotal + Result;
result;

Where you want the total to be displayed, insert the formula:

WhilePrintingRecords;
NumberVar ResultTotal;

If this total is to be displayed at a group or page level, then you must remember to reset the ResultTotal variable for each new group or page. Do this by inserting the following formula in the header of the group or page:

WhilePrintingRecords;
NumberVar ResultTotal := 0;

Naith

 

[peterRed]

thank you Naith, all worked. The first sum is in the Name Group. are you able to sub total by Department and Grand total?
Also when i try to sum the number field {@AccomValue} + {@IncidentalToNumber}; i am unable total it. are you able to help? thanks

 

[Naith]

Because you're using functions like WhilePrintingRecords in your formulas, you are unable to total any of these. You have to use variables as I showed you before to accumulate the totals and then display them when appropriate.

This is what you have to do for {@AccomValue} + {@IncidentalToNumber}. Just mimic the variable style I showed you for each group, like you've done for the Name group.

i.e. Place the display and reset formulas in the relevant footers. Include a couple of extra variables in your formula for the other groups, so your formula now looks something like:

whilePrintingrecords;
booleanVar dataflag :=false;// to identify bad data
NumberVar result:={cv_accomodation_segment.DURATION} * {cv_accomodation_segment.NUMBER_OF_UNITS};
NumberVar ResultTotal;
NumberVar DeptTotal;
NumberVar GrandTotal;
if not isnull({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) and
isnumeric({cv_accomodation_segment.HOTEL_NIGHTLY_RATE}) then result:= result 
* ToNumber ({cv_accomodation_segment.HOTEL_NIGHTLY_RATE})
else (result:=result)* (0);
dataFlag:=true;
ResultTotal := ResultTotal + Result;
DeptTotal := DeptTotal + Result;
GrandTotal := GrandTotal + Result;
result;

Although in this example, the DeptTotal and GrandTotal are accumulated in the main formula, you could also assign these accumulations in the reset formulae of the smaller group. (e.g. Assign GrandTotal in DeptTotalReset).

Naith

 

[peterRed]

Thankyou. When adding the above new formula the DeptTotal miscalculates. The total amount is over stated by the same amount (number) as appears in the last detail line of the Name field row(s) in each Department Group. If the last amount in the details was $360, then the DeptTotal was over by $360. The same is happening with the GrandTotal except the amount is added twice i.e the Grand Total is over by $720. any thought this? thankyou
peter

 

[Naith]

Under the assumption that Name is your first group, and Department is your second group, can you ensure that you have the following formulae placed in the following sections?

//@ResetResultTotal -- Placed in the Name Group header
WhilePrintingRecords;
NumberVar ResultTotal := 0;

//@ResetDepartment -- Placed in the Department Group header.
WhilePrintingRecords;
NumberVar DeptTotal := 0;

//@MainFormula
//**The formula mentioned in my previous post in the details section.

//@DeptTotal -- Placed in the Department Group footer
WhilePrintingRecords;
NumberVar DeptTotal;

//@ResultTotal -- Placed in the Name Group footer
WhilePrintingRecords;
NumberVar ResultTotal;

//@GrandTotal -- Placed in the Report footer
WhilePrintingRecords;
NumberVar GrandTotal;

Naith

 

[peterRed]

Thanks for your patience, all is working.
Regards
Peter