Forum Discussion

aricgresko's avatar
aricgresko
Contributor III
6 months ago

How to find Entity's Currency in Application Database Tables

I'm working on a custom Table View that returns People Planning Plan Data for our users.  The new requirement is to also bring in the Currency of the data. This means I need to bring in the Currency that is assigned to a given Entity (i.e., Currency assigned in Entity Member Properties in the Settings section).  I've looked through the Member, MemberDescription, and MemberProperty Application Database Tables but cannot seem to find where the Currency name is stored.  

If anyone can point me to where I can find an Entity's Currency stored in the Application Database Tables, that would be fantastic. 

Reports
  • Henning's avatar
    Henning
    6 months ago

    You can loop through your data table you are pulling from People Planning and update each row by using the entity information in column X (I assume the entity is in a dedicated column), pull the entity ID, pull the currency using the GetLocalCurrency function, and update the column that shall display your entity's currency using:

    BRApi.Finance.Entity.GetLocalCurrency(si, myEntityId).Name

    As this uses loops and BRApis, this may not be as performant as you need it to be. Try to keep the displayed table small so that users do not feel the (possible) impact if necessary. 

     

     

     

     

  • matt515's avatar
    matt515
    New Contributor III
    6 months ago

    I was able to locate them by using MemberProperty DimTypeID  = 0 (Entity) , PropertyID = 100 and then find your member id and look in the decimal value column. In my case, we have two currencies. 27 = CAD and 176 = USD.

    There's probably another table with the currency names based on the value, but I haven't located it yet.

    • matt515's avatar
      matt515
      New Contributor III
      6 months ago

      I found a parameter that uses a method query that was able to tell me which values equated to the currency

      • aricgresko's avatar
        aricgresko
        Contributor III
        6 months ago

        Thanks, this was the path I was going down, but hadn't run across that parameter you mentioned.  
        Looking at the PropertyID field in the MemberProperty table, I'm noticing that only foreign (in my case non-USD) Entities have a PropertyID = 100.  In my screenshot below, the orange section is a foreign Entity and the white section is a USD Entity. 

         

  • Henning's avatar
    Henning
    Valued Contributor II
    6 months ago

    Hi, why not use (one of) the following?

    Dim idCurrency As Currency = api.Entity.GetLocalCurrency(api.Pov.IC.MemberId)
Dim idCurrencyID As Integer = api.Entity.GetLocalCurrency(api.Pov.IC.MemberId).Id
Dim idCurrencyName As String = api.Entity.GetLocalCurrency(api.Pov.IC.MemberId).Name

    This will make your code more easy to read (without having seen it), easier to administer, and likely more future-proof. Just a suggestion.

    • aricgresko's avatar
      aricgresko
      Contributor III
      6 months ago

      I'm writing this within a Spreadsheet Business Rule, so I can't steal your code exactly as it's written.  Basically, I have a SQL query returning data from the XFW_PLP_Plan table.  I've also added a dummy column within my SQL query where I was hoping to populate the Entity's Currency.  

      I'm happy to post the code if that is helpful. Thanks!

      • Henning's avatar
        Henning
        Valued Contributor II
        6 months ago

        You can loop through your data table you are pulling from People Planning and update each row by using the entity information in column X (I assume the entity is in a dedicated column), pull the entity ID, pull the currency using the GetLocalCurrency function, and update the column that shall display your entity's currency using:

        BRApi.Finance.Entity.GetLocalCurrency(si, myEntityId).Name

        As this uses loops and BRApis, this may not be as performant as you need it to be. Try to keep the displayed table small so that users do not feel the (possible) impact if necessary.