How do I access the parent of a nested field?

#1

I have a schema that is nested a few levels deep. I’ve set up a contrived example schema that illustrates the problem I’m having.

At one of the higher levels, there is a department field which is a custom list two values “Sales” and “Marketing”. In a lower level, I need to choose a manager from a custom list with a custom distribution, so that only some of these managers will be available for Sales employees and the others will be available for Marketing employees.

I used the UI to add rules for all values of department and then just modified the distributions, but I get errors when I preview the data:

[{
  "employees": [
    {
      "department": "Sales",
      "gender": "Male",
      "first_name": "Addie",
      "last_name": "Naton",
      "email": "anaton0@pinterest.com",
      "accounts": [
        {
          "manager": "error: Field 'employees.department' not found"
        }
      ]
    }
  ]
}]

Here is the exported schema in JSON format:

{
  "id": 277492,
  "num_rows": 2,
  "file_format": "json",
  "name": "Nesting Example",
  "array": true,
  "columns": [
    {
      "name": "employees",
      "null_percentage": 0,
      "type": "JSON Array",
      "minItems": 1,
      "maxItems": 1,
      "formula": ""
    },
    {
      "name": "employees.department",
      "null_percentage": 0,
      "type": "Custom List",
      "values": [
        "Sales",
        "Marketing"
      ],
      "selectionStyle": "custom",
      "distribution": [
        {
          "frequencies": {
            "Sales": "3",
            "Marketing": 1
          }
        }
      ],
      "formula": ""
    },
    {
      "name": "employees.first_name",
      "null_percentage": 0,
      "type": "First Name",
      "formula": ""
    },
    {
      "name": "employees.last_name",
      "null_percentage": 0,
      "type": "Last Name",
      "formula": ""
    },
    {
      "name": "employees.email",
      "null_percentage": 0,
      "type": "Email Address",
      "formula": ""
    },
    {
      "name": "employees.gender",
      "null_percentage": 0,
      "type": "Gender",
      "formula": ""
    },
    {
      "name": "employees.accounts",
      "null_percentage": 0,
      "type": "JSON Array",
      "minItems": 1,
      "maxItems": 1,
      "formula": ""
    },
    {
      "name": "employees.accounts.manager",
      "null_percentage": 0,
      "type": "Custom List",
      "values": [
        "Mary Jane",
        "Phyllis Smith",
        "Bob Marley",
        "Phil Jackson",
        "Chandler Bing"
      ],
      "selectionStyle": "custom",
      "distribution": [
        {
          "rule": "field(\"employees.department\") == \"Sales\"",
          "frequencies": {
            "Mary Jane": 1,
            "Phyllis Smith": 1,
            "Bob Marley": 1,
            "Phil Jackson": "0",
            "Chandler Bing": "0"
          }
        },
        {
          "rule": "field(\"employees.department\") == \"Marketing\"",
          "frequencies": {
            "Mary Jane": "0",
            "Phyllis Smith": "0",
            "Bob Marley": "0",
            "Phil Jackson": 1,
            "Chandler Bing": 1
          }
        }
      ],
      "formula": ""
    }
  ]
}
#2

This is the type of problem that scenarios are designed to solve. Please see the tutorial on scenarios. You will, however, have to add a field to your managers table indicating the type of department that they manage–sales or marketing.