FilterValuesSUB - what about a new function ?
I think a new function FilterValuesSUB (or any other more fitting name…) would be very useful in FMP that does the following: given the 2 inputs textToFilter and filterValues, as in the existing FilterValues function, the result should however be a list that includes all textToFilter values that are NOT in the filterValues list.
Example: textToFilter = (A¶D¶E¶C¶Z), filterValues = (A¶B¶C), FilterValuesSUB (textToFilter; filterValues) returns (D¶E¶Z)
This would allow to evaluate keyword lists as if they were sets. The existing FilterValues operates as an AND operator, chaining two lists basically implements an OR, and the new FilterValuesSUB would complete the set of functions, and allow to implement also a NOT operator when textToFilter is the “keyword universe”.
Why so useful ? See this example: I have the problem to evaluate in the fastest possible way the list of data base keys that are not yet used out of a finite universal set of possible keys. Keys out of this universe are randomly allocated and possibly removed, leaving holes that eventually must be reused. Using the missing function I could get what I need by simply evaluating FilterValuesSUB (keys_universe; List (database::key)), where keys_universe is a text field containing a predefined list, and database::key is the field where unique keys are stored.
Are there other solutions ? Yes, many.
But so far all solutions I found are at best based on related ad hoc tables, with operations that however fast they are, become slower and slower the higher the number of keys in the keys_universe. I expect that any solution based only on variables (and this is made possible only by the new function, as far as I know) would be much faster than solutions based on larger and larger tables. A possible workaround would also be to emulate this new function with a custom function built with a loop on variables and fields... but why not a neat and efficient new function in FMP ?
Thanks for any comment on this