Jump to content

Setting a field value from date ranges


This topic is 2831 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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!!  :-)

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

This topic is 2831 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.