Discussions

Expand all | Collapse all

Auto create a table relationship

  • 1.  Auto create a table relationship

    Posted 12-12-2017 08:05
    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


  • 2.  RE: Auto create a table relationship

    Posted 12-12-2017 12:19
    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


  • 3.  RE: Auto create a table relationship

    Posted 12-12-2017 22:04
    Thank you - that works!


  • 4.  RE: Auto create a table relationship

    Posted 12-12-2017 22:08
    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.


  • 5.  RE: Auto create a table relationship

    Posted 12-12-2017 22:28
    Yep, I did. :-)
    Thanks again.