0

I am trying to create a custom formula field (Percent) off of another custom formula field (Number) that self references the Number field to find the highest (MAX) value and to convert the Number field/values into Percent.

Current Custom Formula Field (Percent) created:

Average_Daily_Eligible_Submissions__c / MAX(Average_Daily_Eligible_Submissions__c)

However, the results seem to indicate the formula isn't calculating as expected. The range of average daily eligible submissions (number) goes from 0 to 1. The record with average daily eligible submissions = 1 should return 100% (which is happening) but record with average daily eligible submissions = 0.7 (as an example) should return 70% as in 0.7/1 (ie. 1 being the max value). However the later returns 100% too currently.

Any idea how to fix this formula/or how to use MAX function appropriately?

THANK YOU SO MUCH!

Improve this question
9
  • I realize that the reason it's returning 100% for all record is because it's looking at the current record's values (rather than retrieving the highest value across multiple records)
    – Chloe
    Jul 8, 2022 at 20:12
  • this is a report formula?
    – cropredy
    Jul 8, 2022 at 23:22
  • No, it's a formula field I am trying to create on the Contact object. My goal is to convert a Number field (already existing on Contact object) into a % field (where the % is calculated in relation to the highest/max value of the said Number field, across all contact records). I want to know what % each contact record represents against the highest (contact) record/value.
    – Chloe
    Jul 9, 2022 at 21:04
  • I'm confused formula fields can't reference multiple records of the same object.
    – cropredy
    Jul 9, 2022 at 21:13
  • I am finding this out now ah! I am clearly learning! Do you have any idea how I could store (dynamically) the highest value of a field (across all records of a single object), maybe using flow? If I could store this (max value) number on all individual records (find the max value > update a field on the contact record), I could then create my formula field using the fields of the single record. Not sure if what I am saying is clear enough ah!
    – Chloe
    Jul 9, 2022 at 23:49

1 Answer 1

Reset to default
0

If I understand the requirements correctly, you will need to do something like the following:

  • Create a scheduled batchable (Apex) that runs daily (perhaps after today's quotes are in). Be sure to implement Database.Stateful

    • The start() method does a query to count the number of quotes received that day, week, month, year, last12 months, etc. Save this in an instance variable of the batchable
    • The start() method then does a Select SOQL to choose the Contacts that need to be updated with the count(s) fetched above
    • The execute() method will get 200 Contacts at a time and you can use trivial DML to update the Contacts
    • The finish() method does nothing.

  • With the job, you'll have in every Contact the denominator you will need to then construct a formula field(s) that calculates the tier. Note this solution defines the tier as of the end of the day, not real-time during the day

If you go this route, and get stuck, post a new question with your code rather than keeping this question/answer going

Improve this answer
1
  • Thanks for your response! This will be out of my league so I will have to ask for extra support. I appreciate your help though!
    – Chloe
    Jul 26, 2022 at 2:08

You must log in to answer this question.

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