Filter table by relationship key

[timmehrees]

Hi All,

After a number of hours searching, and testing - I'm going to ask this one and hope it isn't answered elsewhere and I'm blind....

I am trying to create an editable table which only displays the records relating to another table, for example I am trying to show all of the order records related to one customer in table form, AND have it editable.

I can view all of the records in table form (through data providers and repeater) but cannot edit it.
I can achieve this by using the same set up, but instead of the table have form fields, having the fields default to the current value and having to save each line with a button - but this is extremely clunky.

I have tried to have a table show all of the records from the "order" table and filter by the relationship key/foreign key - however this always returns all of the records (even though I can see the correct data in the foreign key field in the database natively). This should be the most simple, but doesn't work. I can filter by any other field, so I assume this is to do with the relationship.

Any help or pointers would be great at this stage.

Budibase: 2.32.6
Database: MySQL 9 (latest docker version)

Thanks.

[ConorWebb96]

Hey @timmehrees,

A few questions for you on this.

• Do you not have access to the relationship fields when you attempt to filter your table data?
• Can you provide screenshots of your filter setup?
• Rather than the id, why not just use the customer name instead?

Below is how I've set up my table filters to get all users from a department.

Here is the filter setup that pulls back all employees based on department. I've manually entered the data here.

Here's the result of that.

We can take this one setup further if you'd like a more dynamic approach.

• Add a data provider. I'm using departments as the source
• Nest a form inside it
• Nest an option picker inside the form
• At the bottom, change schema -> data provider
• Amend the label and value. In my case, I'm using the same thing name and name
• Amend the filter so it references the value of the option picker instead now.

Filter changes look like this

End result

The table still has its crud functionality as well.

I hope this helps.

[timmehrees]

Hey @ConorWebb96 ,

Thanks for the reply, and the screen shots. Will answer your questions first:

Do you not have access to the relationship fields when you attempt to filter your table data?

The fields show up in the filter list to be selected, but even if I set the filter to show no matches when empty I get all of the records.

Can you provide screenshots of your filter setup?

It is a sports scoring app - Match defines the parameters, and score is obviously the event scoring - So one Match, many scores, and want to only show the scores for each match. Interestingly, the data in the fk field is not visible in the data tab on Budibase, but I can see it in phpmyadmin, and on the database container itself.

Rather than the id, why not just use the customer name instead?

The only other field that is unique to each match is a date field - and the format of that caused massive issues when I passed it through the URL - Although it did work if I selected it manually - So I suspect the option picker may work if I have to go to that solution.

Of interest - In the field picker, I get an "undefined" field for the match table - that can only be the ID field, as all other fields appear... I doubt this is a coincidence, and given your screen shot shows a ._id field on the related table....

Thoughts?

Thanks again for your help, really appreciate it.

[timmehrees]

Update time:

I went into the data view, and displayed the table in question. In the columns drop down I changed the foreign key field to writable (it had defaulted to hidden when I created the table)

Initially this didn't do anything - no data in the fields visible in the data view.

I then removed the link for that record to the other table, and re-entered it.... and now all of the records have data, and the filter works as expected on the CRUD table... insert what the emoji here

Anyway, problem solved - all works now to get on with it!

Thanks for your help @ConorWebb96