AnsweredAssumed Answered

How to filter a value list?

Question asked by disabled_morkus on Nov 1, 2015
Latest reply on Nov 2, 2015 by disabled_morkus

I know this question has been asked and answered many times, but I think my situation is a little different since I'm not trying to filter successive values lists, for example




I have a value list I'm using (a list of categories) that will, when each category is checked, will populate a M:M resolver table for VENDORS:CATEGORIES. This all works well (thanks to that poster for the suggestions).


However, I now realize that due to all the categories after data entry of those, I would like the category value list to only show the categories that have not been picked yet. 


However, after a few hours of trying to create a FM relationship and mucking around with the value list configuration, I can only come up with two ways FM wants to display the value lists: (1) all values regardless of which I've picked (what works now), or (2) just the values I've already picked.


I've tried creating a "<>" join using a TO called CATEGORY_NOT_CHOSEN which is joined to CATEGORY on the PK. Then, I tried using that TO in the value list. Tried lots of things like this including the value list option of only using related values, etc.


But I can't seem to figure out how to get the values in the value list I haven't yet picked.


In SQL this is a 10 second query. To be able to build a Value List using a SQL statement would warrant an immediate upgrade to that FM version!


(select cat_item from category where cat_item.PK not in (select cat_pk from ....)




So, I have three tables:




VENDOR_CATEGORY (M:M resolver table




I'm not sure why this is so difficult. It's actually probably simple and I'm missing it.


Would appreciate any suggestions.