9 Replies Latest reply on Nov 8, 2016 7:04 AM by philmodjunk

    Case Function Best Practice?




      I have a simple grade book program.  I use the case function to set the quarter of the marking period for each assignment..


      quarter 1<11/8/2016

      quarter 2 <1/20/2017



      I have used the case function to set up the quarter for each record.  Is there a best practice to set this up and allow  the user change the dates---but not have to go in and rescript?



      I hope I am making sense.....







        • 1. Re: Case Function Best Practice?

          Get ( CurrentDate )

          Year ( myDateField )

          can be used (with some date math) as the years change.

          are these dates (month & day) correct for each quarter? what are the starting dates for the other quarters?



          • 2. Re: Case Function Best Practice?
            David Moyer


            before best practices, are these quarter-end dates variable for all users or variable for each individual user?  That is, are they global across all users, or just for the current user?  And, how many quarter-end dates are there?  Just four, or do they accumulate over time?

            • 3. Re: Case Function Best Practice?

              My guess is you're placing each assignment into a specific quarter based on the Date Assigned, or something like that.


              If you begin each school year with an empty database, and you're the only one using the database, then doing it the way you currently do is certainly adequate.


              You could set your database up with a new "Quarters" table, and in that table, you'd create a primary ID field, a School Year field (2016-2017, for instance), a Quarter field (1), a Start of Quarter field (e.g., 9/1/2016), and an End of Quarter field (e.g., 11/1/2016). You'd add four new records each year, one for each quarter. Your relationship could then be something that took into account the quarter start and stop dates. Something like:


              QuarterStartDate <= AssignmentDate AND

              QuarterStopDate >= AssignmentDate


              Then you could use Auto Enters to calculate the quarter and school year when you enter an assignment date, or you could just rely on the relationship to properly give you the school year and quarter information from the related table.


              Note...this method would use a relationship instead of a Case statement, so David's comment about "before best practices" is a good point to make.

              • 4. Re: Case Function Best Practice?

                -each quarter is the same for all students

                • 5. Re: Case Function Best Practice?

                  You need a table with two or more fields. One field is a text field with the name of the quarter and one would be a date field storing the date. You can then make updates by updating the records in this table once each school year.


                  You would not use case by itself to identify the quarter. You could use a relationship, executeSQL, or a script to determine the current quarter.

                  • 6. Re: Case Function Best Practice?

                    Something like this:


                    Start Date






























                    What would you advise for a script?


                    and thanks for the help...




                    • 7. Re: Case Function Best Practice?

                      Relationships and ExecuteSQL are simpler, but you asked for a script;


                      Go to Layout ["Quarters" ( Quarters ) ]

                      enter find mode [ ] --> clear the pause check box

                      Set Field [Quarters::StartDate ; ">=" & Get ( CurrentDate ) ]

                      Set Error Capture [on]

                      Perform Find []

                      If [ Get ( FoundCount ) = 0 // error ]

                         Show Custom dialog ["Quarters table needs to be updated" ]

                          Sort Records [Restore ; no dialog ] ---> Sort on StartDate in ascending order

                          Go To Record/Request/Page [First ]
                          Exit Script [Quarters::Quarter ]

                      End If


                      Perform Script can run this script and you then use get ( ScriptResult ) to get the Quarter. If this is not always going to use the current date, you can pass the date as a script parameter and replace Get ( CurrentDate ) with Get ( ScriptParameter )


                      You might also consider adding a school year field and simply add 4 more records at the start of each year if you then did a find (or a relationship or a Query...) that used the school year as part of your criteria.

                      • 8. Re: Case Function Best Practice?

                        If you think relationships are easier----I would be willing to give that a

                        shot---you are the expert.....and thank you again for helping....




                        • 9. Re: Case Function Best Practice?

                          In the table that's the context for when you need to know the quarter, define a date field. This can be a field with global storage and link it in a relationship:


                          LayoutTable::DateField >= Quarters::StartDate   (I've typed >= where in Manage database you have a single operator for this)

                          Specify that the relationship be sorted in ascending order so that the earliest start date that is less than or = to DateField is the first related record.


                          Then you simply set DateFIeld to the desired date--whether it be the results of Get ( CurrentDate ) or any other date you need to use and then a reference to Quarters::Quarter will return the Quarter corresponding to the specified date.