Filter with relationship picker

When using an external SQL datasource, such as MySQL, PostgreSQL and SQL Server, it is possible to use the Relationship Picker to filter related data.

The Budibase DB could be used in this context, however it is a NoSQL database and generally not ideal for extensive relationship functionality.

Tutorial

For this tutorial we will use PostgreSQL, and assume there are three tables with the following relationships:

• One Owner -> Many Pets
• One Pet -> Many Appointments

For a given owner, we want to display a list of appointments based on the chosen pet.

Follow this guide to see how to connect to your Postgres database, fetch the tables and define the relationships.

1. Setup the owner form

Set the Pet and Owner name column as the display column and create the autogenerated screen for the Owner table.

Next Eject the Table block. Select the 'Details' Form block, uncheck all fields except for the Pets relationship field, and also uncheck the Show save button and Show delete button settings. Finally eject the details form block, and once ejected rename the form to 'Details Form' and set the form type to Create.

2. Add the appointments

Within the details form, add a Headline for 'Appointments' and add a Divider below.

Next add a Repeater block for the Pets relationship.

Define filters for the repeater block using the Is in filter as follows:

🚧

Filtering on '_id'

The _id field represents a URL-safe encoded id string.
As relationship links in Budibase are stored in this format, this field must be used when filtering on a relationship picker value.

Finally, nest a Cards block within the repeater block. Set the datasource to the appointments relationship.

Set the title to the name of the pet: {{ Pets Repeater block.Pets.Name }}
Set the cards title and subtitle to the date and reason of the appointment, e.g. {{ Appointments Cards block.Appointments.Reason }}

The date can be prettified as follows: {{ date Appointments Cards block.Appointments.Date "dddd, MMMM Do, h:mm a" }}

Result