ShowAll Questionssorted byDate Posted
Angela Tapp

# Complex Date Field Calculation

Hello
I have to import data into existing contact records.
It comes from an online form.
There are two pieces of available data:
1.) Date stamp of when form was submitted
2.) "Current Grade" (this is any grade from Pre-K through 12th grade and it is based on the person's grade at the time the form was submitted.

In Excel, I have calculated a third piece of information but I can't figure out how it could help me in Salesforce. It is "Projected Graduation Year".

What I need Salesforce to do is this:
1.) Report the contact's 'current grade' in real time.  So I will have data from two years ago when a contact was in 2nd grade.  But when I open their record, it will show they are in 4th grade.  A new grade year will begin in June of each year.
2.) Calculate the contact's 'projected year of graduation'.  It is assumed that each contact will graduate in May of their 12th year.

The forumula I used in Excel was:     =W2+(12-V2)
Where W2 is the year it was (in May) when the contact submitted the form)
Where V2 is the grade the contact was when the contact submitted the information.

Best Answer chosen by Angela Tapp
Angela Tapp
I'm going to close this out, but I'll be back because this is only the first part of what I'm needing to do.
I will still need to figure out the projected graduation year and then make it all work in the process buidler as Tom has recommended.
Here is the code I ended up using:

IF(Today()<Date(Year(Today()),6,1),
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
Null),
'12th',13,
'11th',12,
'10th',11,
'9th',10,
'8th',9,
'7th',8,
'6th',7,
'5th',6,
'4th',5,
'3rd',4,
'2nd',3,
'1st',2,
'Kindergarten',1,
'Pre-K',0,
Null))

Steve Molis
I've done Formulas like that, let me dig around my Dev org and see if I still have one
Angela Tapp
Thank you, Steve!
Steve Molis
Okay, it might take a while to track it down.  When do you need it by?  I'm on the East Coast (Boston)
Angela Tapp
It's not urgent.  But I am actively trying to figure it out.  Thanks
Tom Hoffman
Steve...I'm watching Live PD & having a DFH 120...so this looked like my Friday night equivalent of a crossword puzzle.  Feel free to correct my thoughts below...but I think this is how I would tackle this.

We need to use formulas to determine three values,:
Years to Go (years between today * graduation date)

The only information we have is 'Current Grade' at the time the form is completed
Date the form is completed

Based on the above, here is my thought:

Years to Go (number) – use this field to store the the years remaining to graduation when the form is created.  This assumes the current grade you are capturing is reported as text field.  Set this value with a workflow or PB when a record is created

IF(Today()<Date(Year(Today(),6,1),
‘12th’, 0,
‘11th’, 1,
‘10th’, 2,
‘9th’, 3,
‘8th’, 4,
‘7th’, 5,
‘6th’, 6,
‘5th’, 7,
‘4th’, 8,
‘3rd’, 9,
‘2nd’, 10,
‘1st’, 11,
‘Kindergarten’,12,
‘Pre-K’, 13,
Null),
‘12th’, 1,
‘11th’, 2,
‘10th’, 3,
‘9th’, 4,
‘8th’, 5,
‘7th’, 6,
‘6th’, 7,
‘5th’, 8,
‘4th’, 9,
‘3rd’, 10,
‘2nd’, 11,
‘1st’, 12,
‘Kindergarten’,13,
‘Pre-K’, 14,
Null))

This will return a number field between 0-14, 0-13 prior to June 1st, 1-14 for after June 1
st.

Now to calculate Projected Graduation Year & display w/o a comma after 2 (ex 2020 vs 2,020):
Text(Year(DateValue(CreatedDate))+YearsToGo)

For example – if you select Pre-K today in July 2018, the projected graduation year would 2032.  If you selected pre-k on April 30, the projected graduation year would be 2031.

Annnnddd one last field, ‘Grade Today’ to find the real ‘current grade’ not the one you are capturing when the form is being created.

We’ll use the non Text() vers of the Projected Graduation Year to determine the student’s grade today.

IF(Today()<Date(Year(Today(),6,1),
Case((Year(DateValue(CreatedDate))+YearsToGo) -Year(Today()),
0, ‘12th’,
1, ‘11th’,
2, ‘10th’,
3, ‘9th’,
4, ‘8th’,
5, ‘7th’,
6, ‘6th’,
7, ‘5th’,
8, ‘4th’,
9, ‘3rd’,
10, ‘2nd’,
11, ‘1st’,
12, ‘Kindergarten’,
13, ‘Pre-K’,
null),
Case((Year(DateValue(CreatedDate))+YearsToGo) -Year(Today()),
1, ‘12th’,
2, ‘11th’,
3, ‘10th’,
4, ‘9th’,
5, ‘8th’,
6, ‘7th’,
7, ‘6th’,
8, ‘5th’,
9, ‘4th’,
10, ‘3rd,’
11, ‘2nd’,
12, ‘1st’,
13, ‘Kindergarten’,
14, ‘Pre-K’,
null))

Angela Tapp
Hi Tom,
Thank you for taking the time to play with this.
I got a syntax error with the following code to first find out 'years to go'

IF(Today()<Date(Year(Today(),6,1),
‘12th’, 0,
‘11th’, 1,
‘10th’, 2,
‘9th’, 3,
‘8th’, 4,
‘7th’, 5,
‘6th’, 6,
‘5th’, 7,
‘4th’, 8,
‘3rd’, 9,
‘2nd’, 10,
‘1st’, 11,
‘Kindergarten’,12,
‘Pre-K’, 13,
Null),
‘12th’, 1,
‘11th’, 2,
‘10th’, 3,
‘9th’, 4,
‘8th’, 5,
‘7th’, 6,
‘6th’, 7,
‘5th’, 8,
‘4th’, 9,
‘3rd’, 10,
‘2nd’, 11,
‘1st’, 12,
‘Kindergarten’,13,
‘Pre-K’, 14,
Null))
Tom Hoffman
It was missing a ) to close the Year() around Today()

Also, when copying & pasting, sometimes SF doesn't like the ' ' and sometimes carriage returns, but otherwise, I believe this syntax is correct.  If using PB you'll need to add the object name like this: [ObjectName].Grade_When_Submitted__c

IF(Today()<Date(Year(Today()),6,1),
‘12th’,0,
‘11th’,1,
‘10th’,2,
‘9th’,3,
‘8th’,4,
‘7th’,5,
‘6th’,6,
‘5th’,7,
‘4th’,8,
‘3rd’,9,
‘2nd’,10,
‘1st’,11,
‘Kindergarten’,12,
‘Pre-K’,13,
Null),
‘12th’,1,
‘11th’,2,
‘10th’,3,
‘9th’,4,
‘8th’,5,
‘7th’,6,
‘6th’,7,
‘5th’,8,
‘4th’,9,
‘3rd’,10,
‘2nd’,11,
‘1st’,12,
‘Kindergarten’,13,
‘Pre-K’,14,
Null))
Angela Tapp
I'm sorry - I don't know what PB means
Tom Hoffman
Peanut Butter or Process Builder, depends on where its used really ;)

Your PB would be on the object being created, evaluate the process builder when a record is created only, only criteria would be 'current grade' is null boolean false, immediate action - update records, update the record that caused the process to be evaluated, and then your field update would be this formula.

Your YearsToGo field (number field) would be set by formula, then enter this for the formula

IF(Today()<Date(Year(Today()),6,1),
‘12th’,0,
‘11th’,1,
‘10th’,2,
‘9th’,3,
‘8th’,4,
‘7th’,5,
‘6th’,6,
‘5th’,7,
‘4th’,8,
‘3rd’,9,
‘2nd’,10,
‘1st’,11,
‘Kindergarten’,12,
‘Pre-K’,13,
Null),
‘12th’,1,
‘11th’,2,
‘10th’,3,
‘9th’,4,
‘8th’,5,
‘7th’,6,
‘6th’,7,
‘5th’,8,
‘4th’,9,
‘3rd’,10,
‘2nd’,11,
‘1st’,12,
‘Kindergarten’,13,
‘Pre-K’,14,
Null))

Angela Tapp
ahh.. got it.
I was just trying to create the first formula via a custom field.
Since all of my contacts are already imported and I have to import their current grade, it felt like process builder was going to be a bit over my head.  But it might be unavoidable?
Tom Hoffman
It is and it is not. If you wanted to set years to go in excel and update, that would work, but you would likely need a pb for future records. It's not that we can't create 3 appropriate formulas, it's the third one probably will be over 5000 character compile limit, so I'm being preemptive with a process flow.
Angela Tapp
FYI - playing around with tit - I'm still getting a syntax error..
Angela Tapp
Ok, I found the error but now new error Error:

"Formula result is data type (Number), incompatible with expected data type (Text)."
Angela Tapp
I set the field data type to number b/c the system is asking for it.
The calculation is slightly off (see picture).
Here is my current code for that field:
IF(Today()<Date(Year(Today()),6,1),
'12th',0,
'11th',1,
'10th',2,
'9th',3,
'8th',4,
'7th',5,
'6th',6,
'5th',7,
'4th',8,
'3rd',9,
'2nd',10,
'1st',11,
'Kindergarten',12,
'Pre-K',13,
Null),
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
Null))​

Angela Tapp
I have a feeling the count down is backwards.. I'll give it a go.
Angela Tapp
I'm going to close this out, but I'll be back because this is only the first part of what I'm needing to do.
I will still need to figure out the projected graduation year and then make it all work in the process buidler as Tom has recommended.
Here is the code I ended up using:

IF(Today()<Date(Year(Today()),6,1),
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
Null),
'12th',13,
'11th',12,
'10th',11,
'9th',10,
'8th',9,
'7th',8,
'6th',7,
'5th',6,
'4th',5,
'3rd',4,
'2nd',3,
'1st',2,
'Kindergarten',1,
'Pre-K',0,
Null))
This was selected as the best answer
Tom Hoffman
Hey sorry, just seeing all the messages, been out in the yard all day - I'll read through them later tonight and respond.
Angela Tapp
Hi Tom, This was working, but students coming in for this new school year are skipping a grade - ie, it is making them a grade higher than what they are.

For example, if the date stamp is today and the current grade is 3rd, the system is making them 4th graders.

Doesnt this have to do with the:
`IF(Today()<Date(Year(Today()),6,1),`
I'm trying to figure out what the 6 and the 1 represent..

Steve Molis
Looping back in on this, the end of July got crazy busy.  The DATE funtion basically coverts 3 Numbers into YYYY MM and DD and builds a Date Value out of them.

So
`DATE( YEAR(TODAY()), 6, 1)`
So DATE Is basically saying give me 3 numbers so's I can build me a Date.

YEAR( TODAY() ) is isaying Get the number of whatever Year today's Date is.

And TODAY() is saying "It's September 6, 2018, here's the number 2018.

Year says "Thanks" and hands the 2018 to DATE and DATE builds it's YYYY

The number 6 represents June (or MM 06)

The number 1 represents DD 01

So DATE( YEAR(TODAY()), 6, 1)  =  DATE( 2018 , 06 , 01 )  or YYYY/MM/DD = 2018/06/01

Steve Molis
btw -  When you say you're trying to return the Year they will graduate, do you mean a YYYY value like 2018?
Tom Hoffman
That formula was to calculate 'years to go,' So if you are in Grade 2 when you submit, you should have 10 more school years remaining.  School years is an important distinction, because in September 2018, 10 more years is 2028, but 10 more school years is 2029.

If you want the math to determine the year they will graduate vs the number of grades they need to complete, then we'll need to change things because:

2nd grader, Sept start has '11 more years' to finish
2nd grader, May start has '10 more years' to finish

So, I would change the <6-1-Year part of the formula to read:

'12th'=0
...
'Kindergarten'=12

and the > 6-1-Year part of the formula to read:

'12th'=1
'Kindergarent'=13

(by the way Angela, I completely forgot I helped you with this before the most recent 40 question/10 chatter/1 screenshare marathon! Did you get that data upload squared away, everything working on that build?)
Tom Hoffman
++Steve, now I have the slow fingers.
Angela Tapp
Happy Friday!

The following was missing in the original formula, so I added another IF statement..  of course, I'm now getting a syntax error

`and the > 6-1-Year part of the formula to read:`
IF(Today()<Date(Year(Today()),6,1),
'12th',0,
'11th',1,
'10th',2,
'9th',3,
'8th',4,
'7th',5,
'6th',6,
'5th',7,
'4th',8,
'3rd',9,
'2nd',10,
'1st',11,
'Kindergarten',12,
'Pre-K',13,
Null,
IF(Today()>Date(Year(Today()),6,1),
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
Null))

**** So I was thinking the "6" represented the 6th month?  Ie, I was thining that June 1 was the 'transition date'   - is that incorrect?
Steve Molis
No, you're correct

Breaking it down

TODAY() = 09/07/2018
YEAR(TODAY()) = 2018
DATE(YEAR(TODAY()), 06, 01) = 2018/06/01  ( in YYYY/MM/DD format)

Steve Molis
PS.  Just a personal prefernce I always UPPERCASE my Formula Functions and pad the months and days 1-9 with leading 0's
Tom Hoffman
Angela - the way that IF() statement works is as follows: IF( "less than June 1, CurrentYear" , "do THIS", "otherwise do THAT") Otherwise in this case means greater than May 31, CurrentYear @Steve - I try on the Uppercase, get lazy sometimes.
Steve Molis
Hi Guys,
I've been chewing on this for a while and I wonder if a (slightly) more simple approach like this might work

```IF( TODAY() < DATE(YEAR(TODAY()),06,01),
14 , 13 ) -
'12th',14,
'11th',13,
'10th',12,
'9th',11,
'8th',10,
'7th',9,
'6th',8,
'5th',7,
'4th',6,
'3rd',5,
'2nd',4,
'1st',3,
'Kindergarten',2,
'Pre-K',1,
0)```

PS.

This logic has a loophole, because 06/01/2018 is not < 06/01/2018 and it's also not > 06/01/2018 so anyone registered on 06/01 would fall through the cracks
```IF(Today()<Date(Year(Today()),6,1)...
IF(Today()>Date(Year(Today()),6,1),```

you need to say either
```IF(TODAY() < =DATE(YEAR(TODAY()),06,01)...
IF(TODAY() > DATE(YEAR(TODAY()),06,01)```
or
```IF(TODAY() < DATE(YEAR(TODAY()),06,01)...
IF(TODAY() >= DATE(YEAR(TODAY()),06,01)```

Angela Tapp
Update:
I tried Steve's formula and a 2nd grader (who is supposed to calculate to be a 1st grader) was computing to be a 10th grader so I did this:
```IF( TODAY() < DATE(YEAR(TODAY()),06,01),
14 , 13 ) -
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
0)```
This corrected that particular student.
But when I bounced it off one of my older ones that was 'working' (date submited would have been last year) the 5th grader became a 4th grader.

Still playing around with the info you gave me...

Angela Tapp
I'm trying to combine two IF statements, correct?

Angela Tapp
Here's the latest attempt with Tom's formula - I'm just not succeeding in adding the additional IF statement how to add include "Otherwise in this case means greater than May 31,"
```IF(TODAY()<DATE(YEAR(TODAY()),08,01),
'12th',0,
'11th',1,
'10th',2,
'9th',3,
'8th',4,
'7th',5,
'6th',6,
'5th',7,
'4th',8,
'3rd',9,
'2nd',10,
'1st',11,
'Kindergarten',12,
'Pre-K',13,
Null,
AND(
IF(TODAY()<DATE(YEAR(TODAY()),7,31),
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
Null)))))
```

Steve Molis
"greater than May 31..." would be
`TODAY() > DATE(YEAR(TODAY()),05,31)`
or
`TODAY() >= DATE(YEAR(TODAY()),06,01)`
What retul do you want the Formula to return in that case?

Steve Molis
Also Nested IF statements are Mutually Exclusive, if a record meets the criteria of the first IF, it is eliminated from all of the IF's after that.  If a record meets the criteria of the second IF, it is eliminated from all of the IF's after that, etc, etc...

`IF(TODAY()<DATE(YEAR(TODAY()),08,01)...`
will eliminate all of these record too
`IF(TODAY()<DATE(YEAR(TODAY()),7,31)`
Because July 31st is before August 1st

Think of it like you're assigning Letter Grades based on a Test Score.  You have a stack of tests to be graded, when you assign a grade the test it doesn't go back into the same pile, you put it aside in another pile and never have to look at it and Grade it again.

IF(Score >= 90, "A",
IF(Score >= 80, "B",
IF(Score >= 70, "C",
IF(Score >= 60, "D",
"F"))))

Anyone with a test score >= 90% gets an A and their tests are removed from the pile, the only ones left to be graded are the ones < 90%.

Angela Tapp
Thank you!  The reult is a student's "current grade" which ranges from Pre-k to 9th.

I think I'm understanding what you and Tom are saying - I just keep playing with the formula because I just can't get rid of the syntax error. I've either got too many partenthisis or not enough.
Angela Tapp
I've re-read your last post 4 times now.  I can't tell if I should stay the course or if I should start over..

Angela Tapp

Looking like the formula has to be rewritten - can't this just be simple addition?

Am I overthinking this?

We have a student who completed the online form.
We have captured date_submitted (2/14/17) and grade_submitted (4th) at that time

In the 'current grade' formula that I'm working in:

grade_submitted stays the same until July 1st of each year
then plus one for each year going forward

so in example above the student would be in the 6th grade
based on today's date grade_submitted plus two (because we've passed July 1st 2x since 2/14/2017)

seems like a converstion is necessary - convert the grades from 3rd, 4th, 5th to just 3, 4 and 5

Tom Hoffman
Angela, I think you are mixing up formulas now that I'm reading back through this whole thread.

*Steve, not sure your idea works - I tried it out, seems like seniors would return a negative number on one side of the equation, but im probably doing something wrong.

Record creation gives us:
A - Submitted Date

From that we will derive:
C - Years To Go (at submission only)

You wanted to know D & E, but we need C to figure that out.

Starting with C, Years to Go.  This formula was supposed to be used as a field update in a Process Builder, so 'Today()' would have worked, but I think you took it to a custom field and that is where things got strange.

I've updated it to use July 1 as its 'flip date' and to reference 'submitted date' so you can use it as a custom field or in a process builder field update (I still think it should be used in Process Builder because not sure our other formulas will compile...).

Years to Go =
IF(Date_Submitted__c()<Date(Year(Date_Submitted__c),7,1),
'12th',0,
'11th',1,
'10th',2,
'9th',3,
'8th',4,
'7th',5,
'6th',6,
'5th',7,
'4th',8,
'3rd',9,
'2nd',10,
'1st',11,
'Kindergarten',12,
'Pre-K',13,
0)),
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
0))

Here is how this formula works, based on current grade and submitted month, it returns the numbers below for 'Years to Go".  In the next formula, we are going to add YearsToGo to Date Submitted to find 'Graduation Year'

Text(Year(Date_Submitted__c)+YearsToGo)

So now that we know years to go at the time the form was submitted AND we know the graduation year, we can figure out the current grade. We can do this by subtracting Current Year from Grad Year, adjusting the results by one depending on if its before or after July.

IF(Today()<Date(Year(Today(),7,1),
Case((Year(Date_Submitted)+YearsToGo) -Year(Today()),
0, ‘12th’,
1, ‘11th’,
2, ‘10th’,
3, ‘9th’,
4, ‘8th’,
5, ‘7th’,
6, ‘6th’,
7, ‘5th’,
8, ‘4th’,
9, ‘3rd’,
10, ‘2nd’,
11, ‘1st’,
12, ‘Kindergarten’,
13, ‘Pre-K’,
Case((Year(DateValue(CreatedDate))+YearsToGo) -Year(Today()),
1, ‘12th’,
2, ‘11th’,
3, ‘10th’,
4, ‘9th’,
5, ‘8th’,
6, ‘7th’,
7, ‘6th’,
8, ‘5th’,
9, ‘4th’,
10, ‘3rd,’
11, ‘2nd’,
12, ‘1st’,
13, ‘Kindergarten’,
14, ‘Pre-K’,

@Steve, would love your thoughts on this one
Angela Tapp
Hi there,
Playing around with the new "C" field creation.. keep getting a syntax - an extra comma
trying to get rid of it

Tom Hoffman
Sorry, must have deleted the second Case() function...

Years to Go =
IF(Date_Submitted__c()<Date(Year(Date_Submitted__c),7,1),
'12th',0,
'11th',1,
'10th',2,
'9th',3,
'8th',4,
'7th',5,
'6th',6,
'5th',7,
'4th',8,
'3rd',9,
'2nd',10,
'1st',11,
'Kindergarten',12,
'Pre-K',13,
0)),
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
0))
Angela Tapp
I noticed that and tried to fix myself-still being picky
Tom Hoffman
Delete the second of the )) after the last value, 0, in the first case function
Angela Tapp
Spelling is verbatim, I pasted it
Tom Hoffman
That was a leftover from the today() function, didnt catch that in my editing.  Just delete the ()
Angela Tapp
sooo  close!  (this is "current grade")

Angela Tapp
Angela Tapp
I just knew you were doing that!
I'll keep at it
Tom Hoffman
Sorry, just typing this free hand...dont have a check syntax button to double check.

In any case - this is a great exercise in troublehshooting formulas, there are three obvious misses here on my part:

1) there is a missing ) very early in the formula
2) there is an extra ) in the same place as before
3) a field reference needs to be updated

Give it a shot on your own first, if you can't find them, then I'll give the answer ;)
Angela Tapp
Error: Invalid Data.
Review all error messages below to correct your data.
Compiled formula is too big to execute (5,082 characters). Maximum size is 5,000 characters (Related field: Formula)
Angela Tapp
Angela Tapp
FYI - it saved.  About to test it
```IF(Today()<Date(Year(Today()),7,1),
0, '12',
1, '11',
2, '10',
3, '9',
4, '8',
5, '7',
6, '6',
7, '5',
8, '4',
9, '3',
10, '2',
11, '1',
12, 'K',
13, 'PK',
1, '12',
2, '11',
3, '10',
4, '9',
5, '8',
6, '7',
7, '6',
8, '5',
9, '4',
10, '3',
11, '2',
12, '1',
13, 'K',
14, 'PK',

Tom Hoffman
Two things: 1.change the datevalue(createddate) to just your submitted date field 2. The compile error is why I recommended to use PB to set the value.
Angela Tapp
I'm totally up for the PB.. my head just can't think about how that would work .. its been a long weekend.  The above formula would require me to rebuild all my dashboards (which is fine), but I guess I will hold off and revisit the PB in the AM

Tom Hoffman
I think if you look at your error and my previous note on what to replace, you'll figure it out pretty quickly.
Angela Tapp
Got it.
My eyes are fried at this point!
Angela Tapp
Thanks Tom!  I realize the implications of shortening the grades and how that would screw up current PBs and rollups.  I'll check it out in the AM.  Have a good night.
Angela Tapp
Good Morning.
I'm a bit worried that if I step away too long from this I will have a hard time remembering where things were left so  I had a hard time sleeping.

Are you able to correct my thinking on the process builder build?

Object: Contact / start proces when record is created or edited

Criteria: Conditions are Met /Field = student record type

Action: Action Type/Update Records, Action Name "Change the characters of the Grade When Submitted, Record Type/Select Cotact record that started your process

Immediate Actions: Update records to meet all conditions
Filter the records you update based on these conditions:
Grade When Pledged - equals - picklist - 1st
Grade When Pledged - equals - picklist - 2nd
Grade When Pledged - equals - picklist - 3rd
Grade When Pledged - equals - picklist - 4th

Select new values for the records you update

Tom Hoffman
I'm not following.

Your PB should be populating the Years to Go field.

Criteria would be:

RecordType.Name = Student
Submitted Date is null boolean false

Check the box under advanced so it only fires the first time.

Field Update:
Years to Go =
IF([Contact].Date_Submitted__c<Date(Year([Contact].Date_Submitted__c),7,1),
'12th',0,
'11th',1,
'10th',2,
'9th',3,
'8th',4,
'7th',5,
'6th',6,
'5th',7,
'4th',8,
'3rd',9,
'2nd',10,
'1st',11,
'Kindergarten',12,
'Pre-K',13,
0),
'12th',1,
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
0))

If years to go is a stat field, the other fields should be able to be a formula without any compile issues.
Angela Tapp
Seriously been trying to do this without bothering you.
Years To Go is not showing up as an option in the field dropdown list

Tom Hoffman
You have to create it as a Number field (18, 0 so no decimals) to be able to update it from PB.
Angela Tapp
Sorry I don't understand
Years To Go is a custom formula field its output is a number field with no decimals..
Tom Hoffman
Years to Go has to be a free entry number field. You can't edit/update a formula field. Years to Go has be a regular number field so you can avoid compile limits in formulas that reference Years to Go. You can set it to read only on the page layout and/or remove edit permissions to that field on profiles so people do not edit it.
Angela Tapp
I found where I messed up and thought I got it all fixed..

Tom Hoffman
Your field update is supposed to be updating a number field called Years to Go.  Years to Go cannot be a formula field for all of this to work.

Once you have Years to Go as a number field, graduation year is a formula field with a text output using this formula: Text(Year(Pledge_Submitted_Date__c)+YearsToGo)

Then Current Year is a formula field like this:

IF(Today()<Date(Year(Today(),7,1),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo) -Year(Today()),
0, ‘12th’,
1, ‘11th’,
2, ‘10th’,
3, ‘9th’,
4, ‘8th’,
5, ‘7th’,
6, ‘6th’,
7, ‘5th’,
8, ‘4th’,
9, ‘3rd’,
10, ‘2nd’,
11, ‘1st’,
12, ‘Kindergarten’,
13, ‘Pre-K’,
Case((Year(Pledge_Submitted_Date__c)+YearsToGo) -Year(Today()),
1, ‘12th’,
2, ‘11th’,
3, ‘10th’,
4, ‘9th’,
5, ‘8th’,
6, ‘7th’,
7, ‘6th’,
8, ‘5th’,
9, ‘4th’,
10, ‘3rd,’
11, ‘2nd’,
12, ‘1st’,
13, ‘Kindergarten’,
14, ‘Pre-K’,
Angela Tapp

I did not have a "Current Year" field created or on the radar - I am now creatng a new field called "Current Year" and I'm attaching the formula mentioned above.

I had previously deleted but now have recreated:
Years to go- a number field

`formula: Text(Year(Pledge_Submitted_Date__c)+YearsToGo__c)`

Angela Tapp
"Submitted Date is null boolean false"

I don't get it, and I probably won't get it - but in the off chance this was an errror
- I have zero records where the submitted date would be null...

Tom Hoffman
Basically, when the pledge submitted date is populated, we want this PB to fire and figure out years to go based on that submitted date. So...we check to make sure its not null, if its not, then the PB criteria evaluates to true, and works through the actions.
Angela Tapp
Its absolutely hosed.  It was somewhat working.. now its not.
​Things have been changed so much I can't make sense of anything any more.

Here is the code for "Current Grade"
```IF(Today()<Date(Year(Today()),7,1),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()),
0, '12th',
1, '11th',
2, '10th',
3, '9th',
4, '8th',
5, '7th',
6, '6th',
7, '5th',
8, '4th',
9, '3rd',
10, '2nd',
11, '1st',
12, 'Kindergarten',
13, 'Pre-K',
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()),
1, '12th',
2, '11th',
3, '10th',
4, '9th',
5, '8th',
6, '7th',
7, '6th',
8, '5th',
9, '4th',
10, '3rd',
11, '2nd',
12, '1st',
13, 'Kindergarten',
14, 'Pre-K',
```

Here is a pic of this area on a student profile - all students are "Graduates"..

I've hosed the Student Pledge Status (which is connected to reports showing if schools and students are active or almost active). I simply can not save this formula which is verbatium for what was previously working (I had pasted original formula in the descirption):

Here is Years To Go

Angela Tapp
Here is the Update Current Year process builder

Tom Hoffman
It's not hosed - just need a field update & things will fall into place.

Years to Go - right now this field is blank for all existing records (remember, its a free entry number field), this is why everyone is 'graduated,' you'll need to export all ids and send them back via data loader (how I showed you on the call) to kick off that process builder.  Make sure you uncheck the box under advanced on the criteria side before doing this update, so that way it evaluates for all students.  I would also be sure to set your chunk settings to 50 or so to avoid any of your other automations causing a SOQL query limit.

Graduation Year - this looks good to go.

Current Grade - this looks good to go as well.

Student Pledge Status - this is a new one you are throwing at us, but the fix is pretty easy.  First, your error is because you are referencing a text field with a number.  Instead of 1, you would put '1st' and so on.

However, rather than reference the Current Grade text formula field (and risking running into compile limits) we can use the same formula for current grade and just change the output to be the status field you want (note, not sure you have all these status fields, just populated as an example)

IF(Today()<Date(Year(Today(),7,1),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo) -Year(Today()),
12, US_School__r.Kindergarten_Status__c,
13, ‘Pre-K’, US_School__r.Pre-Kindergarten_Status__c
Case((Year(Pledge_Submitted_Date__c)+YearsToGo) -Year(Today()),
13, US_School__r.Kindergarten_Status__c,
14, US_School__r.Pre-Kindergarten_Status__c,
Angela Tapp
It doesn't seem like I should use data loader until the Student Pledge Status is fixed?

I can't understand the last section.
This looks inconsistent with the rest of the formula:
`13, ‘Pre-K’, US_School__r.Pre-Kindergarten_Status__c`
But is adding Pre-K necessary?  I know its showing up but its not an option on the online picklist.

I don't know if I'm following.. but I tried this and got an error

```IF(Today()<Date(Year(Today(),7,1),
Case((Year(Pledge_Submitted_Date__c)+  YearsToGo__c) -Year(Today()),
12, US_School__r.Kindergarten_Status__c,
Case((Year(Pledge_Submitted_Date__c)+  YearsToGo__c) -Year(Today()),
13, US_School__r.Kindergarten_Status__c,

Tom Hoffman
I was just providing an example, remove/add status values as you need to.

A few things, your return type on this formula is set to Number, I believe it should be a text field since your status fields are text.

It also looks like its saying there is an error with a formula in your field, is YearsToGo__c the old formula field or your new, free entry number field?
Angela Tapp
So tempted to delete the field and recreate it.
But I know its tied to other areas (just can't remember)

Angela Tapp
Latest Update:
I deleted the Student Status field and recreated it with the new formula.
It is calcuating formula fields.
```Error: Invalid Data.
Review all error messages below to correct your data.
Compiled formula is too big to execute (7,203 characters). Maximum size is 5,000 characters (Related field: Formula)```

Angela Tapp
Tom Hoffman
Ok...we are mixing up too many things here.

First, your error message on Student Pledge Status suggests Student Pledge Status is being used somewhere else in the SF org and that field is not able to compile any more.

What is the field referencing that field?  Is knowing the status of that students grade at their school important at the student level for the business requirements? If not, I would move on from it - its easily available by looking at the school record, which means its easily available in reporting.

Your DLRS error looks like its saying, you changed Current Grade to output in some non-string format (like a number), is it still a text field?

Angela Tapp
Hi, The answer is yes to both questions.
Angela Tapp
On Student Status, I removed K-5th from the formula (below) and was able to avoid the compile error and all my dashboards came back to life.

I started to wonder if 'Graduated' was removed (since I don't need that report) - maybe I can add a few more grades back into the formula.

IF(Today()<Date(Year(Today()),7,1),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()),
Angela Tapp
Regarding the DLRS error - I received it when I tried to fix a School Name on a Contact Record.

I was emailed the following APEX email:
```Apex script unhandled trigger exception by user/organization: 005f2000008y2gu/00Df2000001Kv2H

dlrs_ContactTrigger: execution of AfterUpdate

caused by: System.QueryException: value of filter criterion for field 'Current_Grade__c' must be of type string and should be enclosed in quotes

Class.dlrs.LREngine.QueryExecutor.query: line 662, column 1
Class.dlrs.LREngine.WithoutSharingQueryExecutor.query: line 680, column 1
Class.dlrs.LREngine.rollUp: line 238, column 1
Class.dlrs.LREngine.rollUp: line 117, column 1
Class.dlrs.RollupService.updateMasterRollupsTrigger: line 1035, column 1
Class.dlrs.RollupService.handleRollups: line 892, column 1
Class.dlrs.RollupService.triggerHandler: line 311, column 1
Trigger.dlrs_ContactTrigger: line 7, column 1```

Angela Tapp
Also - Reporting on the status of grades 9-12 are not part of the business requirements.
Thanks
Angela Tapp
I am scheduled to do a demo of the org today.. I'm not sure what to do.. I can't tell if this is fixable...?

Tom Hoffman
Did you check the filter criteria on your DLRS rollup? It is saying you have to wrap the filter criteria with ' ' if nots already.
Angela Tapp
Yes, I tried that
Angela Tapp
who, hang on..  I put the ' ' in just the 6
Angela Tapp

That edit allowed me to save the DLRS and presented a great deal  of hope.
I was previously unable to do anything at all.

I surrounded the single quote to all the grades and ran 'calculate' on all of them.

The inital error is still happening - it happens when I go into a student contact record and I change the school name (which is a lookup field).

Tom Hoffman
You have to update all of your DLRS records. Your current grade formula outputs like this '6th' '7th', etc not 6 or 7, so be sure to update them all appropriately.
Angela Tapp
I ran 'calculate' on all of them - not sure how long that takes.
But need to make sure you aren't saying I need to update all contacts via data loader?
Tom Hoffman
I was saying you need to update your DLRS records to use the correct syntax of "6th", "7th", "8th" etc; they can't be just numbers with " " and they can be just numbers with " " like "6", "7", because Current Grade outputs with "1st", "2nd", "3rd", etc.
Angela Tapp

k, I think you meant to say "and they can't be just number with... "
Does this look correct?

Tom Hoffman
Yup, looks good, if you do that for all of them, should work and not be any errors.
Angela Tapp
k.. thx!
it looks like it reverts back even if I thought I saved.. seems I have to deactivate first
Angela Tapp

Thought you could use this in your library.
Angela Tapp
It looks beautiful, Tom.  Thank you for sticking it out with me.
My grade counts are off, I still have a complile issue - so many students are showing up as 'graduated'.

Below is Student Pledge Status.  It feeds into active vs. almost active.  I had to take out K-5th grade to get it working.  If I add any more lines, I get a too many characters error message.
Can you remind me what your suggestion was to fix this?
```IF(Today()<Date(Year(Today()),7,1),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()),

Tom Hoffman
Last two pointers - #1 All of those students appear as graduated because they need to be run through that process builder first (you need export all students where Current Grade = graduated, just their contact IDs, then update those records, only map the contact Id and it will trigger the Process Builder. Again, I would set your batch size down to like 25 or 50 for this update, there are a lot of related things happening and that will help you avoid collision detection & soql query limits. #2 Grade status -If you were using lightning it would be simple, we would use a quick action & related record component to display those values, but from what I saw, they were still on classic. If it really has to be there, you'll have to push the status from a PB on the school object down to free text entry fields on the related student records instead of using a formula. Your scheduled processes that kick off each night should update all the school records, which in turn should cause your PB to update the students.
Angela Tapp
1.) Yes, I did this yesterday.. I'll rerun it again now.
2.) I'm spending about 90% of my time in Lightning...

Tom Hoffman
Make sure #1 that your criteria is set to appropriately evaluate - you have want to clone a new version temporarily that has no criteria for firing, run your update, then deactivate that vers, reverting to the prior.

For #2, just follow the directons here.  You can create a quick action to display all of the school grade statuses or make a conditional component for each grade so the right one appears for each student.

Angela Tapp
Is there a knowledge base article on number #1 that I can read?  I'm not understanding...
Angela Tapp
Tom, do you see any issues in using this code as a workaround to item #2?  It compiles.

```Case(((Year(Pledge_Submitted_Date__c)+YearsToGo__c) - Year(Today())) +
IF(Today()<Date(Year(Today()),7,1), 1, 0),
13, US_School__r.Kindergarten_Status__c,

Angela Tapp
ugg.
"you need export all students where Current Grade = graduated, just their contact IDs, then update those records, only map the contact Id and it will trigger the Process Builder. Again, I would set your batch size down to like 25 or 50 for this update, there are a lot of related things happening and that will help you avoid collision detection & soql query limits."

I can't fix.

I have students who had an original grade of 8th last year showing up as 10th graders.  I exported all contacts; deactivated all PBs, uploaded just the ID match via data loader.

I don't know what you are asking me to do.

"Make sure #1 that your criteria is set to appropriately evaluate - you have want to clone a new version temporarily that has no criteria for firing, run your update, then deactivate that vers, reverting to the prior."

Which PB are  you talking about?  All of theme?

Please tell me if you want me to post this to another message board.
Thanks
Tom Hoffman
Deactive the current PB that sets years to go, clone it. Change the criteria for the Years to Go formula to 'No Criteria' for now & activate it. Run your update so all records will populate the years to go based on the form submitted date. This should correct the records since Submitted Date is static and Grade when Submitted is static as well. Once you've done that, deactivate the new vers of the PB with 'No Criteria' and activate the previous version that has criteria.
Angela Tapp
1. DONE Deactive the current PB that sets years to go, clone it.
2. DONE (Assuming the 'No Criteria' option is in the second step of the PB where one would "Define Criteria for this Action Group") Change the criteria for the Years to Go formula to 'No Criteria' for now & activate it.
3. DONE (Assuming "& activate it" is the same as "run your update".  If "Run your update" means upload all contacts via Data Loader, matching only the ID field, I've done that 2x in the past 48 hours) Run your update so all records will populate the years to go based on the form submitted date.
Not sure how long PB takes to fully process

Tom Hoffman
A pb only fires when records are created or updated. Need to redo your update
Angela Tapp
Will do that now.
I did test update a record and it still showed 2 grade years ahead instead of one.
Angela Tapp
Updated all records; no change.
Angela Tapp
Trying to figure out the prob.
I had to remove 12th because of too many characters (and its not part of the business requiements)
Do you think that would cause the glitch?

```IF(Pledge_Submitted_Date__c<Date(Year(Pledge_Submitted_Date__c),7,1),
'11th',1,
'10th',2,
'9th',3,
'8th',4,
'7th',5,
'6th',6,
'5th',7,
'4th',8,
'3rd',9,
'2nd',10,
'1st',11,
'Kindergarten',12,
'Pre-K',13,
0),
'11th',2,
'10th',3,
'9th',4,
'8th',5,
'7th',6,
'6th',7,
'5th',8,
'4th',9,
'3rd',10,
'2nd',11,
'1st',12,
'Kindergarten',13,
'Pre-K',14,
0))```

Tom Hoffman
Attached the wrong video in my previous post.

Ang - really reached the limits of what I can do to help with advice and troubleshooting; you'll need to use all the messages and notes to put this together, I assure you its all here.

If you find you can't get it working, I think hiring someone to assist would be a good idea.

I have attached a video with everything working as designed; you can see that as long as my submitted date & grade when submitted are entered correctly, all my other fields (years to go, current grade, graduation year) all update without issue.  If you find your numbers are off, it has to do with the data being input in those two fields.

Best of luck!

https://failblazer-dev-ed.my.salesforce.com/sfc/p/61000000XlPw/a/61000000g1Pf/lwMQSdNAmQ_9EyVPwHaxe03UROwyXIo9ghOcEjKQezs
Angela Tapp
I understand.
Thank you for telling me.
Angela Tapp
In the off chance that someone else is following along - I'm trying to salvage a huge time investment.

The formula below references YEAR.
A custom field in the above references something called "Graduation Year".

Here is what Tom said: "So now that we know years to go at the time the form was submitted AND we know the graduation year, we can figure out the current grade. We can do this by subtracting Current Year from Grad Year, adjusting the results by one depending on if its before or after July.

I'm obviously not an expert on formulas - but below is the Current Grade formula - Is there zero possibility that the use of YEAR (as system formula) is being used interchangablly with the custom field above called "Grad Year"?

```IF(Today()<Date(Year(Today()),7,1),
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()),
0, '12th',
1, '11th',
2, '10th',
3, '9th',
4, '8th',
5, '7th',
6, '6th',
7, '5th',
8, '4th',
9, '3rd',
10, '2nd',
11, '1st',
12, 'Kindergarten',
13, 'Pre-K',
Case((Year(Pledge_Submitted_Date__c)+YearsToGo__c) -Year(Today()),
1, '12th',
2, '11th',
3, '10th',
4, '9th',
5, '8th',
6, '7th',
7, '6th',
8, '5th',
9, '4th',
10, '3rd',
11, '2nd',
12, '1st',
13, 'Kindergarten',
14, 'Pre-K',