Conditional value lists and missing values

[A.J. G]

I have a table with several fields, each of which is a sub-category of the other. Here's a simple example of these types of fields:

Country, State, City

I've successfully created several conditional value lists, each of which pulls its values from related records in this table.

So, when the user selects a Country from a drop-down list, the next field's value list is filtered to include only related values (i.e, only the states that exist in that country). When the user next selects a value from the State drop-down list, the City field filters its values the same way, only showing relevant cities.

In this way, I've used a single table and implemented several self-joins. This allows the user to drill down into the table and prevents them from selecting incorrect values (like a city that doesn't exist in a country).

Here's the problem:

Some countries do not have states, so the value for this field in the source table is blank. So, when entering data into a layout, the user would put nothing in the State field. FileMaker will not do a self-join on blank fields, so it won't produce a list of cities.

I need to find a way to convince FileMaker to do a self-join on the blank values. In other words, I want the absence of a value to have meaning to FileMaker. When a user fills in the Country field, but leaves the State field blank, this should trigger FileMaker to select all Cities that are associated with the Country, but that do not have an associated state.

Can anyone show me how to accomplish this? I appreciate your help.

[Genx]

you could have state auto-enter to a value like "other" or "unavailable" in the case of those particular countries...

~Genx

[A.J. G]

Thanks for your reply.

You're correct; I could set State as an auto-enter field. However, I neglected to mention that it's important that this field be empty. I have several other calculated fields that check for the presence of State, so if no state applies to a particular country, the field needs to have no value.

I guess another way around this would be to initially put in a placeholder value for State, and then later erase that value. But I'm not really sure how that could be automated in a way that would require no action from the user.

[Genx]

you could put a place holder just by using an auto enter for that field of "---- Please Select State ----" and your calculations could check for this phrase as opposed to doing an IsEmpty() check which is what im assuming your doing now. Then you could also do some validation checking for the state field that checks if the currently selected country has states, if it does then state cannot be equal to "---- Please Select State ----"

~Genx

[comment]

You could do it this way (attached). Replace "n/a" with a space to make it transparent to users.

condVL3-optionalMiddle2.fp7.zip

[A.J. G]

Thanks for your example.

I have tried using spaces, and while this does make things transparent for the user, it does not leave the fields empty. I've got quite a few other calculated fields and scripts that use the IsEmpty function to test these fields.

If there is a way I could leave those fields empty, then that will save a lot of work. Otherwise, I'll have to test for the presence of a space in addition to running the IsEmpty function.

Thanks for any suggestions.

[comment]

Which field, in which table, are we talking about? If you mean the field where the secondary value is being selected - it ends up empty after selecting tertiary. If you mean the other table, where values are kept - it cannot be empty, because it's used in the relationship that shows tertiary values.

[A.J. G]

Yes, I am talking about the field that receives values from the secondary drop-down list. I see now that you've set it up as a calculated field that checks its own contents. I missed that in my first run-through. I apologize for the confusion.

I believe I can implement this in my own solution.

Thanks very much for your help!

Edited February 23, 2006 by Guest