Change dates in found set

[Nite_Phx]

Would like a scrip that advances the date of certain records in a found set. Simple database 1 table with 2 fields in each record, Date and New Date. The first field, Date, is already populated with dates, Mon and Thu dates. The 2nd field, New Date, is a user input field. The records are created in date order so no sort would be required. I'm trying to get the Date field to advance by the difference between the New Date field and Date field only for the records after and including the record that has an entry in New Date. Example:

Record 1 = 04/05/10

Record 2 = 04/08/10

Record 3 = 04/12/10 04/19/10

Record 4 = 04/15/10

Record 5 = 04/19/10

Record 6 = 04/22/10

After the script runs the dates in the Date field for records 1 thru 6 should read as follows:

Record 1 = 04/05/10

Record 2 = 04/08/10

Record 3 = 04/19/10

Record 4 = 04/22/10

Record 5 = 04/26/10

Record 6 = 04/29/10

Note that the Date field in records 1 and 2 don't change however the Date field in records 3 thru 6 changed.

I can get the dates to change however all the dates in all 6 records change. This really has me stumped. Following is one of the scripts I've tried that logically to me should work.

Go to Record/Request/Page [First]

Set Variable [$$Days_Ahead ; Value:0]

Loop

If ( New_Date - Date > 0 )

Set Variable [$$Days_Ahead; New_Date - Date

End If

Replace Field Contents [Date ; Date + $$Days_Ahead ]

Go to Record/Request/Page [Next; Exit after last]

End Loop

All help on getting this 'simply' date replacement script to work will be greatly appreciated.

[LaRetta]

Attached is an example. However, I'm unsure if we have all the information we need so we may need to adjust it, for instance ... what if User enters a date which is not a Monday or Thursday in the new Date field? And will there only be one record with a date in the NewDate field during this process?

Anyway, see how this works for you. There just wasn't enough of a pattern for me to nail it down perfectly. Let us know if it needs adjusting or you get stuck.

UPDATE: See adjusted file below. [color:red]Warning ... I realized that if there wasn't a new date (in this file), you could never exit the loop so I removed this file.

Edited April 12, 2010 by Guest

[Nite_Phx]

Hi LaRetta

Thanks for the quick reply. I was able to look at your attached example and comprehend what you did. Very interesting approach to advancing the date by 3 if the original date was a Monday and by 4 as a default. This script works as long as there are no breaks in the date sequence since the new date calculation is based on the previous records date and adding 3 or 4 to it. We'll need to adjust this so it holds the number of days difference between the old date and new date and then apply this difference to the current records date.

To the best of my knowledge there will only be Monday and Thursday dates and all dates should be sequential however people are highly unpredictable and we're better off covering the potential for non conforming dates and existing gaps in the dates.

The first loop to skip to the first record where 'new date' appears is a great help already. I plan on adding to the beginning of the script a simple routine to warn and perhaps disallow the running of the script if the 'new date' field is not a Mon or Thu. Any idea how to make non Monday and Thursday dates in the drop-down calendar non selectable and perhaps grayed out?

Thanks

Nite_Phx

Mesa, AZ

[LaRetta]

Well, I can't help but wanting to cheat and I'm trying it again ...

We'll need to adjust this so it holds the number of days difference between the old date and new date and then apply this difference to the current records date.

If each of the following dates needs to be increased then they all should be increased by the same number in relation to their current date (being adjusted periodically by the new date along the way). Old date 4/12 increased to new date 4/19 by 7 days so the next old date should increase by 7 and so on. This should work more easily and then, when hitting another adjustment date, jumping forward again (by the total of the two new date adjustments). Anyway, here's next revision.

Any idea how to make non Monday and Thursday dates in the drop-down calendar non selectable and perhaps grayed out?

Drop-downs and pop-ups can't be controlled this way (without plug-in) but you can use script trigger to constrain Users to entering a Monday or Thursday date in NewDate and make sure it is greater than OldDate. Calc might be:

PatternCount ( "25" ; DayOfWeek ( Self ) ) and Self > OldDate

Update: File replaced ... I found an issue where it will break if more than one NewDate and the dates overlap. I have fixed it (I believe).

AdvanceDatesREV1.zip

[Nite_Phx]

Your example file works perfectly. It even allows me to go forward and backwards with dates. Excellent. I didn't realize we would be stuck in a loop if there was no modify date but you took care of that as well.

Thank you very much for your expert solution.