How to find Entity's Currency in Application Database Tables

[aricgresko]

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. 

[matt515]

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]

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

[aricgresko]

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]

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]

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 · Accepted solution]

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. 

[aricgresko]

Thanks - here's how I ultimately wrote the code.  

Added dummy column in my SQL query. 

sql.AppendLine(", CAST('' as VARCHAR) as [Ent Curr] ")

Then was able to return the currency here.  Definitely is slow looping thru but gets the job done.

If column.Name.XFEqualsIgnoreCase("Ent Curr")
   Dim entityName As String = DataRow("Entity")
   Dim entityID As Integer = BRApi.Finance.Members.GetMemberId(si, dimTypeID.Entity, entityName)
   Dim entityCurr As String = BRApi.Finance.Entity.GetLocalCurrency(si, entityID).Name
      columnValue = entityCurr
End If