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)

1
  • 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
    Commented Nov 9, 2017 at 18:06

1 Answer 1

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).

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .