Hi,

 

Just a newbie starter here and was hoping for some help setting up a particular field to change its value by sets of date ranges.

 

For example:

 

I have a table with 3 fields, Serial_ID, Season (S15 thru S18), and a long name field... for example S16 would be 2016. Based on the current date, I would like to change the Season field. Any help would be appreciated, thanks.

 

8/2/2014 to 8/1/2015 = S15

8/2/2015 to 8/1/2016 = S16

8/2/2016 to 8/1/2017 = S17

8/2/2017 to 8/1/2018 = S18

Hi, you might try this, calculation result is number and set storage options to unstored, checking 'Do not store calculation results':

Let ( [
d = Get ( CurrentDate ) ;
moDay = Month ( d ) + Day ( d ) / 100 
] ;
"S" & Right ( Year ( d ) + ( moDay > 8.01 ) ; 2 ) 
) 

I changed the calc a bit.  It is a bit strange to begin a fiscal or a season on the second day of the month.

 

And welcome to FMForums!!  :-)

Based on the current date, I would like to change the Season field.

 

I don't understand your question. Why would the season change "based on the current date"? And in which table would that happen? Surely not in the table you have just described (and which seems kind of redundant)?

 

If your fiscal year always starts on August 2, and you want to calculate the fiscal year in a table where each record has a date field - e.g. a table of Transactions, where every transaction has a TransactionDate field - then you can do this quite simply by =

Year ( TransactionDate ) + ( TransactionDate ≥ Date ( 8 ; 2 ; Year ( TransactionDate ) ) )

This will return 2015 for TransactionDate of Aug 1, 2015, and 2016 for TransactionDate of Aug 2, 2015.

 

 

If you prefer, you can use =

Let (
y = Year ( TransactionDate ) + ( TransactionDate ≥ Date ( 8 ; 2 ; Year ( TransactionDate ) ) )
;
"S" & Right ( y ; 2 )
)

to get the results of "S15" and "S16" respectively.

 

 

These calculation do not depend on the current date, and can (and should) be stored.

I saw it as the need to identify new records created - according to the date they are created on, so it adjusts through the years.  But you are right ... we would not want an unstored calculation.