Conditional Value Lists Not Updating

[KristiB]

Hello,

I'm a pretty new user and I'm sorry if this question is asked all of the time, but I couldn't find the exact thing when I searched.

In simple terms, I have two fields on a layout with pop-up value lists. The second field's value list should be populated based on the first field's value. So if the user chooses "Dinner" in the Meal field, the Menu field should come up with "Chicken" and "Beef" as choices. Since the Meal field isn't conditional, it's just a static value list. The Menu field's value list is sourced from another field that contains a calculation formula based on the value of Meal.

Question 1: The problem is that when a record is created (or edited), and you make a selection from Meal, the Menu field is blank and no pop-up appears. However, it DOES appear if you "click off" into the white space of the layout, then click back in to edit. Shouldn't it update right away without having to do this?

Question 2: When you have conditional value lists like this, how does that work with Find mode? I would want the user to be able to find on any of the possible Menu choices, but only certain ones seem to appear because of the calculation. Is there a better way to make this work?

Sorry this is so long. Thank you for your time.

Kristi

[SlimJim]

Qu 1. The problem is connected with committing records, which is what clicking white space does. I think it may be because you are taking a value list from a calculated field which cannot update until you have committed the meal choice. Only when that field is updated does your second pop-up update. You may have to look at a different way of constructing the conditional value list.

Qu 2. You have already discovered the problem with a find. If you want to find from all possible menu choices then you will need the menu field on the layout with a value list of all possible choices.

There are ways of constructing conditional value lists which do not require the use of calculated fields after you have made your first choice. The most direct, but time consuming method, is to have a table of Meal, Menu choices. (You have to have a record for each possible combination of Meal-Menu) You then relate two instances of this table to your main table. The first by Meal only, the second by Meal and Menu. Then your value list for Meal is the field values of Meal via the first relation and the value list for menu is the field values of Menu (related records only) via the second relationship. There are a number of examples of this on this forum, search for conditional value list. The advantage of this is that if you change your meal-menu combinations you simply add/remove records in the Meal-Menu table and the rest is automatic.

However, this does not solve the second problem you will still have to have a menu field with the full (not just related) value list.

[KristiB]

Thank you for your help! At least I understand what's going on, now. I might just scrap the conditional thing altogether, until I feel like putting more time into it.

[LaRetta]

...However, it DOES appear if you "click off" into the white space of the layout, then click back in to edit.

Attach a script directly to your Meal popup. When User clicks popup, script will fire and Commit Record/Request (but will pause for the User to make a selection). We Windows users have SOME benefit here. You need to use popup menu and not popup list. Macs don't work well with scripted popup menus, sorry. If Mac, you CAN make this xplat by adding a popup list and testing the platform and popping the correct one.

When you have conditional value lists like this, how does that work with Find mode?

Stack two Menu popups. Create two value lists: All Meals (using all values from field) and Menu Meals (using conditional Meals relationship). Then in Field Behavior set All Meals popup to only allow entry in Find and set Menu Meals to only allow entry in Browse.

See attached. It's very easy to implement, Kristi. I've included everything you should need to get it running smoothly.

LaRetta

Kristi.zip

[KristiB]

Thank you, LaRetta! I will try this. Sounds like something I can do. How exciting that I can make it work without too much hassle!

Kristi

[KristiB]

Well, I got most of the things LaRetta described to work. However, I actually have 3 fields whose value lists are conditional, not just two. And when I tried to set up the third, it's not working correctly. I'm attaching my file so you can see.

The three fields are Project, Category and Subcategory. The layouts to be concerned with are "Data Entry" and "List of Proj_Cat_Sub". What happens is that the Subcategory ends up showing all the possible fields from the Project and does not take into account the Category. I must have the relationship wrong or something, but I'm not familiar enough with how relationships work to understand what's going on.

Thanks for your help.

Kristi

Kristi.zip

[-Queue-]

Change your Subcategory value list to use values from

Proj_Cat_SubCat CAT::Subcategory

starting from Bush Accounting 2.

[LaRetta]

And Kristi, you might consider changing your top Category and Subcategory popups to solid white fill. Otherwise you can get a shadow effect because of the overlapped popups.

[KristiB]

Thank you all! It's working great!

Okay, one last question, please. I want the Object Code to calculate based on the choices made by Project, Category, and Subcategory. I added the Object Code field to my Proj_Cat_Subcat table and filled in the values. Then, in the Bush Accounting 2 table, I made the value of Object Code be a lookup starting from Bush Accounting 2, lookup from Proj_Cat_Subcat SUB, by field Object Code. However, some of the records will not have a subcategory (it's not needed in some Projects/Categories), so the object code does not fill in.

How do I make this work? I can't just base the lookup on Proj_Cat_Subcat CAT because different projects have the same categories that may or may not have subs. (This whole category setup is too complicated in my opinion, but I can't change it.)

You guys are the best. I'm so glad I found this forum.

Kristi

[-Queue-]

One thing you could do is define Subcategory in both tables to auto-enter a space and change your scripts to set Subcategory with a space.

[KristiB]

Anyone else have any ideas? I'd rather not define Subcategory as a space, because I have several reports created where those records will show up as blank lines.