1 of 1 people found this helpful
The other option is to use a script trigger when a new DrawingRevision is made that updates the corresponding value in the Drawing table. In this way the field in Drawing is a static, indexable field, and no longer dependent on a related table.
This sounds like a great idea and is just the sort of thing I was hoping to achieve. I'm going to give this a try and see how it goes.
Works like a charm. I actually discovered that I'd already created a script to do just what you suggested, but found out that in my script that does the find for the user was finding on a different field. That was really the essence of the problem. But your idea (when fully implemented) works well.
I also create a second utility script to loop through all records, setting the static indexable field to the latest_revision value.
And the other script is triggered on Save, that updates the static field whenever a drawing gets a new revision letter.
I have a "filter-as-you-type" field for searching on that Revision Letter and before I made the change, it couldn't keep up with typing. Now it's very fast.
Sounds like you have a good set-up.