13 Replies Latest reply on Feb 29, 2012 11:28 AM by philmodjunk

    Value list Problem

    xtremu

      Title

      Value list Problem

      Post

      I am using Filemaker Pro 11 Advanced. My problem now is related to the value list that i created in my portal, i have 3 tables: Student, Enrollment and Session with a join table relationships.

      Student::pkStudID=Enrollment::fkStudID

      Session::pkSessionID = Enrollment::fkSessionID 

      Student table is for student information records, Enrollment table is join table to hold multiple records from Student and Session table, and the Session table for subjects information such as section, description, time, units, days etc..

      Now, I created portal in one of my layout (let say Block section layout) which show records from student table and portal works fine, it shows information on every field in the portal when I click field having value list from fksession field.

      First problem: I need to click every row of the portal to select subjects and add that to the portal, which is not good and not friendly. I want to have one value list field in the portal which show all the subjects to be selected and if I select this then it will add to the portal row up to the maximum units allowed.

      Is their any script or tricks for this? please help

      Second Problem: I tried to made a sub summary, Total Units (sum of=units/results is number), to automatically sum up the units of the units row of the portal but no luck to figure out this.

      Any help would be much appreciated

      thanks

        • 1. Re: Value list Problem
          philmodjunk

          You want to make one selection from a value list and have the selected value put in a field in every record listed in the portal? I wouldn't put that field in the portal, I'd put it on the layout instead as you don't need to see it repeated on every row, but putting it in every row can work too.

          Can you confirm that this is what you want?

          If so, a script trigger on the value list formatted field can perform a script that pulls up a found set of the portal records on a different layout and updates them with either a looping script or a replace field contents script step.

          • 2. Re: Value list Problem
            xtremu

             Hi Phil, Yes Phil that's want I want to do in the portal, so that student can select thier subjects using single value list and put the selected value in every field listed in the portal.

            However, if it is also possible to put that value list field on the layout instead in the portal and if it's good, please advice me how to do this so that I have other option to work on.

            What is the script for this Phil, anyway I am developing my enrollment system and Im on the stage of selecting subjects to be enroll by the student.

            Phil can you help me also on how to total the number of units in the portal row?

            Thanks Phil 

            • 3. Re: Value list Problem
              philmodjunk

              I find myself wondering why you need to assign the same subject info to multiple records if this portal is showing records for a single student.

              Here's the script:

              Freeze Window
              Set Variable[$Subject ; YourTable::YourField ]
              If [Not IsEmpty ( PortalTable::ForeignKey ) //only needed if YourTable::YourField is a drop down field outside portal ]
                 Go To Related Record [Show only related records; From table: PortalTable; Using layout: "PortalTable" (PortalTable)]
                 Replace Field Contents [no dialog; PortalTable::subject; $Subject]
                 Go to Layout [original layou]
              End IF

              The script works the same whether you use a field outside the portal or inside the portal. The If step is used to check for the existance of portal records so you can remove that test if you use a drop down inside the portal row.

              • 4. Re: Value list Problem
                xtremu

                 Thanks for the advice Phil, sorry for the late response.

                Picture below is the scenarios that im trying to solve, hopefully you can help me solve on this.

                 

                xtremu

                • 5. Re: Value list Problem
                  philmodjunk

                  Yes, but why would one student have more than one record with the same subject?

                  Or do you wan to add a NEW record to the portal with the subject selected from your drop down?

                  • 6. Re: Value list Problem
                    xtremu

                    1. but why would one student have more than one record with the same subject? = It's just only an example Phil, I was not able to select different subjects before i make it as picture. Of course student should enrolled different subject phil. Im sorry for that.

                    2. do you wan to add a NEW record to the portal with the subject selected from your drop down? = YES Phil, that's exactly what I wanted to do Phil. That field is from the Enrollment joint table that you suggested before, that field is fkSessionID-dropdownlist-values from field Enrollment::fkSession and display values from second field Session::Section and show value only from second field.

                    Student::pkStudentID=Enrollment::fkStudentID

                    Session::pkSessionID=Enrollment::fkSessionID

                     

                     

                    thanks for your quick response and wait for your advice..

                    • 7. Re: Value list Problem
                      philmodjunk

                      1) but this whole post has been "how do I assign the same value to every record in a portal" if that's not the case then I'm more confused than ever. The reason I am asking this question is that often, finding yourself in a situation where you need to assign the same value to multiple records is a case where storing the value in a single record that is related to all the records is better database design and avoids the need to assign the same value over and over. It's not always the case that you should do this, but is an option to be carefully considered.

                      To add a new record to the portal, simply select a subject from the last bottom row of the portal if you have "allow creation of records via this relationship" specified for the portal's table. If not, you'd use a drop down outside the portal with this script:

                      Freeze Window
                      Set Variable [$Subject ; value: YourTable::DropDownField ]
                      Set Variable [$StudentID ; value: Student::pkStudentID]
                      Go to layout [Enrollment]
                      New Record/Request
                      Set field [Enrollment::fkStudentID ; $StudentID]
                      Set Field [Enrollment::Subject ; $Subject]
                      Go to layout [original layout]

                      • 8. Re: Value list Problem
                        xtremu

                         Phil I created value list field and used script trigger on object modify to run the script below but nothing happened. If I click the value list field it shows the selected subjects then If I select the subject this will add in the first portal row, then what ever subject I selected in the value list still show in the first row of the portal, that supposedly in the second row. I removed alaso the creation of records on this table.

                        That field is from the Enrollment joint table, that field is fkSessionID-dropdownlist-values from field Enrollment::fkSession and display values from second field Session::Section and show value only from second field.

                        Student::pkStudentID=Enrollment::fkStudentID

                        Session::pkSessionID=Enrollment::fkSessionID

                        What's wrong Phil?

                        Code:

                         

                        • 9. Re: Value list Problem
                          philmodjunk

                          If you enter layout mode, click the drop down field located outside the portal and check "display from" in the inspector's data tab, does it show Enrollment::fkBSASubjectID?

                          If not, what do you see?

                          If the layout is based on Sutdent, this field should use either a field with global storage specified (and not the field from enrollment) or a field defined in the student table and then the set variable step to give $Subjects a value would refer to this field instead of the field defined in Enrollment.

                          Is the value list defined to show the subjectId in field 1 and the subject name in field 2 with field 1 values hidden?

                          PS. The reference to Enrollment is being displayed as ${Enrollment }::fkBSASubjectID because your table occurrence box in Manage | Database | Relationships includes a space after the t in Enrollment. If you find this table occurrence, double click it and delete the space, this added ${ } notation should disappear.

                          • 10. Re: Value list Problem
                            xtremu

                             If you enter layout mode, click the drop down field located outside the portal and check "display from" in the inspector's data tab, does it show Enrollment::fkBSASubjectID? YES PHIL, it show Enrollment::fkBSASubjectID

                            If the layout is based on Student, (YES, based on Student Layout) this field should use either a field with global storage specified (and not the field from enrollment) or a field defined in the student table and ( Actually, it is defined in Enrollment Table)

                            then the set variable step to give $Subjects a value would refer to this field instead of the field defined in Enrollment = How Phil?

                            Is the value list defined to show the subjectId in field 1 and the subject name in field 2 with field 1 values hidden? = (first field = Enrollment::fkBSASubjectID, second field BSASubjects::Section or Course No., field 1 value have a white font color Phil. 

                            PS. The reference to Enrollment is being displayed as ${Enrollment }::fkBSASubjectID because your table occurrence box in Manage | Database | Relationships includes a space after the t in Enrollment. If you find this table occurrence, double click it and delete the space, this added ${ } notation should disappear. I can't find this Phil, I tried to delete all occurences and make another occurence but still it's the same Phil.

                            Layout where portal is created in on Student Table Phil. Hope you can help me on this, What is the script or tricks for this..

                             

                            • 11. Re: Value list Problem
                              philmodjunk

                              How Phil?

                              Define a new field and use it instead of Enrollment::fkBSASubjectID for your drop down list. If you give it global storage, you can define it in any table in your database file and it will work. I often set up a globals table just for defining such global fields. If you do not give it global storage, define this new field in the students table.

                              When you use Enrollment::fkBSASubjectID as your drop down, you are modifying an existing record in the enrollment table just by selecting a value from the value list.

                              field 1 value have a white font color

                              Hmm, that can't be done. I must assume that you've set the field on the layout to have a white font color. If you want to hide the ID numbers inside the value list, you can select an option for that inside Manage | valuelists, but don't select that option unless you also ensure that BSASubjects::Section or Course No. only stores unique names--you may want to add a unique values field validation on that field to make sure of that.

                              I can't find this Phil...

                              Double click the "box" I have circled in red. Use the text box in the bottom of the dialog that opens to edit the name. From what I've seen in your posts, there's a space after the t in Enrollment. You should be able to click to the right of the name, then press the Backspace key until the cursor is just to the right of the last letter,. Close the box and you are done. You may find that you also have this space in the table's name. You can go to the tables tab and change the name of the table there in addition to the first change. You do not have to do this, it just cleans things up a bit.

                              • 12. Re: Value list Problem
                                xtremu

                                 Any idea or what is the script for this Phil?

                                • 13. Re: Value list Problem
                                  philmodjunk

                                  Freeze window
                                  Set Variable [$SubjectID ; value: Students::yourNewDropDownfieldHere]
                                  Set Variable [$StudentID ; value: Students::pkStudentID]
                                  Go to layout [Enrollment ] // any table based directly on the portal's table that does not have any layout based script triggers to interfere will work.
                                  New Record/Request
                                  Set field [Enrollment::fkStudentID ; $StudentID]
                                  Set Field [Enrollment::fkSubjectID ; $SubjectID]
                                  Go to Layout [original layout]

                                  Double check my field and table names as I didn't take time to flip back and review the previous posts where you spelled them out.