Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

strange behavior using a lookup in a DATEADD formula

Topic Labels: Formulas
Solved
Jump to Solution
1236 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Eli_Kent
7 - App Architect
7 - App Architect

Hi all, I figured out an easy workaround, but trying to understand why this is happening.

I have three table that matter to this scenario - Employee, Certificate, and EmployeeCertificate (junction table)

The Certificate table has a number field {Period of Validity} that records the period of validity of each certificate in months.

The EmployeeCertificate table has the following fields:

What is happening is that I have a lookup field in my EmployeeCertificate that looks up a number field in my Certificate table. The field it looks up is called

EmployeeCertificate is a junction table. It has one linked record field to my Employee table and another to the Certificate 

  • Employee (Linked Record)
  • Certificate (Linked Record)
  • Date of Certification (Date)
  • Period of Validity (Lookup from Certificate table)
  • Expiration Date (formula)

The Expiration Date formula is DATEADD({Date of Certification}, {Period of Validity}, 'M')

No matter the number in {Period of Validity}, that formula returns the same date as {Date of Certification}. In other words, it treats {Period of Validity} as 0.

So I created a new formula field called {Period of Validity formula}, with the formula {Period of Validity}, which, of course showed the same number as {Period of Validity}, and I rewrote the formula in question to reference the formula field {Period of Validity formula} field instead of the lookup field {Period of Validity}. Still no change.

Then I changed the formula in {Period of Validity formula} to read {Period of Validity}/1, and voila, {Expiration Date} was now showing the result I wanted and expected (in this case, {Date of Certification} + 12 months).

So my question is, what the heck is going on here?

Thank you!

1 Solution

Accepted Solutions
Stephen_Orr1
10 - Mercury
10 - Mercury

Lookup field is coming over as a string and the DATEADD formula is expecting an integer. When you divide by 1, you are changing the data type of {Period of Validity} to an integer.

See Solution in Thread

2 Replies 2
Stephen_Orr1
10 - Mercury
10 - Mercury

Lookup field is coming over as a string and the DATEADD formula is expecting an integer. When you divide by 1, you are changing the data type of {Period of Validity} to an integer.

Oh OK. I see. Thanks!. just changed the  Lookup field to a Rollup field with an AVERAGE aggregation and it works fine.