I have the ff:
Product object with multi select picklist State. Payment object with string State.
Payment will have a lookup relationship to
Product but I need to filter products so that it will only show those that are in the same state as payment's state.
I found out that lookup filter doesn't allow multi select. (https://help.salesforce.com/articleView?id=fields_lookup_limitations.htm&type=5)
I updated Product's state to an object and a junction relationship was created between Products and State. (Products per state)
I was hoping I could add something like this in the Payment to Product look up filter: Payment > State EQUALS to Product > Products Per State > State > Name but that's not possible as well.
I thought of creating a field in Products called
All_States and use a trigger so that every time Products_Per_State was created/updated/deleted it will update the related Product's All_State field. (I haven't done triggers but I am hoping that is possible. I couldnt use formula for All_States because that isn't allowed in lookup filter as well.)
With that I can do this in lookup filter: Product > All_States contains Payment > State
I'm not a 100% confident with this solution. If anyone has any suggestions, I'd appreciate it. Thanks.