How to work with related data in the same data source

Audience: Developers building Sapho micro apps

In Sapho, you can work with data in several ways ranging from very simple and straightforward use cases to much more complex data mashups. How to set up basic user flows based on related data is described below.

Display related data from the same connector

When working with relational databases, you can use the relationships among the database tables to display related data on the same page. For example, you can show a table of line items on an invoice detail page or a chart of individual expenses along with an expense report.


To set this up, you have to open up the detail page within Page Builder and the rest depends on the component you want to use.

List component

  1. Add a list component to the page.
  2. Switch on the Use Records Related to Detail Page setting in the List Properties.
  3. Select a database table in the DB Table setting that has a 1:n relationship to the primary database table of the page.

    The displayed records within the list will be filtered to only the related records from the selected table (those whose foreign key is the same as the primary key of the record displayed on the detail page).

Table component

  1. Add a table component to the page.
  2. Switch on the Use Records Related to Detail Page setting in the Table Properties.
  3. Select a database table in the DB Table setting that has a 1:n relationship to the primary database table of the page (those whose foreign key is the same as the primary key of the record displayed on the detail page).

    The displayed records in the table will be filtered to only the related records from the selected table (those whose foreign key is the same as the primary key of the record displayed on the detail page).

Chart component

  1. Add any type of chart component to the page.
  2. Switch on the Show Related setting in the Chart Properties.
  3. Select a database table in the DB Table setting of the X Axis that has a 1:n relationship to the primary database table of the page.

    The information displayed in the chart will be filtered to only the related records from the selected table (those whose foreign key is the same as the primary key of the record displayed on the detail page).

Write back a related record

Reading and displaying related data is one thing but, as Sapho supports data write-backs to primary systems, adding related records can also be accomplished with Sapho.

Since this scenario is best described by an example, the following steps show how you would add a line item to an existing invoice:

  1. Create an editable form page based on the line item database.

  2. Create a detail page based on the invoice database table.
  3. In the micro app you’d like to edit, go to Pages and click Invoice Line Item Edit Form. Under Number Input Properties in the right-side settings menu, please make sure the DB Column field is set to invoice_id.
  4. Under Formatting in the right-side settings menu, turn off Visible.
  5. Go back Pages and click Invoice Detail. Add a text field component on the page and under Text Input Properties in the right-side settings menu, set the DB Column field to invoice_id.
  6. Under Formatting in the right-side settings menu, turn off Visible.
  7. Add a button component on the Invoice Detail page. Under Action in the right-side settings menu, set Action field as Go to page and for the Page field, select your newly created Invoice Line Item Edit Form page.
  8. Click Set Fields under Populate Target Page.
  9. Set the Target page’s field to invoice_id and then select what value will be taken from the source page to populate the target page. In this example, [component value] and invoice_id.
    In other words, the [component value] of the invoice_id field will be taken from the source page (Invoice Detail) and used to populate the invoice_id field of the target page (Line Item Edit Form).