Conditional Value List with Self-Clearing Fields

[Vaughan]

I've always loved conditional value lists, but I've been bothered that it's easy for data errors to be made:

1) select an entry from the first menu

2) select an option form the second menu (that's related to the first)

3) change the first menu's entry

This will leave the record with an entry in the second menu that is not related to the first menu.

There are ways to avoid this, but (until now) all have required modal interface designs or event plugins to run scripts after fields have been entered, or both.

No more.

This technique uses the auto-enter calculation feature of FMP 7+ and combines it with the Evaluate() function. Evaluate has the option of trigger fields.

When the first field is changed, the auto-enter in the *second* field is triggered. It checks whether the field value is in the value list and clears the field if it is not.

No scripts, no plugins.

I'm just designing a solution that has 3 conditional value lists, and this technique ensures that data can be correctly entered and accommodate later edits without the need for scripts or plug-ins.

All the magic happens with the auto-enter calculation on the Media ID field in the Clear Fields table.

Share and Enjoy.

Clear_Fields.fp7.zip

[comment]

There are ways to avoid this, but (until now) all have required modal interface designs or event plugins to run scripts after fields have been entered, or both.

Ahem... Link

[Vaughan]

D'Oh!

So just referencing the first field in the Let() statement causes it to be a trigger field? (even though nothing is done with it!)

I like it... the Evaluate() function meant that the calc had to be in literal text, requiring all the quote characters to be escaped (if that's the right word).

I was expecting "my" method to be improved upon by others on the Forum... :yay:

[comment]

A reference is reference. The optional triggering mechanism of Evaluate() should not be used for its own sake - it's there BECAUSE the expression parameter must be literal text, therefore it is not referencing the required fields.

[CDiez]

Hello Vaughan,

Your "clear fields" solution solves a problem I have been wrestling with, so thank you for contributing it! However, I am dealing with 3-level hierarchies (e.g., category > type > subtype). I have been having difficulty adding a 3rd level to your solution.

Do you happen to have a version of it set up for 3 levels that you wouldn't mind sharing..?

Many thanks..

Carlos

[Ocean West]

Carlos It think this is what you are after

http://www.filemakermagazine.com/videos/hierarchical-value-lists-dependent-value-lists.html

[Lee Smith]

Hi Carlos,

Please do not ask the same question in different topics.

Had you not received a reply here, I would have deleted this one.

Lee

[CDiez]

Please accept my apologies.

[CDiez]

Thanks. I had already looked at that some months back and it didn't fully address the problem. Can't remember why (and I'm no longer subscribed so I can't view it again).

I think I almost have it though. Just working through one last snag.

I appreciate the reply...

[CDiez]

comment:

I feel bad that I had not realized you had already suggested a solution some months back when I first started wrestling with this. Sorry! I had to put the project aside and now I'm on it again, and I've been looking at the solution you shared then ("condVL2+clear2").

As I mentioned, my solution requires 3-level value lists, and I've been having difficulty modifying your solution to add the 3rd level. I was hoping you would be kind enough to take a look at what I've done (attached). I've been at it for hours with no success, but I'm sure you'll spot the problem in a minute. I'd be MOST grateful.

Notice the problem when selecting the "subtype" (3rd level) in the table layout: it shows all the subtypes associated with the top level ("category"), instead of only the ones associated with the combination of "category" and "type" (my 2nd level). Not sure if the snag is in the relationship or in the auto-calc field. You need to look at the "retail" category to see what I mean.

Also, when "retail" is selected (but NOT when other category values are selected), the subtype field is cleared as soon as I select a subtype value. Can't figure out why that is happening!

I feel this is THE solution if I could just get past these two snags...

THANKS AGAIN!!....

Carlos

condVL2_clear2_REVISED.fp7.zip

[CDiez]

comment,

I stumbled upon the solution, and thought I would post it here, in case anyone else has the same difficulty I had. I believe I had the wrong syntax in the auto-calc field for the 3rd level: it was pointing to the wrong TO. Now your "condVL2+clear2" solution seems to work exactly the way I need it to, so if you don't mind, I will borrow it for my properties project. Initially, I will use it to allow users to select the appropriate zoning district, based on the governing municipality, which is selected from a list based on the county selected. Also for property categories, types and subtypes (a few of which are shown in the attached).

Many thanks for sharing your solution!!

Cheers,

Carlos

condVL2_clear2_REVISED.fp7.zip

[HarryGlos]

Hey Vaughan,

This is very well done and thought out. It’s straight forward and incredibly useful. I know you said you were designing a solution that led you in this direction. But what made you decide on this specific method. Trial and error or just walked right into it?

I know how you can get great inspiration from, ah shall we say Sheila!

Good work Vaughn,

Harry

[LaRetta]

Hey, Harry? You wanna re-read this thread? The rockin' approach came from Comment!

[HarryGlos]

Hey LaRetta,

I have no "Comment" on that!

Harry

Edited February 4, 2009 by Guest

[polarpro]

Thanks a lot for this great file...

I played with it and added a Tertiary Value. (Similar to CDiez' solution, as I've just realized...)

There is only one problem that I cannot solve: I cannot get cleared the third field properly if there are two Primary Values that use a Secondary Value with the same name. To make it clearer, I added MOUNTAIN RANGES both under AMERICA and EUROPE. If I for example choose EUROPE - MOUNTAIN RANGES - Alps and then click on AMERICA I still see Alps in the third field. The attached file hopefully helps making clearer what I mean.

(Background info: Though in this case the example seems a little odd, there is a real situation behind it: The library system I work with has different SECTIONS (= Primary Values), CATEGORIES (=Secondary Values) and SUBCATEGORIES (=Tertiary Values). Every section (e.g. Arts, Politics, Geography) has a Category named General.)

Any ideas?

Mike

condVL2_clear2_THIRD_VALUE.fp7.zip

[comment]

Try it this way?

condVL3_clear.fp7.zip

[polarpro]

Hey comment,

Thank you very much for your fast answer, and for working on my file. It now works excellently! I now see where I went wrong...

Thanks again!

Mike

[Achineche]

Dear all,

Trying to work with the last file attached (condVL3+clear), just trying , I wonder if, could this work if the secondary selection is data from a related record?

Thanks for your help.