Skip to main content

Formula to lookup values from another table

  • December 31, 2021
  • 2 replies
  • 32 views
A
Forum|alt.badge.img+17

Hi

I have two tables

    • Transactions - with a date
    • 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!

Any ideas or suggestions very welcome.

Thanks,
Andrew

B

2 replies

W
Forum|alt.badge.img+12

Hi @Andrew_Davies,

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?

Thanks,
Chris

A
Forum|alt.badge.img+17
  • Andrew_Davies
  • Author
  • Inspiring
  • January 2, 2022

Hi @Andrew_Davies,

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?

Thanks,
Chris


Thanks for your answers - much appreciated.

I worked a way around it. My “Year 1” runs from anytime up to the 31st May 2022. Then years 2, 3, 4 etc run from 1st June to 31st May each year.

So I did a formula that deducts 6 months off the date, then took 21 off just the year value. A bit of concatenating after and I have Year 1, Year 2 etc.

A bit clunky - but works! I took a look at scripts - but this worked well.

Thanks so much again,
Andrew

W
Terms & ConditionsAccessibility statement