Formula problem

I have a formula to populate an Integer field as follows

 

IF(

 OR(

       ISNULLOREMPTY(SFDCFULL_UserLookup.HR_Personnel_ID__c),

       ISNULLORWHITESPACE(SFDCFULL_UserLookup.HR_Personnel_ID__c)

      ),

 0,

 TOINTEGER(SFDCFULL_UserLookup.HR_Personnel_ID__c)

)

 

This gives me an error when the SFDCFULL_UserLookup.HR_Personnel_ID__c field is empty. See below

 

Exception Type : FormatException Message : Input string was not in a correct format. StackTrace : at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.String.System.IConvertible.ToInt32(IFormatProvider provider) at Scribe.Process.ProcessCreateXMLforSAPBMO.b__88() at Scribe.Process.ProcessCreateXMLforSAPBMO.Execute(Dictionary`2 settings, IEnumerable`1 dataEntityInput) - Error in mapping 'field BMO_SFDC_SAP.Header.SalesDirector ' for Operation 'CreateWith'

 

So it looks like the TOINTEGER is not receiving a string.

 

However, if I replace the formula with

IF(

OR(

      ISNULLOREMPTY(SFDCFULL_UserLookup.HR_Personnel_ID__c),

      ISNULLORWHITESPACE(SFDCFULL_UserLookup.HR_Personnel_ID__c)

     ),

0,

1

)

 

The field is populated with 0 showing that the test for emptiness is working, so the first formula should not try to convert anything.

 

Can anyone explain what is happening and/or suggest how I might achieve my aim?

 

Thanks.

(7) Answers

Login