Formula column invalid in Rollup

Good evening,
I just discovered a strange behavior…
I’m trying to setup a database allowing people to record their amount of volunteering hours. The hours should enter in several categories (cleaning, repairing, etc.), and I’d like to calculate the global amount of volunteering hours for each category. For this, I have 3 tables :

  • The first one with the categories (Table 1)
  • The second one with the families (Table 2)
  • The third one (Table 3) which keeps track of every volunteering time (each record contains the date, the family, the category and the volunteering time)

To calculate the global amount of volunteering hours for each family, it is quite simple : I just use in Table2 rollup('Table3','Hours Count','sum')

To calculate the amout of volunteering hours for each family in each category, it’s a bit more complicated :

  1. I added one column for each category in Table 3 with a specific formula. For example for cleaning : if({Table1.categoryName}==='Cleaning',{Hours Count},0) Like this, this column contains only the hours for the cleaning category.
  2. I added one column for each category in Table 2, and I’d like to calculate the hours count per category using a rollup on the columns I created just before in Table 3 (again for the cleaning example, I’ll have something like rollup('Table 3','Cleaning Hours','sum') ) but I get an error about invalid formula :frowning:

Indeed, I can’t see any “Formula type” column in the linked columns list, only “Number type” columns. If I created in Table 3 a “Number type” column with the same hours count as in my Cleaning Hours column, I can calculate my rollup but, as you can imagine, I don’t wan’t to duplicate every column by hand :joy:

  • Do you have any idea on how to solve this problem ?
  • Maybe it would be better to open a new “Feature requests” topic especially for this question but, if I keep this way of doing things, a addColumn script function would definitely be a great help to automatically create the columns in tables 2 and 3 corresponding to the different categories recorded in table 1 !

Thank a lot for your help and good night :sleeping:

Hi Benjamin,

thanks for providing the details and letting us know the difficulties you have encountered using SeaTable. May I ask which version are you using, cloud or on premise? If on premise, which version?

If it’s OK, would you provide some screenshots? I’m not sure how you have linked your tables 1, 2 and 3. Because calculating indirectly linked tables (A linked to C, B linked to C, so A and B are indirectly linked) is currently not supported, but will be supported in the next release, which is version 1.4 in the middle of October. And also in version 1.4, the if clause will be changed, too: ‘===’ will be changed to ‘=’.

Regards,
Meng

Hi Meng,
I’m currently using the cloud version.
Here are three screenshots (one for each table) which I tried to make as clear as possible by adding the formulas for every concerned column

  1. The categories table
    Categories

  2. The record table

  3. The families table

As far as I understand the problem, tables 2 and 3 are directly linked (both the Cleaning count and the Total count use rollup on the Record dates column, but only number type columns (Number type column with Cleaning column data and Hours Count respectively for Cleaning count and Total count) can be used in the rollup formula. If I try to replace Number type column with Cleaning column data (sorry for this far too long column name but I wanted it to be crystal clear :joy: ) by Cleaning column which is a formula type column, I get an error message Formula is invalid in the formula definition window.

Can’t wait for 1.4 ! even if for this particular problem, I’m not sure if the features you told me about could help me :thinking:

Bests,
Benjamin

Hi Benjamin,

thanks very much for your nicely made screenshots and the very detailed explanation :+1:! Now I understand totally what’s going on here.

Like said, calculating indirectly linked tables are currently not supported, although your tables Families and Hours record are directly linked, the calculation of Cleaning in your Hours record table is a synchronized result from the table Categories. SeaTable is designed to synchronize results in real-time, so that when independent variables are changed, the dependent values are changed automatically. So the results from a formula column cannot be treated as a fixed value, but will be calculated on-spot in real-time.

So when SeaTable calculates rollup('Record dates', 'Cleaning', 'sum') in the table Families, it has to find the real-time value of Cleaning, which is but related to the value Category name in the table Categories. As this value cannot be found due to indirect link, an error message Formula is invalid comes up.

Our sincere apologies for the inconvenience! Let’s wait for 1.4, as indirectly linked tables can be involved in one calculation by then. Can’t wait, too to see if this problem will be automatically solved by then :wink: (Oh, and don’t forget to change ‘===’ in your if clause to ‘=’ by then…)

thanks again for the report and the patience!
Best,
Meng

Hi Meng,
Thank you for your answer : I didn’t really understand the concept of indirectly linked tables after your first answer, but now I got it !
You really don’t need to apologize, SeaTable is an amazing tool, you can be really proud of your work !

I’ll let you know if 1.4 solves the problem :slight_smile:
Bests,
Benjamin

1 Like

Hi. I think I’m suffering from the same issue here, doesn’t seem to be fixed in 1.5 (unless I’m missing something!)

I have Table for time logs
startDate, endDate and Hours = hours({startDate}, {endDate})

Then in my subproject table I link the time logs and I’m trying to calculate the sum of hours for each project the formula always give me an error.
sum({Logs.Hours})

I have also tried rollup but no luck.

Any ideas what i am doing wrong? Many thanks in advance,

I can’t get the formula in Logged to calculate the sum of hours recorded in Logs for each subpoject. i hope that makes sense?

Hey @StuE, welcome to the SeaTable Forum!

Glad to see you are using SeaTable’s advanced functions! After carefully looking at your screenshots, I have to say, the problem you reported is not really a problem with “indirectly linked columns”, as it only occurs between your “Subproject” table and “Logs” table, which are directly linked.

And you are not doing anything wrong! SeaTable does not support quoting a formula result from another table yet. But as far as I understand our developers, it will be possible in a release by the end of this year. It’s a known issue - not a bug, but just a feature that’s not available yet.

Ok. Thanks for the reply.
There is no other way around this? Maybe something I can script via the api?

You are welcome, and - unfortunately, the SeaTable API presently only supports the manipulation of a whole row, not single cells. But the wait won’t be long!

Hi Karl

I have the same issue… I am trying to rollup a column which is a formula column in a different table. There is of course a linked column in my current table where I am trying to produce the rolled up result. The problem is that rolling up a formula column doesn’t work. I am using EE 2.5.5.

I can write a script, but, I would like to avoid having too many scripts. Are there any other workarounds?

Rolling up a formula column should work in the latest version. Maybe it is caused by other problem in your case.

The formula column I am rolling up from the other table (T1) also uses another formula column and a linked column in that table (both in T1). Could this be the issue?