0

I have a requirement where I have to create a calculated field involving the formula as described below. Could I get some guidance how to go about it..?

The functionality in question is to track expense requests.

Custom Object: - myExpObj__c

Fields: -

  • Requested Expense Amount -> reqExpAmt__C
  • Requester Name -> reqName__C
  • Total Expenses Requested YTD -> "totalYTDExpense__C"

Now, I want to calculate the total expense for the current calendar year for a particular requester.

So, as an e.g. when a user is submitting a new expense request, I need to calculate the total expenses requested by that particular requester YTD, for that particular requester.

Something like - totalYTDExpense__C = (reqExpAmt__C year to date, for the reqName__C in question)

  • 2
    Welcome to salesforce.stackexchange, Please Visit the help center for guidance in asking solid questions: salesforce.stackexchange.com/help/asking. Please provide some information on what you have tried so far – gNerb Nov 9 '17 at 18:06
0

This is too complicated to fit into a simple formula field. You'll need a roll-up field.

Non-user records:

If you're using a contact record associated to a user as with communities or some custom development (many orgs do this in my experience) and the expense report is a master-detail relationship to the contact, a simple roll-up with suffice. Otherwise you may have to create one utilizing a trigger.

User Records:

If you're not using a custom contact-user association or your relationship is not master-detail, you'll need to create a trigger on the object to calculate the data and and store the data in a read only field; probably at the user level. We use LREngine to generate roll-up fields using lookup relationships.

I have not tried to update a user record from within a trigger of a non-setup record. I'd be weary of a Mixed DML Operation. (Disclaimer, the documentation only emphasizes test classes so I'm not sure if this will impact non-test classes). If you do get a mixed DML operation, Refer back to my previous answer and create a system to link users up to contacts.

Formula

Once you have the roll-up generated, you can reference it in a formula field pretty easily on the expense:

reqName__r.totalYTDExpense__c

That would pull in the value of the roll-up into the record (which would include the current record after it has been saved).

  • Thank you, that's how I ended up solving this. I created another custom object and then did a roll up summary. – user50937 Nov 16 '17 at 16:39

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.