Faulty (?) Calculation for Dwindling Value List

[innodat]

The idea is that IF a field has content, its name does no longer appear in a value list. For example, if the field "Organization" contains a value, the string "Organization" needs to be omitted from the choices in the value list.

Here's the stored calculation which I'm using to create this dwindling list:

Substitute(



"Location¶Insured By¶Main Artist¶Organizer¶Curator¶Other¶";



FilterValues (

"Location¶Insured By¶Main Artist¶Organizer¶Curator¶Other¶";



Case(IsEmpty(Location); ""; "Location¶") & 

Case(IsEmpty(Insured By); ""; "Insured By¶") &

Case(IsEmpty(Main Artist); ""; "Main Artist¶") & 

Case(IsEmpty(Organizer); ""; "Organizer¶") & 

Case(IsEmpty(Curator); ""; "Curator¶") );



"")

And it's not working... It pretty much shows me all values regardless, here and there one (seemingly random) is omitted. I've been trying for hours, just don't see my mistake. Does anyone see why it's not working?

Thanks so much!!

[comment]

The idea is that IF a field has content, its name does no longer appear in a value list.

If the value list is defined to use values from a field, it will show ALL values in the field, across ALL records. I am guessing the "seemingly random" values have been used in all your records.

[innodat]

If the value list is defined to use values from a field, it will show ALL values in the field, across ALL records. I am guessing the "seemingly random" values have been used in all your records.

THANK YOU!

I did define a relationship (Record ID) to evaluate this calculation in the context of the current record only - but I wasn't using it!

Many thanks again, much appreciated.

[comment]

I've only now looked at your actual calc. I don't think that's going to work - unless you always select a block of adjacent values.

[innodat]

I've only now looked at your actual calc. I don't think that's going to work - unless you always select a block of adjacent values.

Yes I can confirm that, I've adjusted the calculation to be executed in the context of the current record only, and it's still not working consistently. The fields contain values randomly, not in a particular sequence.

What kind of calculation would you create to accomplish this?

Thanks!

[comment]

Alas, there's no nifty function to do the opposite of FilterValues(). You need to substitute out each used value individually - either explicitly, or by using a recursive custom function. See:

http://fmforums.com/forum/topic/52332-speedy-value-filtering-may-07/page__p__245860

However, I am not sure where you're going with this, as it looks like you have a dedicated field for each type of value - so where exactly will this value list be used?

[comment]

BTW, the hardest problem in problem-solving is to look at things from a different perspective:

EmptyFieldsVL.zip

I still don't see where this would be useful, though.

[innodat]

BTW, the hardest problem in problem-solving is to look at things from a different perspective:

EmptyFieldsVL.zip

I still don't see where this would be useful, though.

Very generous of you! Your approach seems to be working indeed - and I'm currently trying to follow it and understand why... :-)

It's hard to explain... it's an advanced user interface function of sorts. The user can choose a value, which then triggers a scripted action. He's only allowed to execute these actions for not populated fields. I've attached a video of it. I can't disclose the entire file right now.

Progress Window.mov

[comment]

It seems like you should be using a join table of Roles, instead of dedicated fields. Then it can be a "standard" dwindling value list of roles yet unassigned.

[innodat]

It seems like you should be using a join table of Roles, instead of dedicated fields. Then it can be a "standard" dwindling value list of roles yet unassigned.

I will certainly look into this. It's a large solution though, nearly complete. Therefore it's probably easier to work with your code sample, and do it "right" the next time around.