Limit new subcategory by category

Goal

The category and subcategory are both linked records, and when picking a subcategory for a record you want to see only subcategories that belong to the previously chosen category.

Parts to this solution

  • A set of three tables:
    • Main table with links to the Categories and Subcategories tables
    • Categories table
    • Subcategories Table
  • a system of formula and rollup fields
  • record selection for the subcategory limited to a view

Fields in each table

Fields in the Main table

  • a {Category} field that links to a record in the Categories table
  • a {Subcategory} field that links to a record in the Subcategories table
  • a {Subcategory Limiting System} formula field that calculates if the record needs a subcategory (see formula below)
  • an optional rollup field based for error checking (see rollup configuration below)

Fields in the Categories table

  • a {Subcategories} field that links to multiple records in the Subcategories table
  • a {Main} field that links to multiple records in the Main table
  • a {Subcategory Limiting System} rollup field (see rollup configuration below)

Fields in the Subcategories table

  • a {Category} field that links to a record in the Categories table
  • a {Main} field that links to multiple records in the Main table
  • a {Subcategory Limiting System} rollup field (see rollup configuration below)

Configuring the formula and rollup fields

Main table > {Subcategory Limiting System} formula field

IF(
    AND(
        {Category},
        NOT({Subcategory})
    ),
    "Subcategory Limiting System"
)

Main table > optional rollup for error checking

  • Field in this table that links to the records you want to summarize: {Subcategory}
  • Fields that you'd like to roll up: {Category}
  • Formula:
    IF(
      NOT({Category}),
      IF(
          {Subcategory},
          "Subcategory without category",
          "Pick a category"
      ),
    IF(
      NOT({Subcategory}),
      "Pick a subcategory",
    IF(
      NOT(ARRAYJOIN(values) = {Category} & ""),
      "Category/Subcategory mismatch"
    )))

Categories table > {Subcategory Limiting System} rollup field

  • Field in this table that links to the records you want to summarize: {Main}
  • Field that you'd like to roll up: {Subcategory Limiting System}
  • Formula:
    IF(COUNTA(values), "Subcategory Limiting System")

Subcategories table > {Subcategory Limiting System} rollup field

  • Field in this table that links to the records you want to summarize: {Category}
  • Field that you'd like to roll up: {Subcategory Limiting System}
  • Formula:
    IF(COUNTA(values), "Subcategory Limiting System")

Limiting linked records to a view

In the Subcategories table create a view that filters to include only records where the {Subcategory Limiting System} field is not empty.

In the Main table, for the {Subcategory} field, limit record selection to the view created above.

Usage

If only one record has a category but no subcategory, when you select a category in the Main table, you will see only subcategories for that category.

If records with two different categories have no subcategory, when you select a category in the Main table, you will see subcategories for both of those categories.

You must select a category before selecting a subcategory. If you have many records, you may need to wait briefly while the formulas and rollups recalculate.

This method is not compatible with forms because in a form the category and subcategory are both picked before the record is created..

Notes

  • This is only one of many possible solutions. For example, a variation could show in the Categories and Subcategories tables which records in Main need to have a subcategory.

  • The names of the formula and rollup fields, as well as the results of those fields are all the same: "Subcategory Limiting System". This is to make it easier to recognize that all of these fields work together. Typically all of these fields would be hidden fields from the end user.

Demo Base