Filtering cascading table widgets

[Bitello]

Hello guys!

I've exhausted my attempts to figure out how to solve a functional requirement on my own.

This is the requirement:

• Table A has a 1:n relationship with Table B which has a 1:n relationship with Table C.

• On one page, I need to display the 3 tables (using the table widget).

• When loading the page, all table widgets will be loaded with all data from their respective tables.

• If I select a row or more from Table A, Table B must be filtered by the corresponding foreign keys, and the same must happen with Table C, which will be filtered by the foreign keys with Table B.

• If I select one or more rows from Table B, Table C should be filtered by the corresponding foreign keys.

My problem:
Hello guys!

I've exhausted my attempts to figure out how to solve a functional requirement on my own.

This is the requirement:

• Table A has a 1:n relationship with Table B which has a 1:n relationship with Table C.

• On one page, I need to display the 3 tables (using the table widget).

• When loading the page, all table widgets will be loaded with all data from their respective tables.

• If I select a row or more from Table A, Table B must be filtered by the corresponding foreign keys, and the same must happen with Table C, which will be filtered by the foreign keys with Table B.

• If I select one or more rows from Table B, Table C should be filtered by the corresponding foreign keys.

My problem:

• I don't know how, when selecting a row from table A, update subsequent tables.

Could someone guide me?

Grateful

[andz-bb]

hey @Bitello

I built an example of this for you:

In order to enable this sort of filtering with Budibase DB relationships, you need to create an extra column on each child table that holds the _id of the related parent row. This is done by adding using a static formula that returns the _id from the relationship column like so:

In this example my table A is Employees, my table B is Jobs and my table C is Subtasks. In the above screenshot this is showing a static formula column I set up in my Jobs table which returns the Employee _id for each job. I can then filter my columns using this value.

I'm using standard table blocks and then adding an action on row click to set the selected row _id to the state.

I then use that value from state to filter the data on the table below.

three tables-export-1671109053798.tar.gz

I might have missed something here in my explanation so if there's anything that's unclear let me know! I've also attached the app export for you to try out and have a closer look. I hope this is helpful for you! 😊

[Bitello]

Hey, @andz-bb

Thanks for the solution, it was exactly what I was looking for.

But I still have a problem...

Loading your solution file, it works perfectly.
When I tried to implement it in my application, I couldn't use the "Update State" action...
(by the way, "Update State" is not yet documented, searching Google for the words "budibase" "action" "Update State" and I only found codes that use this action)

The keys appear in your application:

In my app, there are no options for keys:

I created a new application, with only two tables, summarizing your example, and still, it didn't work.
I'm using version 2.2.3, through docker-compose, in Ubuntu.

I tested it in a Windows 10 environment, also using docker-compose, and the result was the same.

Can you help me understand where I'm going wrong?

I am attaching my test application.

Test-export-1671150458985.tar.gz

[andz-bb]

hi @Bitello

The Update State key dropdown only shows keys that have already been used in the state. If you want to create a new key you just need to type in whatever key name you would like to use to represent the value. Then when you go to work with the state again either in actions or bindings you should see that the key is available. Sorry that you had trouble finding the state docs, the page for App State is here: https://docs.budibase.com/docs/app-state

[Bitello]

Fantastic!!!

Santa Claus exists!

These actions are very powerful!

However... I am using a Postgres database and when I try to create a new column of the Formula type I am not presented with the "Formula type" combobox (in BudiDB it is presented)

Also, I can't, in the same way shown in your solution with BudiDB, assign the _id of the row to the formula

Is the given solution restricted to using BudiDB?

[andz-bb]

hey @Bitello 🎅

yes the solution was designed with Budibase DB in mind, but using an external SQL table should make this easier. There's no need to create columns that return the foreign key _id as you should already have columns in your tables for that.

The correct solution in your case would be to set the row's id to the state - in your case I think that is Id for each table instead of _id which is what is used on Budibase DB. Then once that value is set on state you can filter the results on the child table where the Id stored on your state matches the foreign key.

Hope this clears things up but if you would like a specific example for your setup feel free to share your schema and I can demonstrate with that 😊

[LucianHorrobin]

Hi andz,
I'm trying to implement this functionality in my app and at first I thought I had it nailed.
I copied your example and then started to play with many to many relationships, to which i discoverd that if a job is assigned to two people, it wont be displayed in the jobs section. Additionally, if the job is removed from the emplyee in my app, the relationship persists as the added column for filtering is static (you cant filter by dynamic formula columns).

So, to overcome these problems, i rebuilt the app using supabase/PosgresSql, though when setting the _id as a state and then filtering the table i get the following error.

I've tried to figure out how to parse the app state string into an int, and pluck the data, though it is beyond my skills.

Ideally, i'd like to be able to click on the clients table and have the clients assets displayed and inversely click on the assets table and have the clients displayed

There must be a simpler way to achieve filtering by a relationship that i'm missing.

I've attached the app and if you could have a look at it i would be incredibly greatful, or just point me in the right direction.

Thanks

survey_supabase-export-1720924893193.tar.gz

[Bitello]

Worked perfectly!

Thank you very much!

This solution will be very helpful!