Method Community

I'm trying to make a Work Order automatically calculate foreign currency amounts directly in item grid. Item Cost comes in home currency even though selected customer is in foreign currency. I believe I need to configure some kind of action within grid to have the automatic calculation using actual QB exchange rate.

Hi Marco,

Under Customize > Tables / Fields in Method you can add a dropdown for Currency to the ActivityJobItems table, selecting the Currency table for the list of values. You can then add the ExchangeRate field as a Linked Field, linking it through the Currency dropdown. This will make these fields available for the Line Item grid of your Add / Edit Work Order screen, but you'll first have to add them to the grid using the screen designer. You can then use Basic Math Calculation and Enter Value Into Field On Screen actions to calculate and populate values as appropriate (you'll likely want to add one more field to your ActivityJobItems table/grid to house your Foreign Item Cost value).

May I ask why you would want to have Item Cost in a foreign currency? I understand your customer may pay you in a foreign currency, but Item Cost would be what the Item costs you to purchase, not what they're paying you for your services. Depending on what you're trying to do here, there might be a simpler way to go about it.

Let me know.

- Justin

Justin, thank you very much. You're right, it's prices that need to be changed. I need forms (Estimates, Sales Orders, Invoices, etc.) to behave as in QB ,where item prices, sales tax and total amount depend on the customer:job's currency. Home currency is different than USD.

Hi Marco,

Assigning an A/R account with the appropriate home currency of the customer will set the currency for the Invoice, despite the amount still appearing as "USD" on the New / Edit Invoice screen. This currency symbol is set system-wide on the Customize > Display screen in Method.

Where is it that you're displaying the amount to the user? Is it your Print Templates that you wanted to modify, so that the PDF your customer views is in a different currency?

Let me know.

- Justin

I first need to make the template work. Our Item list prices are in HNL (home currency) and at this moment al our forms in Method (Estimates, Sales Orders, Invoices) show item lines in grids in HNL, even though our customers are in USD. I need all money amounts within templates to convert automatically to USD using the exchange rate in QB.

Hi Marco,

The Print Templates (PDF reports) you send to your customers are coded to show the currency symbol as specified by the user's browser/system, so what shows HNL for you should show $ for your US customers. You could hard-code your Print Templates to show USD using our Report Designer, if you'd prefer that.

As far as the conversion goes, this should be done automatically assuming your accounts are set up with the appropriate currencies. As long as you have the Expense Account(s) for your Items set up as HNL accounts, the Cost will be in HNL. If you then select the A/R account for an invoice as a USD Accounts Receivable account, all Sales Amounts entered will be treated as USD, including the Total Amount. QuickBooks will then convert this into your Home Currency and display it to you on screen in QB.

I'd like to stress that the "HNL" you see on your Customers > Invoices screen in Method is simply a system-wide display setting. As long as the Account set for the invoice is a USD Accounts Receivable account, all amounts will be in USD. You can change the currency display setting to blank from the Customize > Display screen to avoid the confusion if you'd like, and you could also customize Method to show you the same conversion to HNL for the Total Amount on your Invoices screen (as QB does).

Let me know if that helps.

- Justin

Hello Justin. I haven't been able to make the grid calculate and make my line item prices and total amount convert into dollars. For some reason Exchange Rate linked field is not retrieving the appropriate rate, it shows up as 1 where it should be 21.69. I also looked at the table in MS Excel via ODBC and ER shows up as 0. I think maybe this is happenning because ER field is an integer field or maybe due to regional OS related issues.

Also, I synched a transaction and yes, Customer uses its currency and AR account and if Customer has a different currency setup than AR account it won't synch delivering error, but if grid is not currency calculated information in transaction won't be correct.

I'm having a great time with Method and I've learned a lot but I'm stuck on this one.

Thanks,

Hi Marco,

I've taken a closer look and there seems to be an issue with the Currency table's ExchangeRate field. I've created a ticket for our development team to look into it and will post back here with any updates.

- Justin

Hello Justin. How long do you think this may take? Thanks.

Marco,

Unfortunately, I do not have an ETA.

I want to make sure though that this is what you're trying to do:

1. Store your items' sales prices in the Item table in HNL
2. Convert those sales prices to USD on the Invoice screen using the ExchangeRate set in QB

Can you confirm, or advise otherwise?

- Justin

1. Item table comes in HNL which is fine. Our item list is in HNL which is our home currency.

2. I want forms (Estimates, Sales Orders, Invoices) to behave exactly as QB templates do when selecting a Customer in USD currency where all money amounts convert automatically using the exchange rate. We have customers in USD and in HNL. I don't want to see HNL prices on transaction grids when using USD Customers, just as in QB.

Hi Marco,

We're currently testing a fix for this issue. I'll keep you updated via this forum thread.

- Justin

Hi Marco,

This is now resolved. Here's what you'll need to do (in order):

1. Log in to Method
2. Change your ExchangeRate in QuickBooks and sync

The new value should sync over and be available for use within Method. Let me know if there's any issues with it.

- Justin

Justin,

Thank you very much. Exchange Rate synchronized fine but not the As of Date Field. 

Marco.

Marco,

Had you already changed the AsOfDate that day, perhaps earlier when you entered the day's ExchangeRate? Is it not syncing at all, or is it incorrect in some way?

Let me know.

- Justin