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