 | |
Create a Value List based on a Selection from a Value List
comp.databases.filemaker comp.databases.filemaker
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Create a Value List based on a Selection from a Value List -
10-29-2009
, 01:47 PM
#2
| |||
| |||
|
|
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. |
filter criteria show up.
When you create a dynamic value list, that is, one derived from a field
in a table, you can select either to include all values, or only include
values via a relationship. If you select the latter, it will ask you to
specify the table to start from in the relationship.
The value list of students you have already set up shows all the
students. Presumably you want it that way.
So you would need a new value list of students to be filtered by school.
Call it "Students by School" for the sake of discussion.
You would need to have a table occurrence of students that is related to
school by the field SchoolID.
You would define the value list on the basis of that table occurrence of
Student, and select "Include only values starting from [the School table
occurrence in the relationship]"
That way, the list should only show the students assigned to that school.
This description is based on the assumption that your system has only a
one-to-many relationship of School to Student, that is, one school can
have many students, but one student has only one school.
If it is a many-to-many, so that one school can have many students and
each student can have many schools, then you would need to take that
into account in defining the relationships and value lists.
It is not far-fetched to suppose one student could have many schools. If
the system tracks students through all their years in the school system,
then they would at least progress from elementary to middle to high
school. Also, they might take some classes at a school different from
their home school. So you probably should set things up with a
many-to-many relationship of schools to students by use of an
intermediate Join table, that might be called Registration. Then you
would need to use the Registration table in defining your filtered value
list.
#3
| |||
| |||
|
|
In article f6e46a8f-cd02-449e-8502-a44acfb3f... (AT) 2g2000prl (DOT) googlegroups.com>, *senriz <dbsen... (AT) gmail (DOT) com> wrote: 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. You can create a filtered value list, so only values that meet the filter criteria show up. When you create a dynamic value list, that is, one derived from a field in a table, you can select either to include all values, or only include values via a relationship. If you select the latter, it will ask you to specify the table to start from in the relationship. The value list of students you have already set up shows all the students. Presumably you want it that way. So you would need a new value list of students to be filtered by school. Call it "Students by School" for the sake of discussion. You would need to have a table occurrence of students that is related to school by the field SchoolID. You would define the value list on the basis of that table occurrence of Student, and select "Include only values starting from [the School table occurrence in the relationship]" That way, the list should only show the students assigned to that school. This description is based on the assumption that your system has only a one-to-many relationship of School to Student, that is, one school can have many students, but one student has only one school. If it is a many-to-many, so that one school can have many students and each student can have many schools, then you would need to take that into account in defining the relationships and value lists. It is not far-fetched to suppose one student could have many schools. If the system tracks students through all their years in the school system, then they would at least progress from elementary to middle to high school. Also, they might take some classes at a school different from their home school. So you probably should set things up with a many-to-many relationship of schools to students by use of an intermediate Join table, that might be called Registration. Then you would need to use the Registration table in defining your filtered value list. |
"So you would need a new value list of students to be filtered by
school.
Call it "Students by School" for the sake of discussion. You would
need to have a table occurrence of students that is related to
school by the field SchoolID. You would define the value list on the
basis of that table occurrence of
Student, and select "Include only values starting from [the School
table
occurrence in the relationship]" "
....But my value list for the students is empty. I did select the
school first. The field to select a school is a dropdown value list
also... not sure if that makes a difference. Any idea what I'm
missing.
#4
| |||
| |||
|
|
On Oct 29, 1:36*pm, Bill <bbcoll... (AT) earthlink (DOT) net> wrote: In article f6e46a8f-cd02-449e-8502-a44acfb3f... (AT) 2g2000prl (DOT) googlegroups.com>, *senriz <dbsen... (AT) gmail (DOT) com> wrote: 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. You can create a filtered value list, so only values that meet the filter criteria show up. When you create a dynamic value list, that is, one derived from a field in a table, you can select either to include all values, or only include values via a relationship. If you select the latter, it will ask you to specify the table to start from in the relationship. The value list of students you have already set up shows all the students. Presumably you want it that way. So you would need a new value list of students to be filtered by school. Call it "Students by School" for the sake of discussion. You would need to have a table occurrence of students that is related to school by the field SchoolID. You would define the value list on the basis of that table occurrence of Student, and select "Include only values starting from [the School table occurrence in the relationship]" That way, the list should only show the students assigned to that school. This description is based on the assumption that your system has only a one-to-many relationship of School to Student, that is, one school can have many students, but one student has only one school. If it is a many-to-many, so that one school can have many students and each student can have many schools, then you would need to take that into account in defining the relationships and value lists. It is not far-fetched to suppose one student could have many schools. If the system tracks students through all their years in the school system, then they would at least progress from elementary to middle to high school. Also, they might take some classes at a school different from their home school. So you probably should set things up with a many-to-many relationship of schools to students by use of an intermediate Join table, that might be called Registration. Then you would need to use the Registration table in defining your filtered value list. Thank you. Very good explanation. Anyway, I did as you stated, "So you would need a new value list of students to be filtered by school. Call it "Students by School" for the sake of discussion. You would need to have a table occurrence of students that is related to school by the field SchoolID. You would define the value list on the basis of that table occurrence of Student, and select "Include only values starting from [the School table occurrence in the relationship]" " ...But my value list for the students is empty. I did select the school first. The field to select a school is a dropdown value list also... not sure if that makes a difference. Any idea what I'm missing. |
The layout in which you are making the selections must be based on some
table occurrence, and the selections presumably fill fields in that
table occurrence.
The relationship to make the filtered value list should be based on the
SchoolID field in the table occurrence that is the basis of the layout.
Then when you select the schoolID, that should cause the filtered value
lists of students to populate.
#5
| |||
| |||
|
|
In article 97e28733-b9c2-4267-acba-b91403282... (AT) f20g2000prn (DOT) googlegroups.com>, *senriz <dbsen... (AT) gmail (DOT) com> wrote: On Oct 29, 1:36*pm, Bill <bbcoll... (AT) earthlink (DOT) net> wrote: In article f6e46a8f-cd02-449e-8502-a44acfb3f... (AT) 2g2000prl (DOT) googlegroups.com>, *senriz <dbsen... (AT) gmail (DOT) com> wrote: 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. You can create a filtered value list, so only values that meet the filter criteria show up. When you create a dynamic value list, that is, one derived from a field in a table, you can select either to include all values, or only include values via a relationship. If you select the latter, it will ask you to specify the table to start from in the relationship. The value list of students you have already set up shows all the students. Presumably you want it that way. So you would need a new value list of students to be filtered by school. Call it "Students by School" for the sake of discussion. You would need to have a table occurrence of students that is relatedto school by the field SchoolID. You would define the value list on the basis of that table occurrenceof Student, and select "Include only values starting from [the School table occurrence in the relationship]" That way, the list should only show the students assigned to that school. This description is based on the assumption that your system has onlya one-to-many relationship of School to Student, that is, one school can have many students, but one student has only one school. If it is a many-to-many, so that one school can have many students and each student can have many schools, then you would need to take that into account in defining the relationships and value lists. It is not far-fetched to suppose one student could have many schools.If the system tracks students through all their years in the school system, then they would at least progress from elementary to middle to high school. Also, they might take some classes at a school different from their home school. So you probably should set things up with a many-to-many relationship of schools to students by use of an intermediate Join table, that might be called Registration. Then you would need to use the Registration table in defining your filtered value list. Thank you. *Very good explanation. *Anyway, I did as you stated, "So you would need a new value list of students to be filtered by school. Call it "Students by School" for the sake of discussion. You would need to have a table occurrence of students that is related to school by the field SchoolID. You would define the value list on the basis of that table occurrence of Student, and select "Include only values starting from [the School table occurrence in the relationship]" " ...But my value list for the students is empty. *I did select the school first. *The field to select a school is a dropdown value list also... not sure if that makes a difference. *Any idea what I'm missing. Ah! Missed that in your explanation of the setup. The layout in which you are making the selections must be based on some table occurrence, and the selections presumably fill fields in that table occurrence. The relationship to make the filtered value list should be based on the SchoolID field in the table occurrence that is the basis of the layout. Then when you select the schoolID, that should cause the filtered value lists of students to populate.- Hide quoted text - - Show quoted text - |
new layout. Thank you very much. In your recent instructions I did
have the related record incorrect and I corrected to the same TO that
reference the SchoolID. It still isn’t working. Unfortunately the
relationship is not as simple as I implied before. I should have paid
more attention. For that I am sorry and hope I haven’t wasted your
time. Although I am making progress in getting this “Filtered Value
List” to work, and am very grateful for that.
Anyway, 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.**
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 soooo appreciated.
#6
| |||
| |||
|
|
On Oct 30, 2:40*pm, Bill <bbcoll... (AT) earthlink (DOT) net> wrote: In article 97e28733-b9c2-4267-acba-b91403282... (AT) f20g2000prn (DOT) googlegroups.com>, *senriz <dbsen... (AT) gmail (DOT) com> wrote: On Oct 29, 1:36*pm, Bill <bbcoll... (AT) earthlink (DOT) net> wrote: In article f6e46a8f-cd02-449e-8502-a44acfb3f... (AT) 2g2000prl (DOT) googlegroups.com>, *senriz <dbsen... (AT) gmail (DOT) com> wrote: 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 FormI 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. You can create a filtered value list, so only values that meet the filter criteria show up. When you create a dynamic value list, that is, one derived from a field in a table, you can select either to include all values, or only include values via a relationship. If you select the latter, it will ask you to specify the table to start from in the relationship. The value list of students you have already set up shows all the students. Presumably you want it that way. So you would need a new value list of students to be filtered by school. Call it "Students by School" for the sake of discussion. You would need to have a table occurrence of students that is related to school by the field SchoolID. You would define the value list on the basis of that table occurrence of Student, and select "Include only values starting from [the School table occurrence in the relationship]" That way, the list should only show the students assigned to that school. This description is based on the assumption that your system has only a one-to-many relationship of School to Student, that is, one school can have many students, but one student has only one school. If it is a many-to-many, so that one school can have many students and each student can have many schools, then you would need to take that into account in defining the relationships and value lists. It is not far-fetched to suppose one student could have many schools. If the system tracks students through all their years in the school system, then they would at least progress from elementary to middle to high school. Also, they might take some classes at a school different from their home school. So you probably should set things up with a many-to-many relationship of schools to students by use of an intermediate Join table, that might be called Registration. Then you would need to use the Registration table in defining your filtered value list. Thank you. *Very good explanation. *Anyway, I did as you stated, "So you would need a new value list of students to be filtered by school. Call it "Students by School" for the sake of discussion. You would need to have a table occurrence of students that is related to school by the field SchoolID. You would define the value list on the basis of that table occurrence of Student, and select "Include only values starting from [the School table occurrence in the relationship]" " ...But my value list for the students is empty. *I did select the school first. *The field to select a school is a dropdown value list also... not sure if that makes a difference. *Any idea what I'm missing. Ah! Missed that in your explanation of the setup. The layout in which you are making the selections must be based on some table occurrence, and the selections presumably fill fields in that table occurrence. The relationship to make the filtered value list should be based on the SchoolID field in the table occurrence that is the basis of the layout. Then when you select the schoolID, that should cause the filtered value lists of students to populate.- Hide quoted text - - Show quoted text - I was able to get your first set of instructions to work creating a new layout. *Thank you very much. *In your recent instructions I did have the related record incorrect and I corrected to the same TO that reference the SchoolID. *It still isn’t working. *Unfortunately the relationship is not as simple as I implied before. *I should have paid more attention. *For that I am sorry and hope I haven’t wasted your time. *Although I am making progress in getting this “Filtered Value List” to work, and am very grateful for that. Anyway, 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.** 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 soooo appreciated.- Hide quoted text - - Show quoted text - |
StudentData (the non-duplicated TO) that does work shows all students.
#7
| |||
| |||
|
|
On Nov 1, 3:36*pm, senriz <dbsen... (AT) gmail (DOT) com> wrote: On Oct 30, 2:40*pm, Bill <bbcoll... (AT) earthlink (DOT) net> wrote: In article 97e28733-b9c2-4267-acba-b91403282... (AT) f20g2000prn (DOT) googlegroups.com>, *senriz <dbsen... (AT) gmail (DOT) com> wrote: On Oct 29, 1:36*pm, Bill <bbcoll... (AT) earthlink (DOT) net> wrote: In article f6e46a8f-cd02-449e-8502-a44acfb3f... (AT) 2g2000prl (DOT) googlegroups.com>, *senriz <dbsen... (AT) gmail (DOT) com> wrote: 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. You can create a filtered value list, so only values that meet the filter criteria show up. When you create a dynamic value list, that is, one derived from afield in a table, you can select either to include all values, or only include values via a relationship. If you select the latter, it will ask you to specify the table to start from in the relationship. The value list of students you have already set up shows all the students. Presumably you want it that way. So you would need a new value list of students to be filtered by school. Call it "Students by School" for the sake of discussion. You would need to have a table occurrence of students that is related to school by the field SchoolID. You would define the value list on the basis of that table occurrence of Student, and select "Include only values starting from [the School table occurrence in the relationship]" That way, the list should only show the students assigned to thatschool. This description is based on the assumption that your system has only a one-to-many relationship of School to Student, that is, one school can have many students, but one student has only one school. If it is a many-to-many, so that one school can have many students and each student can have many schools, then you would need to take that into account in defining the relationships and value lists. It is not far-fetched to suppose one student could have many schools. If the system tracks students through all their years in the school system, then they would at least progress from elementary to middle to high school. Also, they might take some classes at a school different from their home school. So you probably should set things up with a many-to-many relationship of schools to students by use of an intermediate Join table, that might be called Registration. Then you would need to use the Registration table in defining your filtered value list. Thank you. *Very good explanation. *Anyway, I did as you stated, "So you would need a new value list of students to be filtered by school. Call it "Students by School" for the sake of discussion. You would need to have a table occurrence of students that is related to school by the field SchoolID. You would define the value list on the basis of that table occurrence of Student, and select "Include only values starting from [the School table occurrence in the relationship]" " ...But my value list for the students is empty. *I did select the school first. *The field to select a school is a dropdown value list also... not sure if that makes a difference. *Any idea what I'm missing. Ah! Missed that in your explanation of the setup. The layout in which you are making the selections must be based on some table occurrence, and the selections presumably fill fields in that table occurrence. The relationship to make the filtered value list should be based on the SchoolID field in the table occurrence that is the basis of the layout. Then when you select the schoolID, that should cause the filtered value lists of students to populate.- Hide quoted text - - Show quoted text - I was able to get your first set of instructions to work creating a new layout. *Thank you very much. *In your recent instructions I did have the related record incorrect and I corrected to the same TO that reference the SchoolID. *It still isn’t working. *Unfortunately the relationship is not as simple as I implied before. *I should have paid more attention. *For that I am sorry and hope I haven’t wasted your time. *Although I am making progress in getting this “Filtered Value List” to work, and am very grateful for that. Anyway, 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.** 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 soooo appreciated.- Hide quoted text - - Show quoted text - I forgot to mention in previous post that the Value List from StudentData (the non-duplicated TO) that does work shows all students.- Hide quoted text - - Show quoted text - |
StudentData TO and related it to Form40 using the StudentID
(Form40::StudentID = Form40_StudentData::StudentID), and the Value
List is based off of that. In the Portal (Form40_LineItems) I use the
StudentID with this value list. I also inserted the FName and LName
field from Form40_LIneItems_StudentData, so when a student is select
their name appears automatically. Thanks for all the help.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.