Periods of time - with a Title, Start date and End date. ie “Year 1” runs from 1st April 2019 to 31st May 2020, “Year 2” runs from 1st June 2020 to 31st May 2021.
I’d like to have a field in the transactions table, which looks up from the periods of time table, which year a transaction took place. So if the transaction was on the 1st July 2020, it would be in the Year 2 fiscal period. If it took place on the 1st May 2019, it was in the Year 1 fiscal period.
I know I could write that with a long IF statement, but that is cumbersome!
If my fiscal periods were whole years, (ie 1st Jan to 31st Dec) it would be so easy!
Do you want the two tables to be linked automatically when a transaction is recorded? This is my assumption. It would take a script to run on the transaction table to link any unlinked record with periods. A general sense of the script would be to identify unlinked records, identify period for transaction for any given record and then link them. Sorry if that sounded like a “duh” statement.
A workaround, if you didn’t want to write a script, is to do the if statement in transactions to identify the period and then link manually. Yeah, it would probably be more annoying to write than not but there may be a way to write it where there isn’t a bunch of nested ifs.
I noticed the begin dates for the two years you listed are different. Is this the case or just unique to the first year?