Rollup function with filter conditions based on formula columns

In a thread from last year it was mentioned that (if I understand correctly) it was not possible to filter a rollup function using the contents of a formula column in the linked table.

Has this been implemented yet by any chance?
Unfortunately I’m not having any luck with it. I can filter the rollup (sum) by a checkbox column for example, but not by formula columns containing text, numbers or boolean values.

Thanks!

1 Like

It was not possible by then because those tables were indirectly linked, which is now improved.

Your case looks different than that. Would you provide a screenshot, for example, for more details so we can better understand the situation and reproduce it?

Thanks for your reply!

I’ve created a test base with 3 tables:

Formula for column “Sold-Formula”:
if(countlinks('Buyer') > 0, 'Yes', 'No')

Formula für column “Sum prices (sold) (Checkbox)”:
rollup("Products", "Price", "sum", "{Sold-Checkbox}")

Table “Sales”:
The rollup formula above works fine (adding up the total price of all sold products per category using the checkbox column) .

But the checkbox column is a bit pointless, I just added it for testing purposes.

I’d like to calculate the total using the column Sold-Formula from the Products table. I’ve tried adding a new column using the following formulas, but they both return “Formula is invalid”:

rollup("Products", "Price", "sum", "{Sold-Formula} = 'Yes'")
rollup("Products", "Price", "sum", "exact({Sold-Formula}, 'Yes')")

After my original post, I did actually figure out a formula that seems to work and doesn’t require any of the Sold... columns in the Products table at all:
rollup("Products", "Price", "sum", "countlinks('Buyer') > 0")

But I’d still like to know why I can’t use the formula columns in the rollup condition :slight_smile:

Hi, it seems to me that you didn’t know we have a “link formula” column type that does exactly what you need? Just because we have “link formula”, we have removed the linking calculations from the formula column!

I have reproduced your base and have fulfilled the tasks simply by using the link formula columns:

To do so, just create the “Sum prices (total)” column like this:

And for the “Sum prices (sold)”, I have used just the checkbox as a filter condition:

I see - the “link formula” type does indeed do the job, thanks :slight_smile:

So I was expanding my test base with a new table “Invoices”, which might look something like this:

(the column types are not the intended end result, just for demonstration)

I was wondering if it’s possible to have 2 columns that link to other tables, but so that the selections available in the second column depend on what was chosen in the first column.

Let’s say I have dozens of product categories (too many to create a view for each one) and lots of products in each category.
Maybe there are even multiple products with very similar or identical names that belong to different categories.

What I imagined is: When adding a new invoice, I would first select a product category in the Column “Category” (for example Widgets). In the next column, I need to enter the product that was sold.

But instead of being presented with all possible products to choose from (Widgets, Sprockets, and everything else), I’d only like to see Widgets. I’ve already selected that category, so all products belonging to other categories are irrelevant.

Is there any way to achieve this? I’ve searched the forum and the docs, but I was unable to find anything like that.

Hi, yes, I love your question, because we just released SeaTable 2.2 several days ago and the “Cascade settings” for single select does exactly what you need.

How to do it? Just create your “Category” single select options, and then create another single select column “Product”. Then, in the menu of “Product”, select “Cascade settings”.

Then select “Category” as parent single select column and add options. The rest is as simple as it explains itself. Try it now! :wink:

3 Likes

The cascading concept is really neat, and exactly what I was looking for :slightly_smiling_face:

However (sorry for being a a pain), I was trying to build my cascading hierarchy in a different way.

I would call your example a “statically defined” cascade, meaning you manually selected the children belonging to each parent.

What I’d like to do is more of a “dynamic” cascade. The parents would be rows in one table, and each parent’s children would be defined by being linked to the parent in a second table.

To illustrate: I have created a new table in my test base listing only the various categories:

Categories

In the “Products” table, I have added a column (“Category/new”) linking to the “Categories” table:

Products (new)

In the Invoices table, the “Category” column type is now linking one row from “Categories”:

When I enter a new invoice, I select the product category in the “Category” column.
What I’m missing though is the next step: I’d like to have a column “Products”, where I can select the product that was sold.

But the products I get to choose from should be limited to the children (products) of the parent (category), as defined by being linked to that category via the “Category/new“ column in the Products table.

That’s what I mean by “dynamic” cascade. The hierarchy of parents and children would be built by links between tables, so you wouldn’t have to define those relations manually.

Thank you for your patience :slightly_smiling_face:

2 Likes

This type of hierarchy is not supported yet in SeaTable. It may be in the future, but there are not concrete plans. So please do not wait for it.

I also mark this topic as solved because we have strayed way off the original question.