An Update On The "Winner Picker" Formula Field

Two weeks ago I described a formula for a field called Winner_Picker__c that helps perform a many-to-one cardinality reduction.

After last week's immersion in deduplicating our records, today I realized that it needed some refinement to handle deduplicated Contacts.

 

When the source of duplicate Contact records in SalesForce is that there exist duplicate Person records in our ERP Banner, first we merge those records in Banner - then we merge them in SalesForce and clean up any redundancies.

Merging the "Admission" records dangling off of a single Person as the "Person" records are combined in Banner happens as part of manual data entry before the computerized merge.

In SalesForce, it makes more sense for us to do the computerized merge of Contacts first and clean up redundant dangling records that they both brought to the party afterwards.

 

My original formula only produced a unique Winner_Picker__c value within the scope of a person. Today I fixed it so that the formula does the final "coin toss" based on data unique to a SalesForce Contact, rather than data unique to a Banner person (more than one of whom might be dangling numerically identical Admissions records off of Contact until manual cleanup is performed).

Instead of pulling the least significant digits from an ERP record ID, I now pull them from an AutoNumber-typed Record ID from SalesForce called "Name." (Note: Apparently they are up to 10 digits long).

I also realized that having a 2nd Record_Type_Prioritizer__c call wasn't necessary, which is good, because my resulting number was getting too big for SalesForce..

 

The updated code is:

IF(

Term_Lookup__r.Future__c ,
IF(

OR(

ISPICKVAL(Status__c, "Deferred"),
ISPICKVAL(Status__c, "Rejected"),
ISPICKVAL(Status__c, "Withdrawn")

)
, null
,
(

(((Record_Type_Prioritizer__c * 1000000) - Term_Lookup__r.Term_Code_Numeric__c)* 10000000000)
+
VALUE(RIGHT(NAME,LEN(NAME)-4))

)

) , null

)

Rolling Up A Single Record To Its Master Object

Rollup fields are the other of the two types of "calculated value" field allowed in SalesForce.

When you have a "Master-Detail" relationship between two objects in SalesForce, you can add a Rollup Field to the "Master" object that shows the result of an aggregation (count/min/max/etc.) on the values found in a single "column" of the "Detail" object.

But you don't have to use rollup fields exactly as they were intended!

 

Today, I was asked to add a field to "Admissions" called "Application Form Received Date."

After poking around in reports from our legacy system (Banner), I discovered that we calculate this date by looking at the "Received Date" of the checklist item called "Application Form" that is attached to the a given "Admissions" record.

In both Banner and the SalesForce "dumping ground custom objects" for imported Banner data, each application document that we have received is stored in its own record of a "Checklist" object.  "Checklist" is at the "detail" end of a Master-Detail relationship with "Admissions."

It just so happens that in our data model, no "document type" may be used twice in the same set of "Checklist" records.  (Or, in our classic "tines of a garden rake" example, no color of tine may be used more than once on a given rake.)

 

So back to the analogy of children drawing on a garden rake (copying details) as they build it:  the rule here is that if you see an orange tine, you have to copy whatever is doodled on it onto the handle of the rake.

A formula field won't help you implement this rule. Formula fields don't know what to do when they have multiple rows to choose a value from, so they can only copy field values "from handle to tines" (from Master to Detail or from LookedUp to LookingUp).

 

However:

• If you want to copy NUMERIC or DATE data "from tine to handle" (from Detail to Master),
• And you know that you can specify "which color of tine" by inspecting the values of fields on the "tine" (except formula field values),
• And you know that only "1 tine of that color" will exist...

...Then doing a MAX or MIN "Rollup Field" on the "tine" (Detail) field will make an exact copy of it on the "handle" (Master).

 

After all, the solution to "What is the biggest number between 4 and 4?" is ... 4.

 

To build the "Application Form Received Date" field on the "Admissions" object, I added a Rollup Field defined as the maximum "Received Date" among all eligible Checklist items. I then filtered the definition of "eligible Checklist item" to records where "Document Type" = "Application Form" (knowing that there would be only 0 or 1 such records).

 

Yay for kludges! (I think I want to print that and hang it on my wall.)

A Formula Field To Pick The Best Custom Object Among Many

Formula fields are one of two types of "calculated value" field allowed in SalesForce. (The other type being lookup fields, which do not allow any calculations besides aggregations.)

As I mentioned, while most universities choose either custom "Admission" objects or the SalesForce native "Opportunity" object to represent the many-to-one nature of people trying to matriculate to their school, we are forced to live in, and synchronize, both models.

Keeping this data synchronized requires code to perform a cardinality reduction. I used a formula field to do the heavy lifting.

 

Note: If you want to skip the background and go straight to the formula field's source code, click here.

 

Imagine that you're building a garden rake with tines at both ends of the handle. (The handle is a particular "Contact." Each tine at 1 end of the rake represents "Admissions" records. Each tine at the other end of the rake represents "Opportunity" records.)

• You allow the neighbor's children to add tines to the "Admissions" end of the rake.
  (This is like the Central Admissions Office adding records about paperwork coming into their office from the Contact.)
  • The neighbor's children are allowed to draw pretty pictures on the "Admissions" tines before they attach them to the rake.
    (This is like the Central Admissions office recording the details of that paperwork.)
• You allow your own children add tines to the "Opportunities" end of the rake.
  (This is like your department adding records about their intuition that someone is interested in matriculating based on phone calls, event attendance, etc.)
  • Your are allowed to draw pretty pictures on the "Opportunities" tines before they attach them to the rake.
    (This is like your office recording the details of when they think the person would want to matriculate and other relevant information.)

You have a special rule for your children (working on the "Opportunities" tines): Although they're not allowed to add tines to the "Admissions" end of the rake, periodically they must look at the "Admissions" tines that have already been attached. If they see that there is an "Admissions" tine attached to the rake that is the same color as one of the "Opportunity" tines that they have attached, they must copy over all of the pretty pictures from that "Admissions" tine onto their matching "Opportunity" tine.
(This matches our business rule that SalesForce must push all data from "Admissions" objects to their matching "Opportunity" objects so that our recruiters can see everything they need to see on "Opportunity" objects.)

 

That's the kind of environment we're developing in.

There's just one more catch:
Our Central Admissions Office is allowed to add multiple tines of a given color, whereas our Departmental Recruiters must make every tine at their end a unique color. (The color of a tine is analogous to "Program Of Interest.")

The ERP requires that every Person-Program-Term combination be given its own record. Our recruiters don't care about that level of detail - they just want to see the "most relevant" information for a given Person-Program combination.

 

So we have a cardinality reduction problem on our hands. This lets the "doodle-copying" code know which "blue tine" on the "Admissions" side of the rake is the "best" so that we can copy its doodles over to the "blue tine" on the "Opportunity" side of the rake. Otherwise, the "doodle-copying" code will get confused.

 

Here's how I solved it: (update here)

First, I created a formula field on Admissions called Record_Type_Prioritizer__c.

I should explain that: I've oversimplified our problem up above. We actually have 2 similar record types coming in from the ERP - "Recruit" and "Admissions." So Record_Type_Prioritizer__c has objects label themselves 9 (if Admissions), 8 (if Recruit w/ an "app started" flag), or 7 (plain-old recruit).

Next, I created a formula field on Admissions called Winner_Picker__c. (It's also on "Recruit.")

Here is how I described Winner_Picker__c to end users so they could review my logic:

WinnerPicker is a calculated numeric field. First, it disqualifies any "past" records or "withdrawn" by being blank for such records. Then it ranks Admissions records as best, Recruit records with an Application Started? flag as 2nd-best, and other Recruit records in 3rd place. Within each of those categories, it ranks records in descending order from soonest term (best) to futuremost term (worst). If there are still any ties, it breaks them "coin toss" style by ranking them in descending order according to their unique record IDs from Banner.

Here is the math behind this formula:

• Return null for any term that has already started or any Admissions record with an inactive status
• Admissions = 9,000,000; Recruit+Flag = 8,000,000, Recruit-flag = 7,000,000.
• Subtract term code (a 6-digit #; subtraction because we care about sooner terms more than later ones as long as they’re in the future).
• Multiply that 7-digit # by 1,000,000 to add a comfortable amount of zero-padding at the right.
• Add 900 or 800 or 700 (Adm / Recr+Flag / Recr) because the last 2 digits are unique within, but not across, sets of Admissions/Recruit records for a person.
• Add the 2 unique digits not already covered in the previous code from the Admissions/Recruit record's primary key in the ERP as a tie-breaker.

Finally, here is the source code of Admissions.Winner_Picker__c:
Note that Admissions's "Term" field is a Lookup Field to a "Term" object. "Term" records are full of fun formula fields themselves! They provide data about a given term in many formats: as a sortable number, as a comparison of its start date to today, etc.

IF(
Term_Lookup__r.Future__c ,


IF(
OR(
ISPICKVAL(Status__c, "Deferred"),
ISPICKVAL(Status__c, "Rejected"),
ISPICKVAL(Status__c, "Withdrawn")
)
,
null,
(((Record_Type_Prioritizer__c * 1000000) - Term_Lookup__r.Term_Code_Numeric__c)* 1000000)
+
((Record_Type_Prioritizer__c * 100) + VALUE(RIGHT( Banner_Application_ID__c , ( (LEN(Banner_Application_ID__c) - 6) - LEN(Formula_Contact_PIDM__c) ) )))
)


, null
)

 

 

The Result:

Each "Admission" record looks at its field values and suggests its "self-worth" as a number.

This "self-worth" number is guaranteed to be a unique value among all Admissions+Recruit records with the same Person+Program combination.

Thanks to this formula field, you simply have to perform an "IS NOT NULL" and a MAX() to retrieve the best ERP-imported record for a Program.
(Or, in other words, "The best blue tine on the rake.")

It saved us a lot of time during a Requirements Analysis meeting with our Partners doing trigger development work to say, "Don't worry about the cardinality reduction logic - we got it - just MAX() this field for the Person+Program."

 

Now if only you could use a Rollup Field to MAX() Formula Fields ... Don't forget to click here and upvote! :-)