I have two fields in a detail table (Expenditures), [Currency] and [Invoice Date]. I have two similar fields in a master table (FX Rates), [Currency] and [Conversion Date]. There is a field in the FX Rates table [Rate] that I would like to pull into the Expenditure table if the two fields in the Expenditure table match the two fields in the FX Rates table.
How can I get this to work?
You will need to make a new field in your currency rate table which will be a "compound key" field. It will be a concatenation of the currency and the conversion date
For example CDN-01-23-2016.
What I do to avoid having users having to type that in, is to make a formula field to calculate that string based in the two existing fields you have. Then make a form rule to force that key field to always be equal to the formula field
Then set that new field to be the key field of the currency x rate table.
Then in your expenditure table make a formula to calculate the same string and use that as the reference field on the right side of a relationship to the currency x rate table, and pull down the x rate for that currency for that date.
Thank you! I tried something similar to this but hit a road block. The main user of the expenditure table will be uploading potentially hundreds of records per day. I'd like her to be able to just upload the invoice date and currency and have QB pull the rate automatically from the FX rate table. It keeps forcing me to pick from the drop down list on the reference field to drive the lookup field. Is there anyway to get the reference field to not be a drop down or to make the lookup field populate without having to do a drop down in each record?
We are not on the same wVelength here.
Did you do this formula field in the Expenditure table and use that as the reference field?
"Then in your expenditure table make a formula to calculate the same string and use that as the reference field on the right side of a relationship to the currency x rate table, and pull down the x rate for that currency for that date."
Money.js ~ Add new Record
Just enter a dollar amount and the other currency fields will populate. This may appear to solve a related but different problem as I think you would want to do the conversion during a bulk import but the code would be the same and the import code would be similar to the one in this post: https://quickbase-community.intuit.com/questions/1302318