Service Connect - Check null Values for Dates/Numbers

Just answered my own question.

If anyone in the future needs to know how to express a null value for a date based off of a condition the 
"Value Expression" you need is:

0001-01-01T00:00:00

Good morning all,


I am having an issue with an SC solution I built.  I am taking an excel file, and using the values there to push to a UDTable.  Since Excel is being used to push the data, there will be times where the values will be null.  With strings this isn't a problem, but with dates and numbers it is.  I know if I can just see one example of doing this, i'll be fine, but alas, that's impossible to find on the net.  I wish I could code it.


If I were coding this for the number value, I'd say:


IF ExcelFileField = null THEN 0 ----> UDTable.Number01

ELSE

ExcelFileField --> UDTable.Number01


For a Date Value I'd say:


IF ExcelFileField = null THEN 99/99/9999  ----> UDTable.Date01

ELSE

ExcelFileField ---> UDTable.Date01


I've been using different functoids in my conversion but so far I haven't been able to get anything to work.  I've been able to check the value using the conditional functoid, but it throws a TRUE/FALSE value to UDTable.Number01 resulting in an error.  I need it to throw a 0 instead.  I appreciate your time and help everyone.

I would think the conditional functoid would work, but depends on how you've set it up.  On Null=True you'd output a predetermined string of 99/99/9999 (Although I don't know if E9 would accept that date), if Null=False then it would pass your excel field.  

Alternatively, you might have to bring the decision out of the transformation and create a choice split instead.  I had to do that once when a conditional scenario just got too complex.  
Thanks for the reply,

Yes, i think the conditional functoid will have to be used, but for some reason i can only get it to pass a TRUE/FALSE value rather than the numerical value or a 0 based on the ExcelFileValue.  I wish I could put screenshots in here.
I got it figured out!

Found out I was erroneously linking my FromValue to each Alternative, which means the Functoid was passing my FromValue if it was null or not.  I didn't know the XPath Builder in the Condition Functoid is used to literally build the condition.

So if anyone is trying to do the same as described above, the Condition Functoid is your solution.  Use the XPath Builder for the Condition Expression.

Cheers ya'll!
... On a side note, I love figuring this stuff out!!!  Learning stuff everyday is why I'm in this business.
Hello JockTheMotie,

I guess I didn't have it all figured out.  You're 100% correct E9 will not accept the 99/99/9999 value of null = true.  

What is the Value Expression I need in order to pass a null date value to Epicor and have epicor understand it.

I've tried:

?
Just leaving the Value Expression blank
Putting a "" in the Value Expression
9999-99-99T00:00:00
0000-00-00T00:00:00

The only thing I can get to work is: 1901-01-01T00:00:00  which could work, but it's not ideal.  I just want the date value to be null.  I can't figure out why ? doesn't work.  It works for everything else Epicor.  

Any help would be appreciated.