function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
John StamersJohn Stamers 

Lookup based on a Formula

Hey all,

 

I'm working on an application for enrolling volunteers in clinical trials. Each trial has specific criteria, age, BMI, etc. that must be met by the volunteer to be part of the trial. What I'm trying to do is ensure that a volunteer object meets the specific criteria before being able to be added to the trial object. 

 

I have created a dropdown list referencing all trials on the volunteer record and have played around with filters for some of the criteria. However, certian criteria are based on formulas and I have not found a way to bring these values into the lookup filter. From what i've read it doesn't look likea  lookup filter can interact with a formula field.

 

Is there some way to create a "dummy" number field that will simply hold the same value as the field populated by my formula?

 

Any help would be much appreciated.

 

Best Answer chosen by Admin (Salesforce Developers) 
Jeff TalbotJeff Talbot

Formulas technically can't produce a result while a record is being edited, and changing the value of a lookup field requires the record to be edited. This is why you can't reference formula fields in lookup filters.

 

You can create workflow that will copy a formula value to a text or number field. However, that can still be problematic because you'll have to save the volunteer record first without a value in clinical trial lookup field, then edit the record and add the lookup field value. Meanwhile if while editing the volunteer record, you make other changes, you could end up allowing or disallowing a value in the lookup field that you didn't want.

 

Consider creating a third custom object - "Volunteer Trials" -- a child of the clinical trial and the volunteer. This would provide a way to do the validations you desire, and it lets you associate a volunteer with multiple clinical trials if that situation comes up.

 

 

All Answers

Jeff TalbotJeff Talbot

Formulas technically can't produce a result while a record is being edited, and changing the value of a lookup field requires the record to be edited. This is why you can't reference formula fields in lookup filters.

 

You can create workflow that will copy a formula value to a text or number field. However, that can still be problematic because you'll have to save the volunteer record first without a value in clinical trial lookup field, then edit the record and add the lookup field value. Meanwhile if while editing the volunteer record, you make other changes, you could end up allowing or disallowing a value in the lookup field that you didn't want.

 

Consider creating a third custom object - "Volunteer Trials" -- a child of the clinical trial and the volunteer. This would provide a way to do the validations you desire, and it lets you associate a volunteer with multiple clinical trials if that situation comes up.

 

 

This was selected as the best answer
John StamersJohn Stamers

Creating the third object was definitely the way to go. Thanks for the help!