You might try using the following script and relationship:
TimeSheets::cWeekEnding = Weeks::WeekEnding (Weekstarting could also be used here)
Enable "allow creation of records via this relationship for Weeks.
Define cWeekEnding as:
Datefield - Dayofweek(DateField ) + 7 //plus 1 would give you the Sunday date instead of Saturday--the week ending date
Select Date as the return type. Weeks::Weekending is a date field.
Now this script step, when performed from a TimeSheets layout, will create a related record in Weeks if it does not already exist. If it does exist, it will make no changes to the data in your database:
Set Field [Weeks::WeekEnding ; TimeSheets::cWeekEnding]
Note: if you have specific individuals, each with their own time sheets that need their own records in Weeks, you can modify the relationship to match by an EmployeeID number in addition to the WeekEnding fields.
Although I thank you for your thoroughness, I think you've over-complicated this.
The weekending is already put in by the employee, so I don't need to calculate that. All I need is a new record to be created in the other table with the weekending date as entered; but only one per week, not one per timesheet.
Then the method still works, just use the weekending field you already have in place of my calculation field.
Very strange. I setup a script:
Set Field [Weekly Weekending; Weekly Timesheet::Weekending]
Set Script Trigger:
Weekly Timesheet::Weekending - OnObjectSave
If I create one timesheet with date 3/14/2012, I get a new weekly record created correctly.
But as soon as I create another timesheet with that same date, that weekly record is CHANGED to 7/10/2011! Can't figure out where it gets that date. It's not the first record in the database.
From what layout have you set up this trigger? A layout based on Weekly or a layout based on Weekly Timesheet?
If it was set up on Weekly Timesheet, try preceding the set field step with a commit record step.
Yes, fromt he Wekly Timeshet layout
I put Commit Record beore SetField
Now the first timesheet creates that odd date!
When you open a layout to Weekly and inspect the records do you find that a new record was created or did an existing record get modified?
The only thing I can think of is that your relationship matches to a different field than the one that you are editing.
I deleted all records in Weekly first, so it is a new record.
The relationship is absolutely correct: Weekending=Weekending
Then it should work. This is a very simple setup--which is wy this has me scratching my head over why it is failing.
Both fields ARE of type date aren't they?
Found the problem. Still had a AutoEnter:Lookup
Ok, now a weird addendum. I have a calculation field set to contain text when the weekly wages (based on the previously explored relationship) exceed the budget (in yet another table, global field).
If(Weekly::Wages>Insured::Budget;"Over budget by $" & Weekly::Wages-Insured::Budget;"")
Sum(Weekly Timesheet::Wages Weekly)
It isn't updating the text when hours are changed bringin the toal above or below the reference, even though I've verified that the wages field containing the total is. If I change records and go back, then the text is updated.
Whenever a layout fails to refresh as expected, try committing the record first. This can be done without a script by clicking a blank area of the layout. If that works, insert a commit record script step where needed.
From there, you can try Refresh Window.
Commit didn't work, Refresh Window did.
Thanks again...you are the wiz!