Hi all, how can I do this?
I have a table with companies.
I have a table with a list of work done by all companies.
Those tables are related of course.
I do know how I can count all the hours worked by companies in total.
But the client has yearly seasons from Aug 1 - July 31.
How would you count totals per season? So 2021-2022, 2022-2023 etcetera?
Is that possible by formula or should I look for something else?
Thanks in advance!
Plus: is it not possible to choose a custom date in an If-ThenElse column? So if Date is on or before CUSTOM DATE ?
This is a little bit of a brute force approach, but it will work.
- Start with 3 math columns, let’s call them Previous Year, Year, and Next Year. In all cases you are replacing Date with your work record dates.
– Previous Year:Year(Date) - 1
– Year:Year(Date)
– Next Year:Year(Date) + 1 - Now create one more math column to get the month number from the Date
–Month(Date) - Next, two if-then-else columns. We’ll call them “First Year” and “Second Year”
– First Year: If Month Number is less than 8, then Previous Year. Else Year
– Second Year: If Month Number is equal to or greater than 8, then Next Year. Else Year. - And finally, a template column (let’s call that one “Financial Year”) that combines First Year & Second Year
Here is what that would look like:
Once you have the above, you can use the “Financial Year” column in a relation column, and then do whatever rollups you need through that.
2 Likes
Great, that helps a lot!
@Darren_Murphy Great, I have this up and running AND I understand what I have been doing, that’s great 
But I need a little extra help. Sorry to keep on asking. Expert beginner
I have companies with a relation to the work they’ve done. So in a column I can make a sum of all the hours. How would you filter only the hours of say Financial Year 2021-2022 ?
1 Like
Before I answer, can you describe the user experience you are looking for?
Do you want the user to select from a list of companies and a list of financial years, and then see results according to their selection, or…?
Every company has and sees only its own page. On that page I want them to see the number of hours and money per year + sum they put into various projects. On that page they have a list per year as well with detailed information per project. So it’s an overview page: what did we do each year and how many hours and how much money was in total that year involved.
The admin has a view on the data of all companies so can see that info per company as well.
Okay, I see.
So what you could do is modify that last column (the template column) so that it also includes the Company ID. Then the results for each financial year will be company specific.
If you wanted to show the results for all years in a single table - say on the company details screen - then what I would probably do is create a helper table with one row for each financial year. You could make that dynamic so that (for example) it always shows the past 5 years. Then when a user views a company details page, set the company ID in the helper table and use that to build a template + relation. This would give you the results for each year on a separate row in the helper table, so it would be easy then to plug those into a table or chart.
Thank you, I’m going to try that!
Here is how you could set the helper table up:
Thank you very much, I will look at that closely!
But first I go speedskating for a bit Every sunday.
1 Like
@Darren_Murphy I am getting there, but one more (well for now ) question.
I am now trying to add the CompanyID to the workrow. So I Lookup that CompanyID through the relation. But then I can’t see that Lookup in a template. I never understand why not, but how can I change such a lookup into something readable for a template?
Sounds like you might have a multiple relation. If you want to use a lookup value in a template, then the lookup needs to be sourced from a single relation.
But… if you add a Single Value → First → Whole row column to your Companies table (that takes the first row from the helper table), then you should be able to set the CompanyID directly through that single value column.
Ah! Didnt realize the multiple issue, single works!
Plus: when I try to set a custom value for the action in the companies list, how do I get to the helper table? Cause that’s where the Company ID needs to go right?
And please let me know when I’m asking too much! I feel guilty!
Here…
Don’t feel guilty at all. I enjoy helping 
1 Like
Actually, let me ask you a question: How do users get to the company details screen? Do they navigate to the details screen from a list of companies, or do you have a tab that opens straight to the details screen? Or are both possible?
Depending on how you get to the company details screen, you might need to use different methods for setting the CompanyID in the helper table.
There are two types of users.
- Users that are part of a company login and only see their company information. They cant choose other companies. They just get as is.
- Admins that see other tabs than the normal users and can visit pages of all companies. They see a list of companies and click on them to get details.
Okay, for this case you probably won’t have any easy way to set the CompanyID column in the helper table. But, you should know from the User Profile which company the user is associated with, yes? Perhaps you already have the CompanyID stored in the user profile?
For this case, you can use that Single Value → First → Whole Row column in your Companies table. When an Admin selects a company from the list, your action should do two things:
- Set Column Values, via the Single Value to the helper table, setting the CompanyID
- Show Detail Screen → This item
Then you can make both of the above work together by adding an if-then-else column in your helper table:
- If (user specific) CompanyID is empty, then User Profile → CompanyID
- Else (user specific) CompanyID