How can I lookup a value from a master table into a detail table based on two criteria (two separate fields) in a detail table?

  • 1
  • 2
  • Question
  • Updated 3 years ago
  • Answered

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?

Photo of Brett Telford

Brett Telford

  • 20 Points

Posted 3 years ago

  • 1
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
No problem

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.
Photo of Brett Telford

Brett Telford

  • 20 Points
Mark,

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?


Brett
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
Brett,
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."
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,924 Points 20k badge 2x thumb
Don't waste your time creating a relationship or even using an FX Rates table. Just do the currency conversion as part of the bulk import of new data using a web service such as fixer.io (FREE) and the money.js (FREE) library:

Fixer.io
http://fixer.io/


Money.jshttp://openexchangerates.github.io/money.js/
https://www.youtube.com/watch?v=-0kcet4aPpQ
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,924 Points 20k badge 2x thumb
Here is the guts of doing the currency conversion through a web service (ie without a FX Rates table and relationship):

Money.js ~ Add new Record
https://haversineconsulting.quickbase.com/db/bkj4q38ss?a=nwr

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

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=475