Format Numbers in a String

[LauraW]

We are using Crystal to pull information out of our ERP system. I have a field that is used during data entry and is entered as
.000001

However, when I pull the field into Crystal it is showing as
0000000000000001+

The field type is string
Length is 60

I am trying to format the data entry field (with no luck) as it was entered .000001

I have tried RIGHT, LEFT, MID, TRIM, ToNumber (I get that an error that the string is Non-numeric), NumericText and nothing I do works. The field will not allow me to format it either.

Any suggestions?

Laura~

 

[pmax9999]

Hi Laura.

I'm guessing you have tried it, but if not try the VAL() function. It ignores non numeric characters, and in my test returned 1.0.

VAL({Table.Field})

If that doesn't work it may be something specific to the ERP system. Is this the 1st time you have identified the behaviour or does it happen everytime when returning this field? What happens if you delete the entry and re-enter it (through the app)?

Which ERP are you using?

Hope this helps.

Cheers
Pete

 

[LauraW]

Thanks Pete! That returned a whole number of 1.00 even though the field was entered as .000001 (which is what is required) We are using the Lawson/Infor ERP system and we use Crystal Reports as our reporting software. We try to validate the data before releasing into our live system and the report is our double check to make sure that the information is entered correctly.

This field is used when employees work in more than one position. Unfortunately it is a required field and if it's entered as anything but .000001 they get double their vacation time accruals!

Laura~

 

[pmax9999]

So, does this issue arise only for a single record, or every record where theat particular field is used. If it is only one record, I suggest you looka at exactly what was entered, ie does it include non alpha-numeric characters such as Carriage Returns of Line Feeds. If the latter, deleting that entry via the application and re-entering it would be worth a try.

If it occurs for every record, it must be something to do with the way the record is stored in the database and I'd sugget you contact the software vendor for assistance.

Hope this helps.

Cheers,
Pete

 

[LauraW]

Hi Pete,

Unfortunately it happens with every record where we use that particular field. I may have to back into it this way.

IF NOT(isnumeric({PERSACTION.NEW_VALUE_11})) = TRUE THEN ".000001" ELSE "CHECK"

(or)

IF {PERSACTION.NEW_VALUE_11} = "1.00" THEN "ERROR" ELSE "OK"

I will have to test both of these to see if either one works. They both appear to with the one record that is currently entered but I will need to commit more records to fully test it.

Thanks for your help! Happy Friday!

Laura~