Hi all,
I’m starting with Knack, and I’m having difficulties to create something that shouldn’t be that difficult, so I’m probably missing something.
I have a table “Invoices”, with columns “Invoice number”, “Invoice Date”, “Amount”, “Currency”, “Amount in USD”. I have a 2nd table, “FX Rates”, with columns “FX Date”, “FX Currency”, “FX Rate”. I want to automatically calculate the value “Amount in USD” using the corresponding FX Rate based on the Invoice Currency and Date values. How can that be done?
So far, I’ve created an auxiliary field that joins the currency and date in the form “YYMMCurr”, so the filtering is limited to “show me the FX rate for the aux value 2501EUR”. But unless I manually assing that value to the connected field, I’m not able to make it work.
Thanks all,