1

I have a formula field which returns me a field's api name. I want to use that api name for further computation by retrieving the value stored in that field.

  1. The field whose api name is computed by the formula field is from the Product object.

  2. Formula field is in the Samples object.

  3. Samples object has a lookup to Product object.

I know we can directly do this by accessing it through relationship. Like Product__r.Asia_Region_Available_Qty__c but the reason of using the formula field was just that it is not clear until the request has been made by user. Because I have multiple quantity fields and it needs to decide by the region.

I'm able to identify the field's api name by my formula field. The actual purpose to read in the api name of the field is to use its value stored in it. Which is in fact a number. I have to use that value.

You can see below in my CASE expression that I'm using the Custom settings to bring in the fields api names. 

CASE( Account__r.Office__c , "Americas", $Setup.PQFMap__c.Americas__c, 
"EMEA", $Setup.PQFMap__c.EMEA__c, 
"Corporate", $Setup.PQFMap__c.Corporate__c, 
"Australia", $Setup.PQFMap__c.Australia__c , 
"China", $Setup.PQFMap__c.China__c , 
"Hongkong", $Setup.PQFMap__c.Hongkong__c , 
"Taiwan", $Setup.PQFMap__c.Taiwan__c , 
"Korea", $Setup.PQFMap__c.Korea__c , 
"Japan", $Setup.PQFMap__c.Japan__c , 
"India", $Setup.PQFMap__c.India__c , 
"Rest of APAC", $Setup.PQFMap__c.Rest_of_APAC__c, 
" ")
Improve this question
5
  • Can you post whatever you have tried
    – Santanu Boral
    Jul 22, 2016 at 5:04
  • Is the field going to be one of a few options or could it be fully dynamic (i.e. based on user text input or something?)
    – Matt Lacey ♦
    Jul 22, 2016 at 5:10
  • @LaceySnr Yes it could be one of few options. My formula actually have a condition which checks a Office_region__c field's value and then decide about the quantity field. For example if the region is America the qty field will be of America, if Asia then Asia etc.
    – Superman
    Jul 22, 2016 at 5:15
  • 3
    Possible duplicate of Compiled Formula is too big : Also does not work under Workflow criteria
    – Nick Cook
    Jul 22, 2016 at 5:36
  • @NickCook this is not the same question. Don't judge it by its answer. Look at the question please.
    – Superman
    Jul 22, 2016 at 5:38

2 Answers 2

Reset to default
1

You already done the whole lot of work, all you need is to utilize the combination of field that you updated with the relevant quantity field and the Account__r.Office__c.

Below I'm providing a rough formula which can help you to bring in the value of the quantity field. I'm assuming the name of the qty fields from your above given information. It may have some syntax issue and I hope you can resolve them easily.

CASE( Account__r.Office__c ,
"Americas", Product__r.Available_Qty_Americas__c,
"EMEA", Product__r.Available_Qty_EMEA__c,
"Corporate", Product__r.Available_Qty_EMEA__c,
"Australia", Product__r.Available_Qty_APAC__c,
"China", Product__r.Available_Qty_APAC__c,
"Hongkong", Product__r.Available_Qty_APAC__c,
"Taiwan", Product__r.Available_Qty_APAC__c,
"Korea", Product__r.Available_Qty_APAC__c,
"Japan", Product__r.Available_Qty_APAC__c,
"India", Product__r.Available_Qty_APAC__c,
"Rest of APAC", Product__r.Available_Qty_APAC__c,
0)
Improve this answer
1

If you use 2 formula fields, you can achieve this.

  1. 1st formula field in Sample object will store "Quantity" from Product, here formula type is "Number". Instead of storing API name you can directly store a value in it conditionally.
  2. 2nd field will use 1st formula field and the field on Sample object which you want to subtract.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.