Your posts will typically get more views ( and responses ) if you post to the Discussions space instead of the Community Feedback space. Community Feedback is for issues related to the use of this website. Discussions space is for discussion about FileMaker and asking questions.
Fortunately, TSPigeon or one of the other TS people are very good about moving posts for you.
Thank you for your post!
As jormond suggested, I'm going to move this thread from the FileMaker Community Feedback Space (which is specifically for input on the Community itself) to the Discussions Space where you should receive more views and potentially more feedback on this topic!
You appear to be setting up what I call a "hierarchical conditional value list" there are several very different approaches that can be used for that and I may be misunderstanding that you want them to be hierarchical (Value list A, filters list B and B filters list C...)
It would help to show the value list set up dialogs for the ones that don't work for you.
You might also compare what you have set up to the examples in this file:
It has several working examples of Hierarchical CVLs along with detailed documentation on how they work and how they were set up. If you downloaded a copy of this some time in the past, I recommend downloading again as I've updated it to include some new examples--though none of the new ones are relevant to this particular question.
I've been going through your suggestions and trying to figure out how they could apply to my database needs.
So far nothing makes sense. Maybe I'm just pushing myself way over my head.
In any case, yes you are correct: I'm wanting a hierarchical Conditional Value List.
When you say "It would help to show the value list set up dialogs for the ones that don't work for you.", I'm not sure what you mean. ( Most likely cause I'm getting too dizzy with it all)
Here are some things I think you are asking for:
So if I choose a grade, then that chooses in the next list the Core Art number values that follow
Then these values determine a list of substandard numbers & letters
Does this help to understand what I need?
The grade list and Core Art list seem to work, but beyond that it doesn't.
Your examples so far are very complicated and I don't get very well how the data flow gets conditioned.
As I understand it, you want to select a "Grade" value and that should then set up a value list of "sub standard letters". Is that correct?
If so, you need two value lists where I currently see only one. The first value list is used to select a value used to filter the values for the second value list.
Next needed bit of info: On the layout where you've set up your value lists, on what table occurrence is it based? (A "table occurrence" is what we call the "boxes" in your relationship graph, such as "ICLP_Grade_List".) To determine that, open layout setup and tell me what you have selected in the "show records from" drop down. This name also appears to the right of "Table:" next to the layouts drop down when your layout is in layout mode. It's the relationship linking that table occurrence to the "starting from" table occurrence in your "specify fields for value list..." dialog that control any filtering of that list of values that takes place. Much of the time, your layout's table occurrence is the same as the "starting from" table occurrence that you specify for your value list. (Or sometimes it's that of a portal placed on the layout...)
And last bit of info, what is the name of the fields that you are formatting with these conditional value lists?
From what I see here, you need to open my file and work through the basic conditional value list example first before trying to set up anything Hierarchical.
I select "grade", which gives me "Core Art" value, which in turn, gives me a "substandard number & letter" value.
Grade ---> Core Art ---> Substandard # & letter
In terms of layouts and tables, I am using a layout based on the "ICLP_Grade_List" table. In this table, I'm using the following fields for the CV lists:
I will review the Basic CVL again.
Also, just in case this is important, I'm using 2 tables:
y_BM_National Core Music Content Standards Main Menu & ICLP_Grade_List. This second table has 2 more table occurrences with slightly different names, as I show on the table chart.
Table y_BM_National Core Music Content Standards Main Menu contain the values that the CVL's use.
ICLP_Grade_List & it's copies get the values chosen from the first table.
This is probably self evident, but in any case I feel if restated, it may help. Sorry if it doesn't.
Thanks for your support.
Knowing which table occurrence is the basis for your layout was a critical detail. Restating the other details is also helpful.
If you look at the relationships used for the Hierarchical CVL examples in the Adventure file, you'll see that they are not set up like you have set up here. Each of the table occurrences that are set up as value sources for a value list are all directly linked to the layout's table occurrence.
You only have one TO linked to the layout's TO and thus you don't have the relationships that you need. Note also that all but the last field formatted with a value list is one of the match fields used in a relationship to one of those TO's.
b IMLPC_Grade ---> matches to Y_BM_National.... as you have set up
c MLPCS_CoreArt ---> should match directly to ICLP Core Art List
Depending on the data in this table, the relationship to ICLP_Core_Art_List may also need to match by Grade.
The conditional value list used with c MLPCS_CoreArt would then list data from Y_BM_National...
The conditional value list used with c MLPCS_SubStandard_Number_Letter would list data from ICLP_Core_Art_List
Both of these value lists would specify: "Include Only related values, starting from ICLP_Grade_List
As I have pointed out in the Adventure File #1, There's a lot in common between a basic conditional value list and how you set up a portal. If you can set up a portal to list the values you want in your value list as rows in the portal, you have a relationship that can be be used for a conditional value list. The portal's TO would be the same as the one used as the source of values in the top of your Specify Fields for Value List dialog and your layout's TO would be the same that you'd use for your "starting from" TO.
OK. I think I've discovered that I'm looking a t tables in reverse order, for starters.
Looking at your HCVL layout I find that I'm only using two tables, with multiple TO's of one of them (NCMS for short). NCMS provides the values I need to populate the value lists.
I alo see that you have PK_ID's for each category, which I haven't been able to identify in my NCMS table, except for one, which is a PK_ID for every single record in that table.
The other subcategories don't have unique ID's, unless in combination with other fields.
I've tried the second HCVL you show, but still can't get anything to work beyond the second set of VL's, which is accessible through the first relationship between TO's.
This feels very over my head and frustrating.
I appreciate your support.
Try using Portals as a model for setting up the value lists. If you can get the correct values listed in the portal, the same relationship can be used for one of the conditional value lists.
OK. I've tried emulating your set up for HCVL, drawn diagrams to understand data flow, tried setting up portals and nothing works.
The only difference between your set up and mine, is that I have 3 occurrences of the table where all the VL data comes from to the layout I work from, vs yours that has 3 separate tables that feed VL data to the layout you work from. Is this the problem?
What I've understood about the trigger scripts, is that they only avoid choosing more than one item per field, but that doesn't interfere with the CVL's themselves.
Am I wrong?
I spelled out what you needed to do in reply #7. It tells you what you need to do. The relationships in your screen shot of #4 are not set up correctly for the CVL's that you are trying to create.
I’ve gotten to have CVL’s that seem to work well up to the last level.
I’ve also set up a portal that shows records for the first level CVL. The rest of the CVL’s do not seem to affect records in the portal.
Here’s the structure I have:
This is the test layout I made with a portal.
If I choose a different grade, the content of the portal changes to reflect the grade level content. When I choose Artistic Process, Core Art, SubStandard # and finally the Letter, portal content does not change.
Here are more shots of my structure:
First level of CVL (Grade) Seems to work well.
Second level of CVL (Art. Process) seems to work for individual record, but does not show change in portal.
Third level of CVL (Core Art) seems to work for individual record, but does not show change in portal.
Forth level of CVL (Sub Standard #) seems to work for individual record, but does not show change in portal.
Finally, Fifth level of CVL (Sub Stand. letter) Does not work for individual record nor for the portal.
I don’t find my error/mistake. I’ve reviewed the sample CVL’s but can’t figure out what I’m still doing wrong.
This is the test layout I made with a portal.
And this would be a portal to what table occurrence? And the layout on which you placed this portal is based on Lesson Plans? I would guess that your portal is to y_BM_NCMS|Grade. And yes, changing the value of the other fields: ICLP_007..., ICLP_009... and ICLP_011... will not affect what appears in such a portal. Those fields aren't part of the relationship between LessonPlans and y_BM_NCMS|Grade. Thus the portal is doing what you set it up to do, even if it is not doing what you want it to do.
If you want the values of all four fields to control what appears in the portal, you have two options:
1) Create yet another relationship with another table occurrence and use all four fields as match fields to corresponding fields in the related table occurrence. Base your portal and the portal's fields on this 5th table occurrence. This is pretty simple to add to what you have here, but the portal will not show any records until you have selected values in all four fields in LessonPlans.
2) Keep your portal as is, but add a portal filter to it that filters on all four values. This can produce a portal that updates with each new value selection to show fewer and fewer matching records in the portal, but the portal filter expression for 3 fields is pretty complex.
( IsEmpty ( ICLP_007_Artistic_Process01 ) Or
ICLP_007_Artistic_Process01 = y_BM_NCMS|Grade::Artistic_Processes ) AND
( IsEmpty ( ICLP_009_Core_Art ) or
ICLP_009_Core_Art = y_BM_NCMS|Grade::Core_Arts ) AND
( IsEmpty ( ICLP_011_Substandard_Number ) Or
ICLP_011_Substandard_Number = y_BM_NCMS|Grade::Sub_Standard Item Number )
You'll probably find that you need to commit records after each menu selection if you try this option. You can click a blank area of your layout to commit records or you can use a script trigger on the value list formatted fields to make this happen automatically.