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.
