Character Trimming String issue ( more a question )....

lbellipanni

I am extracting a string which requires the padded 0s in front of the number for a database look-up.  However when exporting to the AX repository, the pad 0s need to be stripped.  My initial thoughts were to Create a DataField1int data field and set the calculate expression = DataField1string and setting its visible status to false.   Since the DataField1Int needs to be visible for its value to get calculated, this does not seem to be the best way to do this.   Any suggestions on the best way to handle this?

EX
00456734   ( Need this value to do the data base look-up )
456734  (Need to export this value to cmis repository )

Thanks

rmccutcheon

@GrooperGuru do you have any ideas?

GrooperGuru

As is, you would either have to write a simple script to change the zero padding before export or accept that the second field will not be hidden. However, I'm immediately curious why there are two different systems in play where one use strings and one uses integers. It seems that it would be a best practice to correct the "string" side to be a standard numeric value and to create consistency across systems. Is that possible in this situation?

lbellipanni

GrooperGuru said:

As is, you would either have to write a simple script to change the zero padding before export or accept that the second field will not be hidden. However, I'm immediately curious why there are two different systems in play where one use strings and one uses integers. It seems that it would be a best practice to correct the "string" side to be a standard numeric value and to create consistency across systems. Is that possible in this situation?

I can check and see.  My take is the lookup database is kept by one department, then repository is maintained by another.  There is also a third offsite repository maintained by a state agency that may be the culprit in stripping the pad zeroes for their repository.  I will see about the possibility of establishing a business rule that the numbers always start with 00 and may be able to utilize that.  I hear them mention that when talking of voucher numbers.

hjanum

You could handle it in the SQL query.  Store the value normalized (without zeros) in Grooper. Say the Grooper field fieldvalue1 is needs to be eight digits with left padded zeros. The SQL would look something like this:

SELECT * FROM table where column1=RIGHT('00000000' + fieldvalue1, 8)

The SQL compiler should be smart enough to calculate the value of the right function once and execute the query. Even easier if you use a stored proc.