I am struggling with something that probably has a quite trivial solution. Here is my problem:
In the screenshot, you see different Names (P1, T1, J1), which are employees and on the right side below days are days, which are holidays taken within a period (First column Name). How do I Sum These days now, and only consider those belonging to a certain Employee?
I hope I explained my problem. Thanks so much and kind regards,
Tom
So there is one way in which you could achieve your desired goal.
You could create a filter, which filters for the employee you are interested in. So you could filter on the Employee field for P1 and then you would only see entries that have P1 as the employee. Then you can use the aggregation function all the way at the bottom of your table where you can select sum for the days field.
That should then show you the total amount of vacation days taken for the employee you have filtered for.
Thanks! Now at least I dont feel to stupid for not finding it directly
Another question - Do I have any opportunity of getting these sums in another Table? Now I used this table to calculate all the holidays and it will become more and more with time and I want these holiday numbers to go into another table, where I calculated the holidays of employees to deduct the days they already used from this number. I hope its understandable what I mean. Otherwise I can post another screenshot also.
Yeah, I am not really a programmer or anything so I mostly used Excel. Probably I still think in that logic to much. If there is a formular that could help me achieve this, that would be great. I tried some out, but it didnât work unfortunately. I was thinking maybe with some kind of combination using lookup() but I dont know how exactly.
Okay so I looked into this and there is a way to achieve this with a little bit of manual labor.
Here is a demo:
What you would have to do, is you would need to add a link row field to the table where you stored all the holiday entries. Then you need to add. a lookup field that looks up the number of days that you have entered in that related table.
Then you would need to manually add all the links for a specific employee to the link row field cell, so that you get a list of numbers in your lookup field that would represent each time that person has taken holidays.
Then you can add a formula field that calculate the sum of the list of numbers in the lookup field.
This is very hard to explain in text but I hope the above makes sense to you.
If you have any specific questions feel free to ask, I donât except anyone to understand what I am talking about
Makes Sense. I will try it out and give Feedback if it works, as soon as I come back in baserow (Unfortunately didnât save my password and now I am waiting for the Reset Link)
But this would also mean, that I would have to link new entries every time right? Like there is no automation that would allow me to add days automatically if a new holiday entree is made in the first list, right?
Yes thatâs exactly right, if you use the solution above you would need to manually add a new entry to your link row field every time a new entry was made in the holiday table.
That can be automated via webhooks, and for a developer implementing smth that does that would be very manageable but you already mentioned that you are non-technical.
If this is something you use for your company and you happen to have a technical person available then that person should be able to setup the webhook correctly
Otherwise, maybe there is some cool formula trick that I donât know of that can do this without manual work? I am a little doubtful, but @nigel has built our formula system so if anybody knows, then itâs him
Yeah, I will probably manage to find a technical person in our company, that can do that. Our Plan is to automise as much as possible with Baserow, of what we are currently displaying in Excel. So thatâs a good hint. Iâll let them know
It works! Now I am facing the next problem thoughâŚ
I successfully implemented the Vacation days taken in âGenommener Urlaubâ. Now I want to see the remaining vacation in the column âVerbleibender Urlaubâ, so I used the formular as seen here:
The formular seems to be right, at least I dont get an error, but the final output is still blank. Is that because I canât deduct formular fields from each other?
Hey, it 's me again. So I was able to further develop our model. We can now enter Holidays in one table and it automatically transfers them to our holiday calculation model via lookup fields. The fields look like this now:
So: We enter the Holidays in the Table âholidayâ Link them to the field Employees, so that every time a new holiday is entered the name and the numbers are automatically transferred to this table.
I also linked the Employee field to our Holiday Calculation to be able to deduct the sum of the days taken from the total holidays available, which are schon in the first field âverbleibender Urlaubsanspruchâ:
Now I tried summing these days with two different approaches:
First I tried to Sum the days looking up the values in our employees sheet using:
sum(lookup(âMitarbeiterâ,âUrlaub in Tagenâ))
Sum(lookup(âMitarbeiterâ refers to the employees table because Mitarbeiter is linked to employees and âUrlaub in Tagenâ refers to the days counted in this table. However, I get the following error: Error with formula: argument number 1 given to function sum was of type array but the only usable type for this argument is a list of number values obtained from a lookup or link row field reference.
Is it not possible to sum a field in another table via this function? This would be my favourite option, because then I dont need to create the same lookup fields in the holiday table, which I already created in the employee table just tot sum the days.
Anyways, I also I tried to use: sum(field(âUrlaub in Tagenâ)) to simply sum the lookup field which I already created in this table. However now I get the same error: Error with formula: argument number 1 given to function sum was of type array but the only usable type for this argument is a list of number values obtained from a lookup or link row field reference.
I dont understand why the sum funtion does not work here. I used it the exact same way before, and I checked the formular 10 times and canât see what is wrong. Maybe youâll be able to help me once again
The main workaround currently is in the intermediate table to insert a formula which does the sum in there, and then lookup that formula in the final table and sum it again, if that makes sense? This way you never have a âlookup of a lookupâ but in both tables a âlookup of a single value formulaâ