Databases

You can use the Database forms tool to build and edit database forms that collect and store data which can be used to fill out a database field in an event or profile form. Database fields are a custom field that allow people to choose a record from a searchable database. The database can contain hundreds or thousands of records, which can make a database field a better alternative to option field types in some circumstances.

Each record can contain associated information. For example, a strength database may contain hundreds of exercises, each of which can have additional information about the exercise. A food database could contain macro and micro-nutrient values for thousands of food items. With appropriate data permissions, users can use the database field to interact with the database and add, edit or delete database records.

Database forms do not have the same range of management options as those available for event forms. It is not possible to migrate fields or options for a database form. From the database forms screen you’re able to:

• Use the Add/edit records option for managing the contents of a database.
• Change the name of a database form using the Edit name option.
• Delete a database form, including all database records.
• Duplicate a database form. Creating a copy of a database form does not duplicate the database records.
• Resave a database form to recalculate any calculations in the existing database records.

Changing the name of a field or its options will result in data loss in the database. Any values would need to be added by editing the database record. If the field or options are populated into manual entry type fields in the event form (see Referencing a Database), data will not be lost from those fields.

Steps to build a database form

1. Log on to the builder interface.
2. Select the Database forms tool.
3. Select New database form from the Options drop-down menu.
4. Name the database form.
5. Leave the name of the section blank (delete New section from section title).
6. Add appropriate fields to your database form. 
   • The first field you add will be the search term for the database.
   • Add the additional fields for the information you want to store with the database record.
   • It is possible to filter the options available using one of the fields on the database form (e.g. reduce the amount of options by specifying the allergy type the user is looking for on an allergy database). To do this you need to check the Is a group field on the field settings.
7. Save the database form.
8. Your database can now be referenced from a form.
9. You can now populate this database from the database form management options or the database field in the user interface.

Referencing a database in a form

Once a database form has been created, the database can be added to an event or profile form. You can do this by adding the Database field type to the form and selecting the required database in the advanced properties menu.

Note that people must have data permissions for both the event form and the database form.

Likewise, for the event form to be fully functional for Athlete app users, the event form and the database form must have their advanced properties set to enable access from the Athlete app.

Populating multiple fields from a database in an event or profile form

During data entry, when a database record is selected in the event form, any fields in the event form that match those in the database record will be populated automatically. They must be the same field type and have the same name.

In the example below, a purchase order event form has been set up with a database field to list the available items for purchase. Note that the database form contains a field named Price, as does the table in the order form. When the backpack item is selected, the database populates both the Item and Price fields in the table.

Steps to reference a database in an event or profile form

1. Log in to the builder interface.
2. Select the Event forms or Profile forms tool.
3. Open or create an event or profile form which needs a database field.
4. Add a Database field from the custom menu.
5. In the Advanced properties tab, select the database you need using the Database property.
6. If the database has multiple fields that should populate in the event form, add the same fields after the database field. The field names and options should be exactly the same as they are in the database form.
7. Save the event or profile form.

Populating a database

There are several ways that database records can be changed or added. 

• As a builder, you can add database records using the Database forms tool. Each database has an Add/edit records button next to it in the list of database forms. When you select this option, you'll be able to view all records for the database. Use the Options dropdown menu to create new records by filling in the database form and saving each record. 
• When someone views a database field in an event or profile form and has Write permission for the database, they see a button in the database popup window called Add, which lets them fill out and save a new record for the database.
• People viewing a database field in an event or profile form with Write permission for the database can also see the View all button in the database popup window. This button takes people to a similar page to what the builder sees when using the Database forms tool in the builder interface, and they can use the Options dropdown menu to create a new record.
• Additionally, people who have Write permission for a database see the Import option in the Options dropdown menu. Like importing data into an event form, they can upload a CSV file with new database records.

You can read the article on using databases for more information about how people can interact with a database, including importing records into a database.