Discussions

Expand all | Collapse all

Summary tables and Currency

  • 1.  Summary tables and Currency

    Posted 18 days ago
    Hi all,

    I have a Summary table which rolls up the totals of data that's added to a table called 'Opportunity Products'. Each line of data will have a 'Total price' added to it, but the addition depends on where it has been added from (UK/EU/US) so therefore data is added in three currencies. This poses a bit of an issue in providing summaries as I would love to just have the total figure of all of the revenue rather than split out by currency.
    Is there an easy way to tackle this which doesn't alter the structure of my data too much?


    Thank you,
    Michael


  • 2.  RE: Summary tables and Currency

    Posted 18 days ago
    Are you looking to commingle the currencies into 1 figure or convert them all to a common currency, like all into USD?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Summary tables and Currency

    Posted 18 days ago
    Hi Mark,

    Ideally, convert them all into a common currency for this purpose (GBP) but not alter how they are originally added into the tables if that makes sense?

    Thanks,
    Michael

    ------------------------------
    Michael Bretton
    ------------------------------



  • 4.  RE: Summary tables and Currency

    Posted 18 days ago
    Well the super low tech way is simply to create a table of exchange rates with three records in it.  The key field of the table will be the currency code and you would have entries for the conversion factor. The conversion factor for  GPB would be 1.000, the US rate would be about 0.75 and the conversion for the EU would be about 0.89.

    He would make a relationship between the currency exchange table and your transactions table to look up the exchange rate and then have a formula to calculate the Total Price multiplied by the exchange rate.

    Everything would be converted to a common currency.

    The other factor to consider is whether you were looking to make the conversion at the current exchange rates or do you want to freeze them at the time the records were created.    If you want to freeze them at the time that the records were created then you can use what is called a snapshot field. (see link for Help) This will be an additional numeric  field Which will hold the frozen or snapshot it value of that exchange rate at the time the transaction record was created.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------