My approach would be like this.
Set up a table with a single record and a field called Automation Focus with numeric fields called [Year] and [Record ID# of Parent Record]
Set up a table called 53 Weeks with exactly 53 records.
Make a relationship where 1 Year record has many weeks based on a formula reference field with a formula of 1. Lookup the [Year] and the [Record ID# of Parent Record] down to the 53 weeks table.
Make a formula date field in weeks with the following formula
var number YYYY = [Year lookup];
var date WeekRaw = FirstDayOfWeek(FirstDayOfYear(Date($YYYY,1,1))) + Weeks([Record ID#]);
If(Year($WeekRaw) =$YYYY, $WeekRaw)
Then make a saved table to table import from 53 Weeks into the child table, mapping the [Record ID# of Parent Record] into the field [Related Parent]. On that import filter where the Week is not blank.
On the parent record presumably there is some kind of Date field. One way or the other, maybe by direct data entry, get the Year onto that record.
Create an Automation triggered by say a checkbox being edited on a Parent record.
Step 1. Modify the Automation focus record to copy in the Year and the Record ID#]
Step 2 Run the import.
That should do it! Post back if you get stuck or contact me for direct one on one assistance.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.commark.shnier@gmail.com
------------------------------
