Salesforce: Query Multi-Currency Field

When you have multi-currency enabled in your Salesforce org., by default you will see the converted value in the page layout, list view, and report.

Page Layout
My user currency is USD and Corporate currency is USD too.

If I change my currency to SGD

List View
The same goes to List View, it will show the amount in the opportunity currency and the value in your currency in brackets.


Report
In the report, Salesforce gives the option to get the values in the Opportunity currency and in the converted amount -- which is the corporate currency.

SOQL
When you query Salesforce currency fields, SOQL will always return currency values as defined in the CurrencyIsoCode.

Format() and convertCurrency()
We can use Format() and convertCurrency() functions for currency field in SOQL:
- Use FORMAT with the SELECT clause to apply localized formatting to standard and custom number, date, time, and currency fields, the format applied these fields reflect the appropriate format for the given user locale.
- Use convertCurrency() in the SELECT clause to convert currency fields to the user’s currency.

Looks at this sample: SELECT Id, CurrencyIsoCode, Amount, convertCurrency(Amount) UserAmount, FORMAT(amount) TextAmount, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity order by currencyisoCode

You can’t use the convertCurrency() function in a WHERE clause. If you do, an error is returned, but you can use the following syntax to convert a numeric value to the user’s currency from any active currency in your org. WHERE Object_name Operator ISO_CODEvalue

e.g. SELECT Id, Name FROM Opportunity WHERE Amount > USD5000
In this example, opportunity records are returned if the record’s currency Amount value is greater than the equivalent of USD5000. For example, an opportunity with an amount of USD5001 is returned, but not JPY7000.



How to get currency value in corporate currency using SOQL?
In Spring ’18 release, Salesforce introduces new formula functions ADDMONTHS, CURRENCYRATE, MCEILING, MFLOOR and WEEKDAY. So this purpose, we can make use of CurrencyRate() function.

CURRENCYRATE returns the conversion rate to the corporate currency for the given currency ISO code. If the currency is invalid, returns 1.0.

We can create a simple formula field
Amount / CURRENCYRATE(TEXT(CurrencyIsoCode))

Here is the result:

If you implement dated exchange rate, CurrencyRate() function do not support it yet, it will always use standard exchange rate, unless you always keep the conversion rate aligned.



Reference:

• Formula Operators and Functions
• Querying Currency Fields in Multi-Currency Orgs
• Format()
• New Formula Functions

Einstein Analytics: Result Binding

There are 2 types of binding in Einstein Analytics:
1. Selection: the query result based on user selection
2. Result: the query result based on the changes in other steps

We have discussed selection binding in the previous blogs: here, here, and here. Now let us looks at result binding.

Here is the sample for this blog:
- We have 2 datasets which are not really linked, but both have user Id and Fiscal
- Opportunity Split dataset have Close Date, which can be used as a toggle

Steps:
1. Create Static Step to have Previous Quarter, Current Quarter and Next Quarter
                            "Static_Period_1": {
                "broadcastFacet": true,
                "label": "Static_Period",
                "selectMode": "singlerequired",
                "start": {
                    "display": [
                        "Current Quarter"
                    ]
                },
                "type": "staticflex",
                "values": [
                    {
                        "display": "Previous Quarter",
                        "value": -1
                    },
                    {
                        "display": "Current Quarter",
                        "value": 0
                    },
                    {
                        "display": "Next Quarter",
                        "value": 1
                    }
                ]
            }

2. For wizard for Opportunity Split dataset, add selection binding to filter the result based on the toggle.
           "query": {
                    "values": [
                        "Fiscal",
                        "Id",
                        "User_Id",
                        "Close_Date",
                        "Amount"
                    ],
                    "filters": [
                        [
                            "Close Date",
                            [
                                [
                                    [
                                        "fiscal_quarter",
                                        "{{cell(Static_Period_1.selection,0,\"value\").asString()}}"
                                    ],
                                    [
                                        "fiscal_quarter",
                                        "{{cell(Static_Period_1.selection,0,\"value\").asString()}}"
                                    ]
                                ]
                            ],
                            ">=<="
                        ]
                    ],
                    "order": [
                        [
                            "Fiscal",
                            {
                                "ascending": true
                            }
                        ]
                    ]
                }

* Static_Period_1 is the step name for static step, see (1)

3. For wizard for Forecast Quota dataset, add result binding to filter the result based on the column in the Opportunity Split step.
          "query": {
                    "values": [
                        "Fiscal",
                        "User_Id",
                        "Quota"
                    ],
                    "filters": [
                        [
                            "Fiscal",
                            [
                                "{{cell(lens_1.result,0,\"Fiscal\").asString()}}"
                            ],
                            "in"
                        ]
                    ],
                    "order": [
                        [
                            "Fiscal",
                            {
                                "ascending": true
                            }
                        ]
                    ]
                }

* Lens_1 is the step name contains selection binding, see (2)
* Fiscal is the column name in Lens_1 step


For this blog sample, when user selects "Next Quarter" in toggle, the Opportunity Split data will be filtered based on selection defined in static step, then Forecast Quota data will be filtered based on Fiscal changed in Opportunity Split step.

Here is the complete JSON file.



Reference: Result Binding

Salesforce: Forecast with Opportunity Split

This is the continuation of previous blog Setup Forecast Quota, in this blog, we will share about Forecast with Opportunity Split Overlay.

1. Opportunity Team and Opportunity Splits

Here is the scenario:
Two users, both users have been enabled for Forecast
1. Johan Forecast is the manager
2. George Mann report to Johan Forecast

Two closed-won opportunities with Overlay split:

opportunity #1

opportunity #2

2. Forecast
Let us the result see in the Forecast tab:

Highlight legend:
- Yellow: this is quota as we discussed in the previous blog
- Pink: this is the forecast item for Closed forecast
- Green: this is summary from all subordinates, including the manager
- Blue: USD 305,500.00 comes from opportunity #2 which is the total amount for the 2nd and 3rd; USD 75,000.00 is from opportunity #1. 


3. SOQL
SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db2v000005aTHcCAM'

Summary:

• The Forecast Amount in ForecastingItem is auto-populated from Opportunity Split Amount based on the Forecast Type and Forecast Category.
• The Period Id in ForecastingItem is auto-populated based on Opportunity Closed Date.
• Each user will only have 1 line for ForecastingItem in a period for a Forecast Type.
• If you have multi-currencies enabled, only 1 currency will be returned in the query result, this currency is based on defined Corporate Currency.
• The Forecast Amount from ForecastingItem in SOQL is roll-up to the manager, and managers numbers will roll-up to the managers' manager.

Forecast Manager in Forecast Hierarchy

Sample:

There are 2 users under Staff 1: Song Lee & Free Man

Forecast tab

SOQL
SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db0k000000076kCAA' ORDER BY ForecastAmount

What will happen if we remove Maria Ann as Forecast Manager for the role hierarchy General Manager?

Forecast tab

Notice:
1. The Amount is no longer rollup from Maria Ann subordinates'. Forecast managers see forecast rollups from users below them in the forecast hierarchy.
2. Click the arrow next to label 'Maria Ann' will not drill down to the subordinates level.
3. Jack Bob numbers are only from Linda Yie as Maria Ann not contributing anything.
4. The number from Song Lee and Free Man are not roll-up to anyone.

SOQL

SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db0k000000076kCAA' ORDER BY ForecastAmount

If Maria Ann have her owned Opportunity Splits

Same for SOQL result, Maria Ann will have USD 18,000.00 for the Forecast Amount.


Now, let us remove Allow Forecasting from Maria Ann

Maria Ann does not appear in the Forecast tab at all.

SOQL:

Remember that Maria Ann still owned Opportunity Split, but she does not appear in both Forecast and SOQL.


Summary:
1. Allow Forecasting is a must for all forecast users.
2. Each manager role in the forecast hierarchy should have a user assigned as the Forecast Manager.
3. The API name for "Allow Forecasting" is ForecastEnabled, you can mass update it with API.

Salesforce: Setup Forecast Quota

1. Enable Opportunity Team

  

2. Enable Opportunity Split and Add Opportunity Split Type (optional)

For this blog, I am adding Overlay opportunity split type

 3. Enable Forecast, Add Forecast Type (optional), and Enable Quota

enable forecast

adding forecast type 

You may need to refresh your web browser to see the “Forecasts Quotas” tab to appear.

4. Enable user for “Allow Forecasting”

From the user detail page or Forecast Hierarchy.

user detail

forecast hierarchy

  

 5. Assign Quotas to Users

Make sure you select the right Forecast Type and select the right currency (if necessary)

Or use the Data Loader tool to load CSV into Forecasting Quota object

6. Set Forecast Manager for each Role Hierarchy

This is only necessary if user have subordinates as per role hierarchy, set up this from Forecast hierarchy.

7. Verify Quota

7A. Forecast Tab

Make sure to select correct Period, Forecast Type, and Currency

7B. SOQL

As mentioned in (5), we can use Data loader to load quota to Forecast Quota object. So, the same we can query from Forecast Quota object too.

SELECT Id, QuotaOwnerId, QuotaAmount, QuotaOwner.Name, ForecastingTypeId, PeriodId FROM ForecastingQuota

• From the above query, this shows us that Quota Amount numbers are NOT roll-up from subordinates to the manager.
• The currency here is based on currency defined when added the quota for each users.

Reference:

• Enable Quotas in Collaborative Forecasts
• Manage Quota Data for Collaborative Forecasts via Data Loader or the API

Salesforce: Get Folder Sharing Access

Report and dashboard accessibility are defined in the folder that stores the reports or dashboard, there are 3 levels of access:
1. Manage
2. Edit
3. View

You can share the folder by:
1. Users
2. Roles
3. Roles and Subordinates
4. Public Group
5. Territory (only when Territory is enabled)
6. Territory and Subordinates (only when Territory is enabled)
7. Partner Users (only when Partner community is enabled)
8. Partner Roles (only when Partner community is enabled)
8. Partner Roles and Subordinates (only when Partner community is enabled)
9. Customer Users (only when Customer community is enabled)
10. etc

Here a sample of report folder shared with users, roles, and etc.

If you see from the above screenshot, Group of GM and Staff 2 are from another screenshot, because of sharing window only able to show 5 lines at a time, so if you need to extract the list of users, roles, and etc., you need to get it from metadata.

Workbench
We can use REST Explorer from Workbench to retrieve this info. Let us walk through step by step:
1. Login to Workbench
2. Select REST Explorer under utilities menu
3. By default, the service would be /services/data/v45.0, click Execute
4. Click on folders: /services/data/v45.0/folders, click Execute
5. Click Expand All link, and search for the folder name, if you can't find it, click nextPageUrl link
6. Or, instead of looking for the report folder, get the folder Id and execute it, e.g.  /services/data/v45.0/folders/00l90000001MUVKAA4
7. Click sharesUrl: /services/data/v45.0/folders/00l90000001MUVKAA4/shares
8. Click Expand All link, now you get the list, but to put it into nice table file would need another effort

Convert JSON to CSV
8. Click "Show Raw Response" link
9. Copy the JSON file to http://www.convertcsv.com/json-to-csv.htm
10. Download the result into CSV file

Reference:

• FolderShare

Einstein Analytics: Embed Analytics Dashboards with multiple fields filter

Here is the scenario:
Show all Opportunities with filter: compare Parent_Account_ID__c value from Opportunity page (a formula field CASESAFEID(Account.ParentId)) with 2 fields from a dataset: AccountId and Parent_Account_Id__c.

As per Summer '19 release, we can use Filter Builder or Filter String, but unfortunately, the filters do not support OR logic.

Here is the workaround for the scenario:
1. Create a field in Dataset using ComputeExpression to concatenate AccountId + Parent_Account_ID__c, let us say AccountIdnParentId.

2. Use the new field created from ComputeExpression with "Contains" operator.

In case if Parent Account Id is blank, this will cause the dashboard error, you can create a custom formula field in the Opportunity to check and make sure if Parent Account Id is blank, use Account Id.

Reference: Embed Analytics Dashboards in Lightning Pages