Creating a relationship to a period (i.e a year

[daspearce]

Hi, I am trying to create a list which will show entries from a table in a given year.

So I have a table where each entry has a date.

I thought I would create a period table with a start and end date (this is accounting, so using Tax Year)
Date_Start 6/4/2020
Date_End 5/4/2021

and so forth

and then use a relationship with a >Date_Start and a <Date_End to get the way to show each entry based on the year. I would then also create some fields to sum some values, as I am looking to show a tax allowance per year based on investments made.

Cant quite see how to do this as the relationship doesnt have a between function like in sql?

My end goal is to have a summary for each year which shows how much the sum of the investments is and show that as a list
2018/19 - £1232
2019/20 - £1432

Any ideas on the best way to acheive this. I cant think its too hard.

[Jeff_Hager]

Please vote:

Conditional Relations

I know myself and others have made this case before, but I’d like to mention it again. I think it would be hugely benificial to be able to apply IF conditions within Relation settings. My use case for this is to get the unpaid dollar amount from unpaid invoices. I have invoices for students that, let’s say, are billed monthly. The user creates the billing period for a particular month, and any lessons that have occured within that billing period are automatically included in that invoice. In…

I’ve been needing something like this for a very long time. I’ve done some very crude workarounds using secondary google sheets, or massive 180 column array columns that are populated via sheet formulas with every possible date within a range. I don’t know if I even understand how it works anymore.

As for your situation, is your fiscal year always consistent as far as the month and day? I wonder if you could have an additional column that gets populated with a fiscal year, such as 2018/19, or 2019/20 when the row is created, or maybe you can get creative with some math, template, and IF columns to dynamically build your fiscal year based on the date in the row.

I have a couple of similar cases. One is where I want to have a relation that finds similar related rows with a date prior to the date in the current row. In this case, I don’t have an exact list of dates to match up to. I need to compare to a range of dates. This is needed so I get get rollup columns to return the correct rollup value. The other situation I have is with a sheet of lessons that have dates. I also have another sheet with billing cycles that have a beginning and end date. What I try to do is dynamically link the lessons to the billing cycle, where the lesson date is within beginning and end dates of the billing cycle…and I try to link a billing cycle to the lessons, where the lesson date is within that beginning and end date of the billing cycle. Lesson dates and billing cycles can be modified at any time so I need those links to be dynamic. I can’t rely on hard setting a lesson to belong to a billing cycle because that billing cycle may or may not exist yet. Being able to put conditions on a relation would make this so much easier.

I think we share a similar need, but I think yours might be achievable.
Maybe you could have four math columns:

• one with YEAR(date)-1
• one with YEAR(date),
• one with YEAR(date)+1.
• finally one with MONTH(date)*100+DAY(date)

Then create two template columns:

• one that joins the -1 year and current year (ex. 2019/2020)
• one that joins the current year and +1 year (ex. 2020/2021)

Then create an IF column as follows:
IF month/day < 0604
THEN first template column
ELSE second template column

That might get you close to what you want.