Auto enter start date based on end date in a previous record

[warway]

Hi there,

I'm fairly new to Filemaker, but I'm pretty sure there is a solution to what I'd like to achieve. I'm trying to keep a log of hotel rates. The rates are valid for date periods and each date period follows consecutively. To speed up data entry and eliminate errors I feel there is really only a need to enter the end date for each rate period and the start date should be calculated from the previous end date e.g below:

Start Date End Date Rate 01/01/2012 15/01/2012 500 16/01/2012 31/01/2012 400 01/02/2012 20/03/2012 450

The initial start date would need to be entered, but after that the start date in the next record could be calculated from the previous end date i.e. previous end date + 1. I think there must be a way to achieve this with a custom function, but I've scoured the internet for a solution and can't seem to find one.

Any ideas?

[doughemi]

Make StartDate a date field with an autoentered calculation:

Let(

prevEndDate=GetNthRecord ( EndDate ; Get ( RecordNumber ) -1);

if(prevEndDate≠"";prevEndDate+1;"")

)

[warway]

Thanks, this works brilliantly!

[warway]

I've just realised that whilst this works perfectly when entering data, there is a flaw if you need to change the end dates. If you change the end dates, the start dates aren't updated automatically when using an auto entered calculation. If I swap the start date to a calculation field this works except for the very first record where the start date can't be calculated. Ideally I'd have an auto enter calculation just for the first record and then the calculated field for the remaining records. I don't think this is possible. Other than having 2 fields, which I don't feel is an elegant solution. Is there a better solution?