13 Replies Latest reply on Nov 2, 2009 12:41 PM by senriz_1

    Create Value List based on a Selection from a Value List

    senriz_1

      Title

      Create Value List based on a Selection from a Value List

      Post

      In my FMP9 database, two of the tables are School and Students and are joined via ID fields.  When a student is entered into the db a school is selected so we know which school they are assigned to.  Right now, I have a Value List that connects the studentID to their Name and another that connects the schoolID to it's Name.  On a new Form I created I can select a school from a drop-down value list, but I need to create a Value List that shows only the Students who are assigned to the School that was selected from the value list.  Is there a way to do this?    Thanks.

        • 1. Re: Create Value List based on a Selection from a Value List
          philmodjunk
            

          This is called a conditional value list.

           

          Assuming your new layout is bassed on School...

           

          In Manage | Value Lists... take either your existing students value list or make a copy of it. Edit the value list.

          Click Specify Field... and select "Include only related values starting from School"

           

          This value list will now be filtered by the School selection to only show those students assigned to the school you select on the current record.

          • 2. Re: Create Value List based on a Selection from a Value List
            senriz_1
               I must be missing something.  Can you help me further?  I kept the SchoolID_Name drop-down Value List as is.  I then made a copy of the StudentID_Name Value List, selected Specify Field, selected the button, "Include only related values starting from:  SchoolRecord".  Then the dropdown list for Students became empty.  I reversed this and did the same to the SchoolID_Name, selecting the button for related values to be StudentRecord and then dropdown list for the School became empty too.
            • 3. Re: Create Value List based on a Selection from a Value List
              philmodjunk
                

              "Then the dropdown list for Students became empty."

               

              At what point did it become empty?

              You dismissed the dialogs by clicking OK, Selected the new value list for your School::StudentID field. Entered Browse mode and clicked to drop down the field? Is this what happened?

               

              Had you already selected a schoolID in the SchoolID field first? If that field is empty, you won't see any values as this is the field your relationship is based on.

              • 4. Re: Create Value List based on a Selection from a Value List
                senriz_1
                   Yes.  I selected the school first, which is a value list that consist of schoolID_SchoolName, then tabbed to select the students and the dropdown value list is now empty.  
                • 5. Re: Create Value List based on a Selection from a Value List
                  philmodjunk
                    

                  That suggests that something is unusual in your relationship between the two tables or the layout you are setting up.

                   

                  Under layout setup, what table is listed in "Show Records From"?  It should show "School" and this should match the "starting from" name in the value list setup.

                   

                  Double check your relationship graph and post the names of the Table Occurrences and the key fields for the relationship linking these two tables.

                  • 6. Re: Create Value List based on a Selection from a Value List
                    senriz_1
                      

                     I must have the Relationships wrong and/or the Value List.  This is how my relationship with the TOs is set up:  I made a duplicate copy of the SchoolData and the StudentData.  I also created a TO called Form40 and another called Form40_LineItems. 

                     

                    The Form40 TO is related to the Form40_LineItems TO by Form40ID, with the checkbox “Allow creation of records in this table via the relationship”.

                     

                    The Form40_LineItems TO is related to the Form40_LineItems_StudentData To by StudentID.

                     

                    The Form40 TO is also related to the Form40_SchoolData TO by the SchoolID.

                     

                    The Form 40 Layout is from the Form40 TO.  The SchoolID is from the Form40 TO, also.

                     

                    I put in a portal using data from the Form40_LineItems TO, which includes the StudentID field.

                     

                    The form works with the value list associated with the StudentID field in the portal as long as it is not related to Form40.  But, once I select the “Include only related values starting from:  Form40”.  No data shows up in the drop-down StudentID field.

                     

                    My value list, that works, is created as follows:  In the Values:  Use value from field, the first field is

                    StudentData::StudentID (this is not the duplicated TO).  In the “Also show values from a second field” I selected StudentData::LastFirstName.  **This works as long as I don’t select the related values starting from button, but it shows all the students.**

                     

                    I made a copy of this value list and re-selected the fields using the duplicated TOs:   Form40_LineItems::StudentID.  In the “Also show values from a second field” is Form40_LineItems_StudentData::LastFirstName.  **This doesn’t work, whether or not the related values starting from button is selected.**

                     

                    Any further assistance would be so appreciated. 

                    • 7. Re: Create Value List based on a Selection from a Value List
                      philmodjunk
                        

                      I don't see the needed relationship linking Form40 to your student data based on SchoolID.

                       

                      If I decode your post correctly, your layout is based on Form40 and your portal refers to Form40_lineItems, with Form40ID as the linking key field:

                      Form40::Form40ID = Form40_LineItems::Form40ID

                       

                      You need the following relationship:

                      Form40::SchoolID = StudentData::SchoolID

                       

                      Then specify your fields has coming from the StudentData TO (your relationship graph may require you to name if differently from my example) and use related values starting from Form40. That should work for you.

                      • 8. Re: Create Value List based on a Selection from a Value List
                        senriz_1
                           Thank you!! Thank you!!  The value list now shows just those students assigned to that school. 

                         

                        I just now have one more problem with it.  When I enter in the first student... their name shows up in the LName and FName fields, but that same name shows up on the next line even when I select a different student.  My portal is using the fields from Form40_LineItems, except the FName and LName are from Form40_StudentData. How do I get each name to show up?

                         

                        Again, thank you for helping me with this Value List... so grateful. 

                        • 9. Re: Create Value List based on a Selection from a Value List
                          senriz_1
                             I thought perhaps I misunderstood you and switched the portal to be from Form40_StudentData and when I did that the Value List still worked but every student assigned to the selected school is automatically filled in on every line on the Form.  I don't get the option to choose it just fills it in automatically.  BTW, I did re-do the relationships as you described in your post.  Any idea what I'm still doing wrong?  Thanks again.
                          • 10. Re: Create Value List based on a Selection from a Value List
                            philmodjunk
                              

                            You need one relationship for your portal:

                            Form40::Form40ID = Form40_LineItems::Form40ID

                             

                            and one relationship for your value list:

                            Form40::SchoolID = StudentData::SchoolID

                             

                            I can't tell from your posts what "Form40" is: A TO that points to School data perhaps?

                            And I don't know what "Form40ID" represents--but it should be independent of the relationship that filters your value list down to just the students from the selected school.

                             

                            You may need to double check the TO you referred to when you placed the name fields into the portal. Your description suggests you may have specified the wrong TO.

                            • 11. Re: Create Value List based on a Selection from a Value List
                              senriz_1
                                

                              I do have the one relationship for my portal,  Form40::Form40ID = Form40_LineItems::Form40ID.  (Form 40 is a Form/Register where certain students are selected to be put on this particular list/register, but not all the students from the selected school are on this list)

                               

                              I do have the relationship Form40::SchoolID = Form40_StudentData::SchoolID.

                               

                              My portal is from Form40_LineItems.  The value list works perfectly once I corrected my relationships as you instructed from your post.  I just need to get the student's name to show up after selecting them from the value list associated with the studentID field.  The student name fields (LName, FName) are from the TO Form40_StudentData.  Is this where I'm making my mistake?

                              • 12. Re: Create Value List based on a Selection from a Value List
                                philmodjunk
                                  

                                Very likely.

                                 

                                I went back and checked over your post where you listed your TO's. Form40_StudentData is not listed there, so I can't tell how you've related it to your portal's TO, Form40_LineItems_StudentData.

                                 

                                There should be this relationship in order for this to work:

                                 

                                Form40_LineItems_StudentData::StudentID = Form40_StudentData::StudentID

                                 

                                Where Form40_LineItems_StudentData::StudentID is the field you are setting up with the value list.

                                • 13. Re: Create Value List based on a Selection from a Value List
                                  senriz_1
                                    

                                  My mistake, after you instructed me to create a relationship between Form40::StudentID = Form40_StudentData::StudentID, I removed the StudentData from the relationship with Form40_LineItems.  I thought you meant it related incorrectly... I re-created it.  Everything works fine in the portal now.  The studentID is from the Form40_LineItems, the LName and FName is from Form40_LineItems_StudentData in the Portal.  I tried making the StudentID from Form40_LineItems_StudentData, but it wouldn't work. 

                                   

                                  I just want to say thank you a thousand times.  I really really appreciate all the patience and instruction you gave me to fix this issue!!