Auto create a table relationship

  • 1
  • 1
  • Question
  • Updated 1 year ago
  • Answered
Hi Quickbase users,

I have a table with a Date field and a Numeric field which can be either dollars or euros.
One more field specifies the currency (i.e. "USD" or "EUR").

Now I am trying to create a new calculated Numeric field which would have everything in EUR.
To achieve this I created a second table with daily USD-EUR FX rates (it has date and rate fields).

What I am trying to achieve is to automatically retrieve the exchange rate for a relevant date from the FX table and multiply it by the Numeric field in the first table.

I tried to create a table to table relationship but this way you need to link these tables manually.
I am looking to automatically link them using the date field as reference.

Hope it makes sense.

Thank you,
Dmitry
Photo of Dmitry Bejenar

Dmitry Bejenar

  • 90 Points 75 badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
No problem

Make the Key field of your FX rates table be the key field.

Make a new field on the data table called [Date mirror for Relationship]. The formula will just be [Date]

Make a relationship between the two tables based on that relationship.

Look up the FX rate.

The reason for creating that extra mirror formula field is so that you can still conveniently enter dates into your data table and still get to use the calendar picker
Photo of Dmitry Bejenar

Dmitry Bejenar

  • 90 Points 75 badge 2x thumb
Thank you - that works!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Thx I see I had a typo above that you figured out  

Make the date field of your FX rates table be the key field.
Photo of Dmitry Bejenar

Dmitry Bejenar

  • 90 Points 75 badge 2x thumb
Yep, I did. :-)
Thanks again.