Jump to content

Unable to format the data - REPLACE

Alexander Jansen

Recommended Posts


A few months ago we had an external developer build a NAV-CRM integration solution in Scribe for our business. Today we encountered an error with a single record in our NAV database - unfortunately, the error doesn't state exactly which record failed. I'm pretty confident however it's a human data-entry mistake and would probably be an easy fix, so I would like to get this figured out without having the developer get involved.

The Scribe error log:

Operation failed. Label: Lookup cat_contractline, Name: cat_contractlineLookup, Message: Dynamics 365 Connector Unable to format the data '' for cat_contractline.cat_subcontractnonew.: Error in calling Operation Query (Lookup Block)


When navigating to this block we have the following in place:

cat_subcontractnonew equals REPLACE(REPLACE(NAVProd_SubcontractZZFetch.Sub_Contract_No,"Z",""),"V","")


cat_subcontractnonew is a decimal field and NAVProd_SubcontractZZFetch.Sub_Contract_No is a string field with values containing a number and in some cases also a letter (e.g. ZZ5, 3.2V). The point of the formula above is to strip away any Z's and V's from this.

I'd like to figure out what kind of data could possibly cause Scribe to be "unable to format the data" and throw this error, as in my mind it doesn't make sense this operation should ever fail.

Link to comment
Share on other sites

  • 2 weeks later...

Hi Alexander,

In this case NAV is throwing the error itself.

My feeling is with replacing the Z's and V's some additonal 'whitespace' is being generated.

So my suggestion would be to use the Strip function to remove the Z's and V's from your string



STRIP(string_expression, "identifier")


Returns only the requested type of characters from a string: alpha, numeric, punctuation, whitespace, other/unknown.






The string to be evaluated.




The type of character to return. Following are the valid values for this argument:

A Alpha

N Numeric

P Punctuation

W Whitespace

O Other or unknown






This function is useful for reformatting data such as phone numbers, zip or postal codes and social security numbers. Use in conjunction with the FORMAT function.


Use the "O" identifier to remove all characters except those that have an ASCII value of less than Hex 20(decimal 32) or greater than Hex 7F (decimal 127). The characters between decimal 32 and decimal 127, which are not stripped out by the "O" identifier, include all of the characters found on a standard QWERTYkeyboard.

Whitespace refers to space, tabs, or certain Unicode characters identified as blanks..


STRIP("(603) 555-1234","N")

Returns: "6035551234"

STRIP("abc123.456def", "AP")

Returns: "abc.def"

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...