Date from next record calculation.

[Tpaairman]

I'm doing a budgeting page, and have two fields.  A Start Date and End Date.  The idea is let's say I want to show bills due between the 1st and the 15th, then the next record would be the 16th to the end of the month (let's just go with the 30th for easy math.)  I might need to adjust the starting date because the 1st might be on a weekend.  So, what I started with was a calculation in the End Date, to look at the Start Date of the next record, and subtract 1.  That is simply:

GetNthRecord ( StartDate; Get ( RecordNumber )+1)

The problem comes in with the last record, since there is no next record to look at, but I'd still like to plug in a range of dates for a few reasons.  I figured just add 15 days until later on when the next record automatically gets created (via a script).  So I figured have a Case statement that if the result was "", then use the Start Date + 15.

Case ( GetNthRecord ( StartDate; Get ( RecordNumber )+1)=0; StartDate + 15 ; GetNthRecord ( StartDate; Get ( RecordNumber )+1)-1)

But, I just get a ? in the field.

[comment]

I would suggest an entirely different method that does not depend on the next record (which could be not the record you expect, if not all records are found or if they are sorted in other than default order).

You could calculate the StartDate from a field that auto-enters a serial number* - for example, let's say your first record should represent the first half of January 2022. Here StartDate could be =

Date ( Div ( SerialNum ; 2 ) + 1 ; 15 * Mod ( SerialNum ; 2 ) + 1 ; 2022 )

and EndDate =

If ( 
Day ( StartDate ) < 16 ;
Date ( Month ( StartDate ) ; 15 ; Year ( StartDate ) ) ;
Date ( Month ( StartDate ) + 1 ; 0 ; Year ( StartDate ) ) 
)

which calculates the exact end of month where needed.

---
(*) For simplicity, the calculations assume that the serial numbers will start with 0.

 

[Tpaairman]

I appreciate the suggestion, but that does not work for what I need.  First of all, there is a script that automatically creates budget sheets a few months in advance, including entering the start date.  However, there will be times when the start date needs to be adjusted a day or two.  So the idea is that if you adjust the start date, the end date of the previous record adjusts accordingly. 

The other issue is that I had used the 1st to the 30th as my example here, however in reality it will be bi-monthly, and will be the 5th to the 19th and the 20th to the 4th of the next month, getting manually adjusted later as needed.

That said, there is no reason to do a find on this page, so no records would be omitted.  The page just has the start and end dates, and a couple other misc. fields, but the bulk of the info is in portals.  Also, there is a script that deletes sheets older than a certain time and creates new ones in advance.  So, you navigate to other records instead of searching. 

If the last record has a missing end date, and therefor things don't show up, it would be more of a very minor inconvenience, if that.  So it's more a case of me trying to figure out why I can get the date if a record exists, but I can't get it to do something if the field has no calculated result.

Edited March 23, 2023 by Tpaairman

[comment]

I still suspect that's not the best approach - but to answer your question as asked, you could do:

If (
Get ( RecordNumber ) < Get ( FoundCount ) ;
GetNthRecord ( StartDate ; Get ( RecordNumber ) + 1 ) - 1 ;
StartDate + 15
)

Note that the calculation field must be unstored.

The problem with your attempt (AFAICT) is that you seem to think that GetNthRecord() returns 0 when the record does not exist. In fact, it returns an error. I believe your formula would work if you changed your test to:

GetNthRecord ( StartDate; Get ( RecordNumber )+1)="?"

But I am not able to reproduce your result, so there may be something else at work here.

 

[Tpaairman]

I don't know what it's returning.  I did try ? in the case statement and it still didn't work, but I figured it wouldn't since, as you said, it's returning an error, and the ? is just it's way of saying there's an error.

When I set this up, I had first just entered the GetNthRecord function on it's own, just to see what it would do on the last record, and the field was blank.  So that's why I thought the case statement should be if the field ="", but it didn't work.

But that all aside, I plugged in the IF statement you just posted and it works, so Thank You for that. 

I understand what you're saying about how it might not be the best approach, but given the situation overall that it will be used in, it won't be a concern.

Edited March 23, 2023 by Tpaairman

[comment]

Just for completion, have a look at the attached file:

• The cEndDate1 field uses your original formula. As you can see, it does NOT show a ? in the last record (at least not on my system - perhaps you have deselected the "Do not evaluate if all referenced fields are empty" option?);
• The cEndDate2 field uses your original formula, modified to use "?" instead of 0.  As you can see, this DOES work as expected.

--
P.S. Please edit your profile to show your version and OS, so that we know what you can use.

 

test.fmp12

Edited March 24, 2023 by comment

[Tpaairman]

I tried the calculation again, and it worked, so I'm guessing there was something checked that shouldn't have been or something like that.  However, I did end up using what you suggested and it works, so Thank you for that.