Symptom
In the worksheet, the local currency conversion will not take place automatically until we click on save when the value is fetched from Lookup Table.
Environment
Compensation Worksheet/Variable Pay Worksheet
Reproducing the Issue
We have a custom field which is formula driven and fetches value from a lookup table.
It fetches the value from the lookup table and it is put in the selected currency of the plan. But after we save/refresh the worksheet, it gets converted to based on the conversion rate of the local currency to Functional currency.
It is observed that this is happening to only the employees with a different currency other than the selected 'Functional Currency USD / Planner Currency USD".
Cause
It's a typical case that constant value is used inside custom formula. Lookup function will return a constant amount value, and our JS code won't do currency conversion for the values in formula.
Resolution
To work around this issue, you need use a help custom column like following:
1. In User Directory, configure some custom field like CustomField6, and the values for CustomField6 are all 10000.
2. Configure customConversion custom field for currency conversion for constant value like following:
<comp-field-definition id="customConversion" isCustomField="true" isVisible="true" type="money" useFor="varpay" importKey="CustomField6" readOnly="true" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" reportable="false" needCalcTotal="false">
<comp-field-label><![CDATA[customConversion]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
3. For the custom field formula that contains constants, multiply constants with customConversion like following:
toNumber(lookup('GAIP_2017_MY',userid,1)) * customConversion / 10000
Then create new worksheets, and the issue will be gone.
Keywords
KBA , LOD-SF-VRP-BUD , Budget setup, budget planning, data etc. , Problem