14 Replies Latest reply on Apr 3, 2013 9:07 AM by LaRetta

    Creating a To Do List for students

    olliecollins

      Hi there,

       

      I use a FM12 db to track student's work, and want to create a report which acts as a to do list, telling students which pieces of work they need to complete.

       

      Fields have values entered as follows:

       

      Work A (Field1)

       

      Work is complete (Value1)

      Needs finishing but not urgent (Value2)

      Needs finishing as a priority (Value3)

       

      What I want to report (ideally on an emailable PDF) is:

       

      StudentA

       

      Work Urgently Needed:

       

      Work A(Field1)

      Work B(Field2)

       

      Work that needs finishing:

       

      WorkC(Field3)

      WorkD(Field4)

       

      Work Complete

       

      WorkE(Field5)

       

      Anyone got any suggestions?

        • 1. Re: Creating a To Do List for students
          taylorsharpe

          Try a related table of work that needs done with a single field identifying whether it is urgently needed, needs finishing, or is complete.  Then write a report using a layout based on the work table and it will show the related student names in each record.  Just remember you'll have a student table and you'll have a work table and the work table will have a foreign key related back to the primary key in the student able.  The key will probably something like student ID. 

          • 2. Re: Creating a To Do List for students
            olliecollins

            I'm trying to use the following script to return a a list of fields that have been marked as Urgent, which should give me a To Do list for my students.

             

             

            GetField ( Evaluate ( FieldValue = "Urgent" {; [_4._Criteria_Brief ; _5._Theme ;…]} ) )

             

             

            I haven't got it working yet. Any suggestions gratefuly received.

            • 3. Re: Creating a To Do List for students
              taylorsharpe

              PS:  Ignore the Case example I previously posted.  Case only evaluates to the first successful.  This would work for you:

               

               

              If ( _4._Criteria_Brief = "Urgent" ; "_4._Criteria_Brief¶" ) &

               

              If ( _5._Criteria_Brief = "Urgent" ; "_5._Criteria_Brief¶" ) &

               

              If ( _6._Criteria_Brief = "Urgent" ; "_6._Criteria_Brief¶" ) &

               

              If ( _7._Criteria_Brief = "Urgent" ; "_7._Criteria_Brief¶" ) &


              ...

              • 4. Re: Creating a To Do List for students
                olliecollins

                many thanks for that solution, however, it's a little hit-and-miss: it will return the first "Urgent" value, but will only return subsequent values if they are all the same.

                 

                i.e. if there are four "Urgent" values in a row, they will all be reported, but if the sequence goes "Urgent", "complete", "Urgent",  "Urgent", it will only report the first "Urgent".

                • 5. Re: Creating a To Do List for students
                  taylorsharpe

                  That is true.... and that is why I posted an update using the If statements instead of the Case statement.  If you'll visit my update you will be good to go. 

                  • 6. Re: Creating a To Do List for students
                    olliecollins

                    Hi there,

                     

                    to clarify:

                     

                    the 'If' example is the one I'm referring to in my previous post, and is behaving as I described. Any ideas?

                    • 7. Re: Creating a To Do List for students
                      taylorsharpe

                      What are the names of the fields that have "Urgent" in them?

                      • 8. Re: Creating a To Do List for students

                        Ollie said, "I'm trying to use the following script to return a a list of fields that have been marked as Urgent...

                         

                        Work Urgently Needed:

                         

                             Work A(Field1)

                             Work B(Field2)

                         

                        Work that needs finishing:

                         

                             WorkC(Field3)

                             WorkD(Field4)

                         

                        Work Complete

                         

                             WorkE(Field5)

                        --------------------------------

                         

                        Work should be records and not fields, Ollie.  I would suggest that you pause and review your need from a structural level and then the reporting will be a snap.

                        • 9. Re: Creating a To Do List for students

                          Whenever you have several 'like' fields it is major red flag that you are off the path.

                          • 10. Re: Creating a To Do List for students
                            taylorsharpe

                            Write a Script

                             

                            SetVariable ( $Table = Get ( LayoutTableName ) ) ;

                            SetVariable ( $Fields = ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName='" & $Table & "' ORDER BY FieldName” ; “” ; “”  )

                            SetVariable ( $Loops = ValueCount ( $Fields ) )

                            Loop

                                 SetVariable ( $Loop = $Loop + 1 )

                                 ExitLoopIf ( $Loop > $Loops )

                                 SetVariable ( $Result = Evaluate ( $Table & "::" & GetValue ( $Fields ; $Loop ) )

                                 If ( $Result = "Urgent" )

                                      SetVariable ( $Urgent Fields = $Urgent Fields & "¶" & GetValue ($Fields ; $Loop ) )

                                 EndIf

                            EndLoop

                            1 of 1 people found this helpful
                            • 11. Re: Creating a To Do List for students

                              Oh no, Taylor, don't use sledge hammer to force results from record/field matrix which shouldn't exist in the first place.  This requirement, if properly structured, would be simple FIND then simple SUMMARY report.

                              • 12. Re: Creating a To Do List for students
                                taylorsharpe

                                He says he wants all fields that have "Urgent" in them and I have no idea of knowing what those field names are.  This will find all fields and test if "Urgent" is in them and create a list of $Urgent Fields that have the word "Urgent" in them.  He wants Field names, not what is in the field. 

                                1 of 1 people found this helpful
                                • 13. Re: Creating a To Do List for students
                                  taylorsharpe

                                  I wouldn't use Summary because I think he wants this record by record.

                                  • 14. Re: Creating a To Do List for students

                                    Taylor said, "He says he wants all fields that have "Urgent" in them and I have no idea of knowing what those field names are.  This will find all fields and test if "Urgent" is in them and create a list of $Urgent Fields that have the word "Urgent" in them.  He wants Field names, not what is in the field. "

                                     

                                    Sure we know the field names ... Work A, Work B ... Why would he want field names?  If he DOES want field names it is because he is trying to pull information from wrong structure to get his results.  It is not the answer.

                                     

                                    Olie said, when providing his original requirements, " I use a FM12 db to track student's work, and want to create a report which acts as a to do list, telling students which pieces of work they need to complete."

                                     

                                    ... and what Ollie displays for a sample is simple sub-summary IF the fields were records utilizing correct structure.  I could be wrong indeed so maybe we should ask for clarification.  I would really like to see his file or a simple file showing his setup.  If given sample of his setup ( just the pertinent tables/fields then I would be happy to provide example of easy solution.  :-)