IDC - System having trouble with DD/MM/YYYY date formats

ECM
1

Hi all,

Super new to this IDC stuff, trying to get a demo set up for a customer. They are capturing data from invoices for AP purposes.

We are in Canada, and most of their vendors give their dates in MM/DD/YYYY format. No problem here.
I have Date Conversion set to MM/DD/YYYY (as some vendors use dashes or dots in their dates)

However, some of their vendors give dates in DD/MM/YYYY format, and herein lies the problem. If the DD is greater than 12 (e.g. 31/12/2022), the Date field will be in red and present as an error. If the DD is less than or equal to 12 (e.g. 10/12/2022), it will “work”, but it will still be wrong (it will show as twelfth of October, rather than tenth of December)

An Epicor person told me to have the locale settings set to both Canada and US (primary and secondary locale), but it still doesn’t work. I’ve also set the secondary locale to GB, no luck.

Any thoughts on how to get past this?

2

I went around it by making two DFDs and a different drop folder for our EU customers, but I am an end user so I don’t have to demo it.

3

Unfortunately, at least from my experience, this isn’t something that IDC can handle without some sort of consistency being provided to the application. As @gpayne suggested, this can be done by having different directory folders setup so that they use a specific Document Type which is then set to the locale which would use the date format you’re looking for.

Another could be mapping the vendor name, or another unique identifier of the vendor, to a date format field and then using that date format field as a reference for a formula to properly format the date into MM/DD/YYYY from any other format. This solution could work, but you may also end up with dozens of mappings so if that isn’t an issue, then this could work for you.

Another solution we’ve used is having a user defined (UD) field in Epicor on the vendor profile and then breaking apart the date field from IDC and handling the reformatting in ECM. This would require you to have ECM as well as a UD field to handle the formatting, though.

4

So, I set up a format option for the user to select. Then I use that option to reformat as necessary. I also cheated and sent the date to SQL to parse it for me so. SQL will take lot of different formats and parse it back to something consistent. You need some sort of SQL connection set up (we’re cloud, but they can set up a lookup DB for you). You aren’t actually looking anything up, but you can still use SQL to do some work.

This is the drop down I made. There is a “Selected date” which takes whatever is selected on the page. Then the drop down is used to determine the format. I have it set up as assignable so that it should remember by vendor which it should be. Then there is a calculated field that uses those 2 fields to give me a consistent output.

image
image854Ă—516 21 KB

Here’s the code for the calculated field.

if ( EQ(STRICMP(MyDateFormat, "Month/Day/Year"),0),
 	PROC(
    @InvoiceDate = ARRGET(SQLEXEC(STRJOIN("Select FORMAT(TRY_CAST(' ",SelectedDate,"' AS DATE),'MM/dd/yyyy')")), 0)
    ),
	PROC(   
    @DateArray =  REGEXSEARCH(SelectedDate, "(\d{1,2})\/(\d{1,2})\/(\d{2,4})"),
    @InvoiceDate = STRJOIN(ARRGET(@DateArray, 2),"/", ARRGET(@DateArray, 1),"/", ARRGET(@DateArray, 3))
    )
)

Hopefully that makes sense.

1 Like
5

This looks like a good solution - I’ll give it a try. You’d think the IDC would be able to handle something as simple as date formats, heh

Thanks,

6

7

I’d use a map field between the vendor name and the date format type. Then the user doesn’t have to select it unless it’s the first time.

1 Like