Hey @Alex
Thanks for the quick reply! It would help, if I wouldnât be to blind to find the aggregation function ! Where exactly is that?
Hey @Alex
Thanks for the quick reply! It would help, if I wouldnât be to blind to find the aggregation function ! Where exactly is that?
Hey @TomBo ,
I feel your pain, it took me a while to find it as well, itâs not super obvious and maybe something we should address UX wise!
@jose how do you feel about that?
Either way, the sum function is right here:
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.
Thanks so much
Hey
So in essence what you would want to do, is reference the sum
in another field, in another table?
That is not possible, the sum
you see at the bottom canât be referenced by anything.
Something that might help to understand is, that you need to treat these tables as database tables, not as excel spreadsheets. (if that makes sense)
Maybe there is some funky way to achieve something similar using formulas? Maybe @nigel can help out here and confirm if this is a possibility
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.
Thanks for helping guys!
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?
So you probably wonât have to use minus
in order to make this work, you can also just use simple operators like +
or -
.
So you can do something like this:
Hm I tried that aswell, that is my formular:
field(âUrlaubsanspruch 2022â) - field(âGenommener Urlaubâ)
But the field still remains blank:
Just to make 100% sure, did you press save
after editing the formula or did you just click outside of the context to close it?
Oh wow. Sorry That seemed to be the mistake haha
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
Thanks so much!
Hey there
So I ran this myself and it seems to confirm what you are saying.
In summary: when you sum on a lookup field it works. When you sum on a lookup field that looks up another lookup field, sum does not work anymore.
Thatâs very interesting.
@nigel do you know if this is a bug?
Hi unfortunately this is a known bug with lookups, a lookup of a lookup can no longer be aggregated and is partially tracked by this issue: Lookup of Lookup fields appear as JSON when used in formulas (#1027) ¡ Issues ¡ Bram Wiepjes / baserow ¡ GitLab
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â
New Day, New Question
I have the following Table:
Now First I have Test Times, which is basically just a link to table, to do lookups, second I have times, which lookups all times that I entered in a specific table and is linked to a certain employee. So EMP1 has 5 time entries, therefore lookup field âTimesâ shows me 5 different values. Lastly I have the field âBillableâ, which is a Bolean field in the original table and basically just shows if a time entry is marked as billable or not (If billable Bolean field is marked by a check sign). So you see there are 5 times, the first four are checked and the last one is not checked. Is there a possibility to sum only the times that are checked and if yes how?
I already summed all time entries, but I want to sum only the money that are marked as billable in the Boolean field as well.
Thank you so much and kind regards,
Tom
Hey Tom
This is possible by using filter
.
You can do something like this:
sum(filter(field('Times'), field('Billable?') = true))
Or in short:
sum(filter(field('Times'), field('Billable?')))
This basically filters
the field
Times
based on if Billable?
is true
and then runs sum
over the items remaining.
That should do the trick?
FYI, you can also do this without the lookups if you donât need the lookup fields in that table for anything else. You can replace field
in the formula above with lookup
and it will lookup the value from the other table using the formula.