COVID-19 Test capacity

This is a sample template of a Form that can be used to collect information about the test capacity for COVID-19 for each medical institution in a country. The test capacity depends on people and material available in each medical institution. The Form can be used to collect daily data about the availability and shortages of such resources.

You may need to collect data related to the resources available in your country or in medical institutions to evaluate the test capacity they have. Test capacity does not only depend on the number of tests available.  It depends on the materials used during the administration of the test (e.g. cotton swabs to collect samples, protective equipments for people who take the samples), the analysis of the test (e.g. laboratory equipment, liquids and filters for analysis) and also on the personnel available in each stage of the process (e.g. personnel to conduct the test, to analyze the results, to deliver the results, etc.)

In this example we will use a Reference Form to collect all the medical institutions and their geographical location in one place to make sure that all users reporting use the same names for the medical institutions participating in testing for COVID-19. Then we will add a Form with a Subform to collect daily data per institution. We will link the Form to the Reference Form.

You can use the Form and Subform Schema below to directly import the fields to a new Form and Subform and save time. You can also duplicate a Form to quickly create similar Forms if needed.

The example uses dummy data.

Add a Reference Form

To make sure that all users reporting use the same names for the medical institutions participating in testing for COVID-19, you can create a Reference Form with a Text field and add all the medical institutions of your country as Records.

By making that Text field a Key field you can then refer to it in your Form and users will be able to select from the list of available institutions the institutions for which they report. In the Reference Form you can also reference ActivityInfo’s built-in Geodatabase to link each institution to a geographical location.

  • In the Database Design section, click on 'Add form' to add a Form.
  • Give a name to your Form and add a Reference field to reference the Geodatabase of ActivityInfo and a Text field for the name of the institution.
  • Make the Text field a Key field so as to link to it in the next Form.
  • Save the Form.
  • In the Table View page, click on 'Add record' and add the first institution of your country. Click on 'Save record' to save it.
  • Repeat the same process to add all the medical institutions participating in the testing. If you have a list of institutions available you can import them as Records to this Form.
  • On the URL of the Table View page you can view the Form Id of this Form. If you want to import a fields list instead of designing a Form using the Form Schemas at the end of the article, you will need to use this Id.

You can change the visibility settings of the Form to Reference data to make sure users doing data entry don't get confused with it.

Add a Form

The Parent Form will link to the list of medical institutions. Users will be able to select the institution for which they are reporting from the list. We will add a Subform to the Parent Form to collect daily information about each institution.

  • In the Database Design section, click on 'Add form' to add another Form.
  • Give a name to the Form and add a Reference field. Select the Reference Form that you created in the previous step. Make that field a Key field. By making this field a Key field you prevent users from adding duplicate Records and ensure they will add all data related to the selected institution in the same place.
  • Add a Subform field and give it a descriptive name.
Add a Subform

We will add a Subform to collect information about the daily numbers of material used and personnel occupied for the various stages of COVID-19 testing.

  • Click on the Subform to open it.
  • Add a Date field and make it a Key field. By making this field a Key field you prevent users from adding duplicate Records for the added date.
  • Then add Quantity fields with Codes. We add Codes to be able to do automated calculations based on the numbers collected.
  • In this example, we have added the following Quantity fields, you can adjust these to your needs.
    • Label: Cotton swabs used- Code: CU - Unit: Cotton swabs
    • Label: Packaging material used - Code: PU - Unit: packaging material
    • Label: Personal protective equipment for samplers used - Code: EU - Unit: personal protective equipment
    • Label: Liquid units used for analysis - Code: LU - Unit: liquid units
    • Label: Filters used for analysis - Code: FU - Unit: filters
    • Label: Analysis equipment units used - Code: AU - Unit: analysis equipment units
    • Label: Personnel doing tests - Code: PET - Unit: people
    • Label: Personnel transferring the tests - Code: PT - Unit: people
    • Label: Personnel analysing results - Code: PAT - Unit: people
    • Label: Personnel delivering results - Code: PDT - Unit: people
  • Then, add a Single Selection field to check if there was capacity for more testing today or there were shortages. In this example we have added the following Single selection field:
    • Label: Was there capacity for more testing today? Options: Yes, No
  • Finally, add a Multiple Selection field  to collect information about the shortages of the specific day. In this example, we have added the following Multiple selection field:
    • Label: If no, please select the shortages of this day: Options: cotton swabs, packaging material, personal protective equipment for samplers, liquid units for analysis, filters for analysis, analysis equipment, personnel doing tests, personnel transferring the tests, personnel analysing results, personnel delivering results
  • Optionally, you can add a Multi-line Text field to ask for more information.

You can add Section headers to make the Subform easier to navigate.

Add Calculated fields in the Parent Form

In the Parent Form, we can make some calculations based on the numbers added in the Subform. We can add Calculated fields to automatically sum up the daily numbers of material used. This can be useful for resources used once and then discarded (e.g. cotton swabs, packaging, protective material).

For the personnel we can use a Formula to get the average amount of personnel occupied in that medical institution for each position. We can use another Formula to calculate the maximum number of people occupied in each position.

  • In this example we used the following Calculated fields and Formulas. You can adjust these to your own Quantity fields and needs.
    • Label: Total cotton swab kits used - Formula: sum(CU)
    • Label: Total packaging materials used - Formula: sum(PU)
    • Label: Total personal protective equipment for samplers used - Formula: sum(EU)
    • Label: Total liquid units for analysis used - Formula: sum(LU)
    • Label: Total filters for analysis used - Formula: sum(FU)
    • Label: Total analysis equipment used- Formula: sum(AU)
    • Label: Average number of personnel making tests - Formula: average(PET)
    • Label: Average number of personnel transferring tests - Formula: average(PT)
    • Label: Average number of personnel analysing tests - Formula: average(PAT)
    • Label: Average number of personnel delivering results - Formula: average(PDT)
    • Label: Maximum number of personnel making tests- Formula: max(PET)
    • Label: Maximum number of personnel transferring tests - Formula: max(PT)
    • Label: Maximum number of personnel analysing tests - Formula: max(PAT)
    • Label: Maximum number of personnel delivering results - Formula: max(PDT)
  • Save the Form.

You can add Section headers to make the Form easier to navigate.

Form Schemas

Instead of manually designing the Form and the Subform, you can directly import the fields list to your Form and Subform. The following files contain the fields list for the Form and the Subform. As this is a sample Form, you might need different fields so can add new fields, edit fields or delete the fields that you don't need to customize the Form. The Form links to a Reference Form with a Reference field to include a list with all the medical institutions, so make sure you add a Reference Form first, as described in the first step.

As the Form includes Calculated fields based on fields of the Subform, you will need to import the fields in three steps.

  • To import the first fields list to the Form, add a new Form and give it a name.
  • Then, download and open the first file which includes the first part of the Parent Form. You need to find the Reference Form Id of the Reference Form from step one and add it the cell in the Row 'reference', Column 'Referenced Form'. You can find the Form Id in the URL of the Table View page of the Reference Form.
  • Copy the cells along with their Headers.
  • In the Form Design page, open the fields palette of the Form and click on "Paste field list from spreadsheet". Paste the cells and click on "Add fields" to add them.
  • Continue with the Subform. To import the fields list to the Subform, click on the Subform to open it.
  • Download the second file and copy all the cells along with their Headers.
  • As before, open the fields palette of the Subform and click on "Paste field list from spreadsheet".
  • Paste the cells and click on "Add fields" to add them.
  • Save the Subform to go back to the Parent Form.
  • Finally, in the Parent Form, we will add the Calculated field.
  • Download the third file which contains the second part of the Parent Form with the Calculated field, open it and copy all the cells along with their Headers.
  • As before, open the fields palette of the Parent Form and click on "Paste field list from spreadsheet".
  • Paste the cells and click on "Add fields" to add them.
  • Save the Form.