8 Replies Latest reply on Nov 8, 2011 8:26 AM by philmodjunk

    Creating records in related tables

    DennisPrickett

      Title

      Creating records in related tables

      Post

      I have a database used for physical therapy documentation.  There are three tables (actually several more, but these are the relevant ones for this problem): "Client Main," "Daily Visit," and "Weekly Visit Summary."  Each of the Visit tables are linked to the Client Main via a Client ID# relationship.

      Each day, a new record is created in the Daily Visit table for each scheduled client.  For each client there may be one or as many as five visits in the week.  For each client that is seen during the week, a single record is created in the Weekly Visit Summary.  The Weekly Visit Summary table has two date fields: Week Start Date (always Monday)and Week End Date (always Friday) .  Currently, I manually create this record at the end of the week by looking at a listing of clients seen that week.

      I would like to automate the process if possible, so that when a record is created in the Daily Visit table, a record on the Weekly Visit Summary table would be automatically created and the Week Start Date and Week End Date fields auto-populated.  In the Weekly Visit Summary, there can only be one record per client per week, so when a second record is created in the Daily Visit table in the same week, there would already be a record in the Weekly Visit Summary table for the week for that client.  I think that I could probably come up with a script to be run at the end of the week to create the records in the Weekly Visit Summary table, but my preference would be have the records created automatically as daily visits are created, as it would streamline my documentation process.

      Is there a way to create these records automatically, and populate the date fields in the Weekly Visit Summary?

      Dennis

        • 1. Re: Creating records in related tables
          philmodjunk

          Perhaps you don't need the weekly summary table at all. A summary report can be setup to produce such data without needing an additional table to do so by generating the needed report from your daily visits table, but grouped and summarized by week so that you see only one row in your report for each week.

          Given the current structure and relationships, you'll need a script either way. Either a "batch" script to run at the end of the week or a "by visit" script that runs each time you exit the daily visit portal.

          Using the OnObjectExit trigger on that daily visits portal, you might set up this structure:

          Add this unstored calculation field, cThisWeekStart, in Client Main:

          Let ( Today = Get ( CurrentDate ) ; Today - dayofweek ( Today ) + 2 )

          This produces the date for Monday of the current date's week each time.

          Now use the duplicate button (two green plus signs) in Manage | Database | fields to create a new occurrence of Weekly Visit Summary and link it to Client Main like this:

          ClientMain::ClientID = ThisWeeksWeeklySumm::ClientID AND
          ClientMain::cThisWeekStart = ThisWeeksWeeklySumm::Week Start Date

          Enable "Allow creation of records via this relationship for ThisWeeksweeklySumm.

          Now your script can be a single line:

          Set Field [ThisWeeksWeeklySumm::Week End Date ; Let ( D = Get ( currentDate )  ; D - DayOfWeek ( D ) + 6 ) ]

          This enters the correct week end date and the relationship auto-matically enters the client number and week start date dates. If the record already exists, this record already has these values and nothing is changed. If the record does not exist, this step creates it.

          • 2. Re: Creating records in related tables
            DennisPrickett

             I'll look this through and see if it will do what I need (usually, if Phil is answering, then it's right on target!).  On the Weekly Summary I have a text field in which I summarize client treatment and progress for the week - information which is not necessarily contained in any of the Daily Visit records.  So, I think I need the separate table for that reason.

            Dennis

            • 3. Re: Creating records in related tables
              philmodjunk

              Sounds like a good reason for that added table--and the reason why I didn't limit my response to the first suggestion here. You could enter such a weekly summary into the text field of the last daily visit record for that week and still see it in such a report, it seems 'tidier' to keep the related table for this.

              • 4. Re: Creating records in related tables
                DennisPrickett

                 Phil, this works perfectly  Thank you.

                Dennis

                • 5. Re: Creating records in related tables
                  DennisPrickett

                   

                  OK, I thought that I could use what you provided to do an additional step but just can't figure out how to do it. 

                  In addition to doing a weekly summary, I also have a separate table for a monthly summary.  The monthly summary would typically be done one month following the first visit with the client, and then again one month following if the client is still receiving services. I think that I could use the same trigger to create the record in the monthly summary table using a second line in the script, but in this case it would only happen once a month instead of once a week.

                  I created a new occurrence of Monthly Visit Summary and linked it to the Client Main like this:

                  ClientMain::ClientID = ThisMonthsMonthlySumm::ClientID AND ClientMain::cThisMonthStart = ThisMonthsMonthlySumm::Month Start Date and enabled "Allow ....." for ThisMonthsMontlySummary

                  I understand how to modify the setup for the different tables, but I'm having a problem with the calculations and scripts:

                  I need the start of the current month (cThisMonthStart) to be calculated using the first visit date with the client, so it's not the same as calculating the first day of the week.  I can easily pull the start date of the first month from Evaluation::EvalDate, but this won't help with calculating the beginning of the following months.

                  Also, I'm not sure how to format the script to add the record to the Monthly Summary table when the first daily visit of the treatment month is created.

                  • 6. Re: Creating records in related tables
                    philmodjunk

                    cThisMonthStart can be computed as:

                    DateField - Day ( DateField ) + 1

                    You can use Get ( CurrentDate ) in place of date field for the first day of the current month, or you can use a datefield that refers to a date already entered to compute the date of the first day of the month.

                    Date ( 1, Month ( datefield ) ; Year ( Datefield ) )

                    produces the same result.

                    The same script that uses Set Field to add a new record (if one does not already exist), can be modified to include a second set field that sets some data in your monthly summary table in much the same fashion. If you don't have a monthEnd field (and I don't see the need for it), it might be simply:

                    Set Field [ThisMonthsMonthlySumm::Month Start Date ; // put an expression using one of the above methods here ]

                    • 7. Re: Creating records in related tables
                      DennisPrickett

                       I may have confused things a bit -

                      I need for the cThisMonthStart calculation to be the first visit date, not the first day of the month.  So, if I see a client today, the beginning of the month of treatment of that client will be 11/8/2011, and the beginning of the second month of treatment would be 12/8/2011. 

                      It seems that the DateField - Day ( DateField ) + 1 returns the first day of the month.

                      I can just use cThisMonthStart = Evaluation::EvalDate ,    but I don't understand what would happen at the beginning of the second month.

                      • 8. Re: Creating records in related tables
                        philmodjunk

                        Yes, the calculation is for the first day of the month. Add such a date field to your monthly summary field and use it in your relationship. Then set up the set field step to assign the "first visit date" to your existing field.

                        You may need to use scriptsteps inside an If step in this case to make sure that subsequent visits don't overwrite the existing date in this field:

                        If [ Not ThisMonthsMonthlySumm::MonthDate ]

                            set Field [....

                        End If

                        I've invented a new field, MonthDate for storing the date of the first day of the month and for use in the relationship.