Help

strange behavior using a lookup in a DATEADD formula

Topic Labels: Formulas
Solved
Jump to Solution
520 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.