I am working on getting the system to recognize all the 32,188 different ways the world decides to write dates…
I already made a formula to be able to flip DD/MM/YYYY and MM/DD/YYYY, then am mapping the format that each vendor uses to the convert to our standard date format. That part is working great.
So, the next part of this journey is to look at the next of the 34,985 different ways to format a date and get that to convert.
But is there a way to do this for other date formats without having to explicitly handle each possible variation?
This feels like it’s a common enough issue that there should be an easier way.
side note: when I tried to just use the string of the “selected date” as a result instead of using MakeDate(), which theoretically would work for most of the invoices, the validation was failing saying that they didn’t match. Which I’m assuming has to do with the type on the field. But I can’t let the “selected date” be set as a date, but the format fails if it doesn’t match… I couldn’t find a way to just convert the text to a date without pulling it apart using regex.
Hmm - I’ll ask my folks but we’ve been running/training IDC for a couple of years no and haven’t had any issue with date field formats. As far as I know IDC has been figuring it out automatically and putting the yyyy-mm-dd in the output. But I’ll take a look and let you know.
Epicor had me setup another DFD, Batch and Document type for EU dates and every thing else has been handled by them without (to my knowledge) any date issues.
Instead of setting up a different DFD, (because I don’t have a good way to separate the stuff coming in, I want any and all invoices coming in the same stream, our volume is too high to have people who “Just know”. We keep getting burned on that, and we want to get away from that).
If we had all US format, month/day/year, I’m pretty sure IDC would handle the word months fine. Where we get tripped is the international, so I have to determine that based on… something.
So want I ended up doing was adding a date format drop down. Then this value is mapped to the vendor name, because the vendor will always (or at least usually) have the same date format. So if they run across a vendor that does it day/month/year, the user just selects the date format for that one, then that is mapped to the vendor, and the next time that vendor shows up, it’s automatically switched.
For the normal format, or if someone uses words, I execute SQL because sql will take a bunch of formats, and then I can take the response from SQL and shove that into a string with the correct format going forward.
I think this should work nicely. I wish it wasn’t so hacky, but I had a call with and IDC guy today, and he said that this was pretty clever, and that they didn’t have any built in functions to do what I’m doing, so I’ll use this I guess.
In my asking, it seems that my folks just end up typing in the date manually… but our volume is not where yours is, so I guess it is what it is for us. At least until I decide to recreate what you did
What I’m worried about is the parts of the month where the order is ambiguous and someone doesn’t notice when it’s wrong. “Almost automatic” is the worst kind, because it lulls you into a false sense of security.
Exactly - totally agree - automation makes people “less attentive” about everything that is automated. And of course, that allows errors. I do like your solution in tying a date format to a Vendor, and shrinking the error rate substantially.
I’m wondering in you could also use the Vendor’s country code to do the format choice rather than having the user select the choice. That would of course require the vendors in the UK to use UK formats exclusively…
Oh for sure you could. I’m bringing in the Epicor Vendor Num and Vendor ID, And also doing a lookup to validate the PO number and setting the company based on the lookup. So if you have a field that you can use to set it, you’d be golden. I wasn’t sure I had that, and so this method of “set it the first time you see it, then automatic after that” I thought was a nice way to do it. Cause someone has to set something initially.
I tried this out today, and it looks like it only works if the Captured date and the output date are both set to text? I’d love to be able to use the Date datatype, but IDC doesn’t like that - I get an “index out of range” error on the array function - it’s like the array won’t form with a Date type, but with Text it will?
Secondly, Ancora documentation says that arrays are zero-indexed, but in your code you’re starting at 1? And it works - I guess their documentation is just wrong?
From what I can tell, the Date flag (format) in the DFD is formatted to the region/locale of the Batch Type or the Document Type, depending on which level it is set at. Setting it to text no longer assumes its a date and instead just treats it like any other string variable. I typically set it up for text and then do any date handling in ECM as there are more options available there.
As for the array index beginning at 1 instead of 0, this may be due to how it was declared in the variables that were set. @Banderson would be able to provide the actual answers as this is his solution, though.
It won’t take in something that isn’t in the format it expects and output the format it’s set to. So to be able to take in word months, it has to be text.
I guess I didn’t notice it, I just adjusted the index until it worked. It’s 0 in some parts of the code, but looks like the it wants 1 for this one. I have no idea why.
Does it export them in the date locale chosen? Or a translate them to a standard? I would be awesome if it translated the different date types to standard for export so that ECM could read them appropriately.
Translates to the standard format. The locale indicates how the date is formatted and when converted to the IDC format will just be a date.
I tested this on French months and it converted to a regular US date format.
It’s a misc parameter called DateLocale and DateFormat.
When changing the format, you need to know the Date Locale of the batch type and the date locale of the vendor. The date format will be dependant on the locale but I did not have to change the locale if the date format is DD/MM/YYYY to MM/DD/YYYY.
I’ll probably have to play with that then. Sounds promising.
Do you have to explicitly set up each vendor then for the vendor specific date? Right now, I have a mapping for the date format so the user can change it, then it remembers it. I would to to find all of the vendors then and make vendor specific DFD’s for each one that has a date format different then US? That kind of sucks, especially a new vendor shows up, we have to do setup to be able to process the invoice.
I’m using the Specific Versions functionality, it allows you to indicate what the vendorname would be when the date locale/date format would need changing. Since it is misc parameter I’m not sure at the moment if it would work with a mapping field in the way you have it setup, basically similar functionality.
