Filter by 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 Owner name column as the display column and create the autogenerated screens for the Owner table.

Next edit the /owners/:id screen. Delete all form fields except for the Pets relationship picker and delete both buttons. Set the form type to 'Create'.

2. Add the appointments

Within the form add a headline for 'Appointments' and add a divider below.

Next add a repeater block for pets relationship.

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

Finally, nest a cards block within the repeater block. Set the datasource to the appointments releationship.

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.

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

Result


Did this page help you?