Lookup relationship filter by related object's junction relationship

Question

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.

Answer 1

Your information of MD objects is not relevant

Anyways you can use LookUp filter for your use case.

While setting up the lookup filter you will get option to select current record or lookup and the field comparison between value/field.