Datasets can be created with a parent/child relationship. A standard example of this is the Organizations and Organization Contacts dataset. An Organization Contacts dataset record can relate directly to an Organizations record, and Organizations data can be used for querying within the Organization Contacts query base (for example, a query for Organization Contacts records that belong to Organizations records with a category of "High School"). 

Custom datasets can also be configured in this way. Common examples would be CBOs/CBO Contacts and Churches/Church Contacts.

Creating a Child Dataset

To create a child dataset, refer to the procedure in the Creating a Custom Dataset article, with the addition of setting the appropriate parent dataset within Database > Datasets.

Creating the Related Dataset Row Field

Parent/child datasets relate to each other using a very specific kind of field called a "Related Dataset Row."

To create this field:

1. Select Database on the top navigation bar and select Fields.

2. Select Insert.

3. Enter the following User configurations in the dialog box:

   • Status: Set to Active.

   • Dataset: Select the parent dataset from the list.

   • Scope: Leave as Person.

   • ID: This is the code that Slate uses to store fields in the database, and most administrative staff will not see the value. It is a best practice to create this ID with all lowercase letters and using no spaces or special characters other than an underscore. Once this ID is set and in use, do not change the value.

   • Name: Give the field a name. This name is only visible administratively. It is acceptable to change the name, even if data already exists for the field.

   • Value: Set to Store Value.

   • Multiple: Set to Multiple Values.

   • Ordered: Set to Unordered Values.

   • Appendable: Set to Replace existing values upon form submission or dataset import.

   • Data Type: Set to Related Dataset Row.

   • Related Dataset: Select the newly-created child dataset from the list.

4. Select Save.

Creating the Child Dataset Query Base

Due to the required related base and join configurations, the child dataset query base must be created manually, rather than by adding it from the Slate Template Library.

1. Select Database on the top navigation bar and select Query Bases.

2. Select Insert.

3. Enter the following User configurations in the dialog box:

   • Status: Set to Active.

   • Order: Enter an order value lower than that of the parent dataset query base.

   • Key:

     d_contact.[id]

   • Enable Lookup: Set to Yes.

   • Enable Query: Set to Yes.

   • Enable Reader: Set to No.

   • Related Bases: Copy the GUID of the parent dataset query base and paste it into this box, surrounded by tags.

   • From Clause:

     [dataset.row] d_contact

   • Join Clause:

     left outer join [field] d_contact_rel on (d_contact_rel.[related] = d_contact.[id]) and (d_contact_rel.[field] = 'ID OF RELATED DATASET ROW FIELD')
     left outer join [dataset.row] d on (d.[id] = d_contact_rel.[record]) and (d.[dataset] = 'GUID OF THE PARENT DATASET')

   • Where Clause:

     (d_contact.[dataset] = 'GUID OF CHILD DATASET')

   • Read Permission: Set the read permission as desired.

   • Dataset: Select the child dataset from the list.

Further Configurations

After following the instructions in this article, the dataset, related dataset row field, and query base for the child dataset will be configured.

To show the appropriate child dataset records on the dashboard of the parent record, consider creating an Embedded Dataset Row Query.