Unable to format the data - REPLACE

[Alexander Jansen]

Hi,

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.

[Nick Roelandt]

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

Syntax

STRIP(string_expression, "identifier")

Description

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

Arguments

 

 

 

string_expression

The string to be evaluated.

 

 

identifier

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

 

 

 

 

Returns

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.

Remarks

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..

Examples

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

Returns: "6035551234"

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

Returns: "abc.def"