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.
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.
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.
Phil, this works perfectly Thank you.
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.
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 ]
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.
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 [....
I've invented a new field, MonthDate for storing the date of the first day of the month and for use in the relationship.