Filtering Relationship Columns

[PaulMcF1987]

I have moved my work from my own MYSQL database to the Budibase DB (I am hoping that the Budibase DB is reliable?)
I feel, from looking at the different options that budibase DB seems to offer more options.

I am struggling with relationship columns though. The relationship columns are a big part of the app for me as every job that I do involves a customer, service partners, both of which are selected via Relationship column/dropdowns and on occasions there are services/equipment etc that I need to reference via the relationship option.

This way, when I view the customers account or service partners profile, I can see the jobs that each were involved in.

The big stumbling block for me at the minute, though, is filtering.

I want to give my customers access, so that they can log on to the app and view all of the jobs that that are related to them. When I used my own tables I wrote a query and filtered the jobs based on the fk_A_B column
However within the budibase DB, this option is not available.
Via the data tab on the back end, I created a column view, Chose filter and entered a customer name, however, the table returned with zero values.

Is there a way to achieve the out come that I am looking for?

---

Also, within the app. If we get invoiced, there is a table showing all outstanding invoices and on our previous app, we would type the service partners name into a search bar and all of their outstanding invoices would be displayed.
I have tried search boxes and filtering but as yet have not been able to find a way to achieve this

Is this possible?

[cesarverdesslots]

It is not an answer. I have the same question.

I create the Salas table with a relationship column name Zona_Tecnica:

And when I tried to apply a filter with the Filter top right button, the Zona_Tecnica column doesn't appear as a filter option:

Beside this, Busibase let me press the Save button without select any column to filter the table and, if I does it, the Filter status is shown as active:

but there is really any condition set. I have to delete the empty row and save again in order to deactivate the Filter status

Show the Filter status as activate when it really isn't, it is a completely minor issue. The main one is to allow to apply a filter on a relationship field.

For example, I would like to filter the Salas rows with a Zona_Tecnica value equal to ZT_VALLE. Is it possible?

Thanks again!

[gerhardmpl]

You might look into #5661. While filter by relationship is not (yet, #2316, #6039) possible for the internal Budibase DB, the linked post shows a limited workaround. It is not perfect, but might be ok for you. Another option is to move to an SQL database like mysql, but than you need take care of relationship yourself.

[cesarverdesslots]

Thanks @gerhardmpl for your answer and the link to other discussions. I will study it in detail.

👍

[PaulMcF1987]

@gerhardmpl thanks for offering that solution,

I had tried the solution on offer before and due to it not working is why I opted to ask the question. I have tried it again but it still doesnt seem to work with using an external MYSQL DB

When I use the formula option and add he column to the filter, I get the error Unknown column 'JobRecord.Company' in 'where clause'

@cesarverdesslots did you have any luck?

[gerhardmpl]

@PaulMcF1987 I understood that you moved from your own MYSQL database to the Budibase (bb) DB, so all tables are now bb DB tables? I am not sure if the formlar option works with other data sources.

Not knowing your data structure and probably totally oversimplifing, let's assume you have a bb DB table jobs and another bb DB table partner (with a text field name as primary display). In the jobs table data section you create a relationship column partner (one partner – many job rows) showing the partner’s name, but you cannot filter by that relationship column.

If you add a static formula field search_partner with the JavaScript code return $("partner.0.name"), that name should be visible in the new column and you should be able to filter by that column.

[PaulMcF1987]

@gerhardmpl sorry, yeah in the question, I had moved over. But I found that the CouchDB functionality was not great and felt that it would be best to move back to MYSQL.

When I run the formula, the name does display in the formula column, but this column is essentially useless as a reference column as it does not work for filtering, cannot be used as a sort column...

[thinkbig1979]

This is an issue for me as well. Don't understand why filtering and searching is not possible for relationship columns in tables. This is pretty limiting in the UI.

I had also already tried the formula column approach before I found this thread, but unfortunately that was not a solution either.