ECM - AP Invoices in multiple currencies - how do I control the display format dynamically?

ECM
1

Ran into an interesting quandary today while preparing to rollout IDC/ECM to our European offices. We process AP Invoices in GBP, Euro & USD in the same batch/group of invoices. This isn’t a problem when entering invoices directly into Epicor, but IDC and ECM both have little/no functionality that I can find to dynamically control the display format.

Specifically, how do I get the decimal display format to swap the decimal points and commas (the European format) for GBP and Euros - and to do it dynamically based on a meta field? I’m thinking we add a currency field, or datalink set the field based on the Customer record - and then have the display format change automatically.

So far, the only thing we can find is under User Settings/Environment and that is an all-or-nothing option.

2

@MikeGross I think this would be similar to what @Banderson was dealing with and having the vendor lookup in IDC give you something to map to another Batch Type using specific version.

I know that locale handles dates, I am assuming it will also do the number formatting.

image
image1272×88 17.1 KB

image

2 Likes
3

There are some options in the DFD for locale settings, but mostly with dates I believe. I don’t see anything for numbers.

You might have to make a regex formula with an if or switch based on the vendor name to translate the number into US format.

For dates, I made a drop down for which format they used, then mapped the vendor to to that field so after they set it once, the formula looks for that option and automatically recalculates the date in the correct format. You could do the same thing with the numbers.

4 Likes
4

I have done this in ECM by making the Date and Number fields formatted as text. Instead of mapping the values in IDC and having it learn that, I have the customer create a UD field on the vendor profile which can be looked up from ECM for each document. Depending on what the UDF states, I send the workflow to specific handling to change the date from its source format to US format. Same can be done with the number values.

There’s no right way to do things so there’s certainly some “artistic” license for how to develop things and not saying this is a better approach than what you defined. At the end of the day, it just comes down to what works.

5 Likes
5

Sounds like we need to create an idea! That’s a lot of work!

1 Like
6

Write it up Utah, I think the currency handling along with formats should be controlled by the user and only by changing the format on the ECM environment we could see the data in different locals. Multi-currency is clumsy at best in ECM when it does work.

2 Likes
7

That pretty well sums up invoicing in general. It’s frustrating Amazing, how many different ways companies can make up to present an invoice.

2 Likes
9

Scott, I’m not trying to shy away from putting in the work or effort, I don’t deal with multicurrency at the moment and wouldn’t be the best person to try and capture that in an idea… maybe @MikeGross or @Banderson could…

10

@vleveris - I think there is an answer somewhere between what we both said, thanks for that nudge.

@Banderson @gpayne @utaylor @swilliasc111 thanks for the input/support

Looking at this a bit more, my original dilemma might have been lacking some detail.

IDC
You can specify that a field is either decimal or money. Money causes IDC to search for, and insert, a currency symbol IF it finds something to work with. That makes the field a string/varchar type - but

  • IDC doesn’t understand the decimal/comma swap that Europe uses, and
  • what happens when it goes to ECM? The symbol gets stripped somewhere along the line.

I’ve not fully tested the Batch Language/Locale settings to see what happens, but I suspect it’ll swap the comma/period, and may default the currency symbol.

ECM
In ECM, the field is either text/string or decimal, with a fixed Display Format that includes a Currency option (versus decimal or fixed decimal), but the documentation does nothing to indicate the differences other than Currency will display the symbol - but where does it get this and why doesn’t it support the comma/period swap where appropriate? Near as I can tell, a Decimal Currency field will display the symbol associated with the User Pref/Environment setting, which uses the default symbol for the country selected. If you set this for Netherlands, that field will show a Euro symbol and it WILL swap the comma/decimal point - regardless of the actual invoice currency.

Conclusions so far:
I was attempting to determine I could dynamically alter the currency field’s symbol based on either a calculable field or a data link or something. Our centralized European accounting department will work in multiple currencies, and I was looking for some control over the field display based on invoice currency.

For now, I’m thinking of stripping out all the currency references and allowing Epicor’s AP Invoice logic to set the default supplier currency when the AP invoice is created. IDC and ECM will simply see decimal values.

At most, I could create new Batch designations for each currency and the user would have to select correctly, but with IDC automatically taking these right out of an email inbox, the batch identity is currently fixed. But then I would have to create (ECM?) “calculated” fields to address the Currency code and the field’s display format simply to present it to the user correctly.

Anyway, that’s where we are so far. We have an open ticket with EpicCare just on the off chance someone over there knows something different.

3 Likes
11

One thing for sure that I have found an issue with is changing the language in ECM to accommodate for this currency handling. When I did that, ECM interpreted all of my workflow inputs to be the opposite of what I wanted (ie. commas as decimals and vice-versa).

We deal with a number of Canadian customers so the date handling is always something we run across. Again, no one way to resolve things so whatever you find works, then you’re ahead of the game.

I’ve only dealt with one customer for Europe and I used the Text format for dates and handled that field in ECM. As for the dollar amounts, I had the locale set on the Document Type and that seemed to be enough for me. I stopped development on the Europe portion, though, so I don’t remember if I had enough developed to fully test the outputs.

1 Like
12

Agreed - we’ve found the same thing. luckily our folks in the EU are ok with the Date fields being ‘American’, but the numbers are another thing. And because they are dealing in multiple currencies at the same time, it’s more of a thing.

@vleveris A Question - If I leave all the settings effectively blank/default in ECM, will ECM use the Windows ‘locale’ settings and reformat the number fields? We’ve not tried it, but it just came to my mind reading your response.

3 Likes
13

Hmm… that’s a very good question. I would imagine that if it’s a cloud instance then it wouldn’t have any impact, but if it were on premises then it may read the local settings and adjust accordingly. This is completely theoretical and certainly worth a test, though.

3 Likes
14

Try changing the locale or create a document type with a different locale in IDC to get the invoice to format the invoice amount fields as the correct currency.

I find if IDC correctly formats the data, the data in ECM should be able to get currency from PO information or if non-po use a currency field to capture the currency.

If currency changes based on a key field like address you could create a selection field with currency selections that automatically map based on the address or maybe do a calculation based on the country.

2 Likes
15

@swilliasc111 I see where you are going, and I agree with you.

The prerequisite for this to work is that each batch can only contain one currency and have the correct doc type selected. We are reading emails form an inbox automatically. IDC has no function to dynamically change doc types after OCR processing.

IDC passes XML to ECM, and it doesn’t contain currency info unless it’s a field on the DocType. I could add that field to the doctype and make it mandatory, but that defeats the automation expected from the IDC->ECM process when IDC cannot determine currency (otherwise assuming the doc has been trained in IDC).

The problem is that the data in the ECM field will be a decimal data type. You can assign a currency format in the Field Definition but where does it know the currency?. Another field could contain the Currency Code/identifier (however it’s obtained or derived or received from IDC), but I need to know if it has a special name or something that connects it to the Field’s currency format to be correct for that document as I move between documents in my queue.

@vleveris @swilliasc111 New Question - how does a Field definition’s currency format know what the currency value is? this may be the root of the problem and I couldn’t get there until we talked it out.

16

You can have more than one document type in a batch type.

I was experimenting with the classification engine to change the doc type based on what was coming in. I didn’t complete it, as I realized I could do what I needed with formulas (and sort of with vendor specific DFDs, long story), but the point is, you might be able to classify the documents by vendor into different document types. At the very least, change the type manually after import, since I know you have someone doing the monitoring of IDC.

I was getting a lot better results of reading the problem invoices when I separated them to a totally different doc type. I just ran into a couple of problems that I reverted back to a single main DFD. But it might be worth a shot.

3 Likes
17

One possible solution is to use a field to calculate the currency of invoice based on the format of the InvoiceAmt. My initial thought would be to use a substring from the right and find the value 3 characters in. If it’s a decimal, it’s North American (USD, CAD, etc.). If it’s a comma, it’s EU.

Are you referring to the fields within IDC? If so, I couldn’t really say as we don’t have much back-end access to evaluate how things work, specifically. All I can really say is that IDC has developed it to identify, understand and calculate properly when an associated locale is specified.

2 Likes
18

I like this dialog we got going - and we’ve come up with a number of things to try. I’m going to test some version of all of these ideas and I’ll come back and post what happened.

@Banderson - totally forgot about that aspect - probably my tunnel vision of this topic - thanks for reminding me about that classification and multi-doctypes might be a way to a solution. With this new implementation in the EU, I’d like to not have a user in IDC except to complete training occasionally. I was thinking about the combination of a Currency Code field in IDC that the training would remember and that would get sent to ECM - like you do with dates. Nice to know that all works.

In thinking about all of the paths I could go, I truly believe the root problem is the ECM UI, as that is where users will process and view (outside of a workflow) documents in the future.

In the DB and XML exchanged between IDC and ECM, its only numbers and text; and something has to interpret a currency code into some sort of action. I don’t really want to expend the effort of converting fields to text only to insert a symbol and swap the commas/decimals if I don’t have to.

Which is why I keep coming back to the question
How does the ‘currency’ format flag on a decimal field know what the currency is?
If we can answer this, then there is no manipulation needed other than to set a value in the metadata somehow.

4 Likes
19

My thought would be that when you declare a field in ECM as a decimal and then use the Currency format, that it likely converts it based on the language of the system. I’m basing this off of the fact that when I last switched the language of the system it messed up all of my workflows because it couldn’t interpret the commas and decimals in the previous North American language format. I would have had to switch all of my Advanced Math tasks, and likely some others, to accommodate for the EU language change. From a workflow development standpoint, this makes sense to me as I wouldn’t expect someone in the EU to have to develop their workflows in North American formatting and vice-versa.

20

ok, I see your point. I wish we could find an international consolidated accounting function to chime in.

On the assumption that I cannot control the display format easily, but rather only with the OS settings, then I think my answer is to establish a Currency Code field which could be in IDC (learned) or in the ECM workflow (manual entry) and is passed to Epicor in the Create Invoice task. And tell the users that their local OS setting will determine the decimal/comma placement regardless of currency or invoice origination.

I’ve got some testing to do. :slight_smile:
Thanks everyone!

1 Like
21

One thing we often do in the case where we can’t determine something by vendor from the invoice is to have the customer create, populate and maintain a UD field in Epicor for whatever purpose it’s needed for. This could be to identify which vendors have their invoices in DD/MM/YYYY format so we can take the text values from IDC and switch them around. Same could be done with the currency format, although since this is already a field that exists on the vendor profile you may be able to leverage what exists to do the same with the commas and decimals.