round to the nearest 20 1

[neskin]

Hi there,
I have field varchar
The data is 20, 30, 40-50
I have a formula if it is 20 or 30 it's fine but with 40-50
did not rounded

// To calculate the quantity of stickers to be printed on the packing slip.
// Stickers are printed in multiples of 20. Checks value in No of Attendees
// field on Name Events screen - if blank or less than 20, quantity is 20.
// Otherwise, the value is rounded up or down to the nearest 20.
// (e.g. 30 is rounded down to 20 and 31 is rounded up to 40).


// Declare variables
Local numberVar NumGuests;
Local numberVar NumStickers := 0;
Local numberVar NumRemainder := 0;

// Initialise NumGuests
IF (NOT(ISNUMERIC({Name_Events.STATUS_EVENT})) OR
ToNumber({Name_Events.STATUS_EVENT}) <= 20) THEN
NumGuests := 20
ELSE
NumGuests := ToNumber({Name_Events.STATUS_EVENT});

//IF NumGuests <= 20 THEN
//NumGuests := 20;

IF NumGuests > 20 THEN
NumRemainder := Remainder(NumGuests, 20);

IF NumRemainder <= 10 THEN
NumGuests := NumGuests - NumRemainder
ELSE
NumGuests := NumGuests - NumRemainder + 20;

NumStickers := NumGuests;

thank you

 

[MisterMo]

What would you want the field '40-50' to be, at the moment a field with such value is converted to 20

IF (NOT(ISNUMERIC({Name_Events.STATUS_EVENT})) OR 
ToNumber({Name_Events.STATUS_EVENT}) <= 20) THEN
NumGuests := 20
ELSE
NumGuests := ToNumber({Name_Events.STATUS_EVENT});

because is not numeric.

If however these can be converted to numeric values eg. 40 , 41, or 50, 51 and so on

then on your last calculation you would have

NumGuests := NumGuests - NumRemainder + 20;

which will result in

if 40 = 40
if 41 = 39
if 42 = 38
if 43 = 37
if 50 = 30

Can you see the pattern.








-Mo

 

[Naith]

Your formula looks sound to me, regardless of the default field value.

What I would suggest is adding 'WhilePrintingRecords' at the beginning ofthe formula for processing control. Now check the impact on your results.

 

[MisterMo]

Sorry I miss-read the last IF statement.


-Mo

 

[lbass]

If you are saying that the field result could be "40-50" then try something like the following:

stringvar statusevent := {Name_Events.STATUS_EVENT};
Local numberVar NumGuests;
Local numberVar NumStickers := 0;
Local numberVar NumRemainder := 0;

if NOT(ISNUMERIC(statusevent)) then
NumGuests := (val(left(statusevent,instr(statusevent,"-")-1))+
val(mid(statusevent,instr(statusevent,"-")+1)))/2 else
IF ToNumber(statusevent) <= 20 THEN
NumGuests := 20
ELSE
NumGuests := ToNumber(statusevent);

IF NumGuests > 20 THEN
NumRemainder := Remainder(NumGuests, 20);

IF NumRemainder <= 10 THEN
NumGuests := NumGuests - NumRemainder
ELSE
NumGuests := NumGuests - NumRemainder + 20;

NumStickers := NumGuests;

-LB

 

[neskin]

hi LB,
When I copy your formula i am getting the error when I run the report

String length is less then 0 or not an integer
Bassicly what I need if eg.40-50 get the first number 40 and calculatethe quantity . it is fine if the only one number
eg. 20 or 36 or 90
Status event field
20
36
40-50
100
and so on
Thank you
nat

 

[lbass]

I forgot to allow for a blank field, I think. Try:

stringvar statusevent := {Name_Events.STATUS_EVENT};
Local numberVar NumGuests;
Local numberVar NumStickers := 0;
Local numberVar NumRemainder := 0;

if isnull({Name_Events.STATUS_EVENT}) or
trim({Name_Events.STATUS_EVENT}) = "" then
NumGuests := 20 else

if NOT(ISNUMERIC(statusevent)) then
NumGuests := (val(left(statusevent,instr(statusevent,"-")-1))+
val(mid(statusevent,instr(statusevent,"-")+1)))/2 else
IF ToNumber(statusevent) <= 20 THEN
NumGuests := 20
ELSE
NumGuests := ToNumber(statusevent);

IF NumGuests > 20 THEN
NumRemainder := Remainder(NumGuests, 20);

IF NumRemainder <= 10 THEN
NumGuests := NumGuests - NumRemainder
ELSE
NumGuests := NumGuests - NumRemainder + 20;

NumStickers := NumGuests;

Also, I used the average of 40-50 to arrive at 40. Wouldn't that be a better approximation? If you want it only to read the 40 in this example, then change that particular clause to:

if NOT(ISNUMERIC(statusevent)) then
NumGuests := (val(left(statusevent,instr(statusevent,"-")-1)) else
IF ToNumber(statusevent) <= 20 THEN
NumGuests := 20
ELSE
NumGuests := ToNumber(statusevent);

Maybe you should test to see what other variations there might be in the field by adding a record selection criterion:

not(isnumeric(statusevent))

Then you could report back if there are other variations that are interfering with the execution of the formula.

-LB

 

[neskin]

LB
Thank you very much
I have checked the blank field and I am using (val(left(statusevent,instr(statusevent,"-")))* -1 for some reason
the number is negative.
It is work perfect for my case
Thanks again
Nat