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!
