Error: Unknown or inaccessible field

When you are working on your LookML files and are satisfied with your updates, the next step to deploy your LookML changes is to run the LookML Validator to perform a full model validation.

Occasionally, you might see an error like the following:

 Unknown or inaccessible field "user_order_facts.lifetime_orders" referenced in "users.lifetime_orders". Check for typos and missing joins.

In this example, the error refers to the lifetime_orders field in the users view. The error indicates that users.lifetime_orders cannot access the user_order_facts.lifetime_orders field that it references.

Why does this error occur?

There are a few reasons this error can occur:

1. The field you are referencing does not exist.
2. The field you are referencing is an entire dimension group — for example, a dimension group is referenced without an appendedtimeframe.
3. The field is inaccessible by some Explores because there is a missing join.

Option 1: The field does not exist

If the field user_order_facts.lifetime_orders is referenced in LookML fields but does not exist as a field itself, you will receive the unknown or inaccessible field error.

You can resolve the error by adding the field that is triggering the error — in this example, user_order_facts.lifetime_orders — to the view that contains the field in question. In this case, you can make sure the field is defined in the user_order_facts view; if it doesn't exist, you can add it.

Option 2: The field references an entire dimension group

Dimension groups represent a group of dimensions. type: time dimension groups represent a group of time-period dimensions that are defined in the timeframe parameter. When you reference dimension groups in LookML, you must append the appropriate dimension — timeframe, in this case — to the dimension group name.

For example, consider the following dimension group:

  dimension_group: created {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.created_at ;;
  }

If you want to reference the created dimension group in another LookML field, you must reference a specific timeframe dimension within the group, such as one of the following:

• date: ${created_date}
• week: ${created_week}
• month: ${created_month}

If you try to use just the name of the dimension group — ${created} — Looker will not know which timeframe you are referring to and will generate the error.

Option 3: There is a missing join

The following is the LookML definition of users.lifetime_orders:

  dimension: lifetime_orders {
    type: number
    sql: ${user_order_facts.lifetime_orders};;
  }

Note the use of the substitution operators ${} to reference the LookML field user_order_facts.lifetime_orders.

The lifetime_orders dimension in the users view references the lifetime_orders field from the user_order_facts view. In this case, the error is triggered because there are instances in the model file where the users view is joined to an Explore without the user_order_facts having been joined as well.

To see which Explores are causing the problem, you can expand the occurrences that are highlighted in the error message:

These occurrences show that the order_items and orders Explores in the ecommerce model are causing the error. These Explores have many joins and are defined as the following in the model file:

  explore: orders {
    join: users { # users joined without user_order_facts
      relationship: many_to_one
      sql_on: ${orders.user_id} = ${users.id}
    }
  }

  explore: order_items {
    join: inventory_items {
      relationship: many_to_one
      sql_on: ${order_items.inventory_item_id} = ${inventory_items.id}
    }
    join: orders {
      relationship: many_to_one
      sql_on: ${order_items.order_id} = ${orders.id}
    }
    join: users { # users joined without user_order_facts
      relationship: many_to_one
      sql_on: ${orders.user_id} = ${users.id}
    }
  }

In both of these Explores, the users view is joined without also joining the user_order_facts view; as such, neither Explore can access the user_order_facts.lifetime_orders field. If you tried to query the users.lifetime_orders field — which references user_order_facts.lifetime_orders — in either Explore, you would trigger the error.

The LookML validator is warning you that users will receive the error when they query users_order_facts.lifetime_orders. The users.lifetime_orders field will not trigger the error in an Explore to which user_order_facts is also joined.

For example, consider the users Explore:

  explore: users {
    join: user_order_facts {
      sql_on: ${users.id} = ${user_order_facts.users_id}
    }
  }

Here user_order_facts is joined, so querying users.lifetime_orders will not trigger an error.

How can I fix the error when it is caused by a missing join?

If the error is caused by a missing join, you can fix this error in a couple of ways:

1. Join the missing view in all cases. For the example used throughout this page, make sure the user_order_facts view is joined wherever the users view is joined in an Explore.
2. Exclude the field causing the error from Explores if you do not want to join the missing view.

Join the missing view

In the preceding example the error can be solved by joining user_order_facts to all the Explores where users is also joined. This will ensure that Explores can access user_order_facts.lifetime_orders when users.lifetime_orders is used in a query.

You can use the metadata panel in the IDE to see all Explores that use the users view.

The following example joins the missing views:

  explore: order_items {
    join: inventory_items {
      relationship: many_to_one
      sql_on: ${inventory_items.id} = ${order_items.inventory_item_id}
    }
    join: orders {
      relationship: many_to_one
      sql_on: ${order_items.order_id} = ${orders.id}
    }
    join: users {
      relationship: many_to_one
      sql_on: ${orders.user_id} = ${users.id}
    }
    join: user_order_facts { # join user_order_facts through users
      relationship: many_to_one
      sql_on: ${users.id} = ${user_order_facts.users_id}
    }
  }

Now, if you rerun the LookML validator, this error should not appear.

Exclude the field that is causing the error from Explores

You may not want to join the user_order_facts view to all Explores where users is joined. For example, maybe you don't want users to access the fields from the user_order_facts view in the orders Explore, but you do want users to access the fields from the users view without error. You can do this by excluding the field causing the error — users.lifetime_orders — from the orders Explore, using the fields parameter.

The fields parameter for Explores lets you include or exclude specific fields from an Explore. In this case, you can exclude users.lifetime_orders from the orders Explore like this:

  explore: orders {
    fields: [ALL_FIELDS*, -users.lifetime_orders] # exclude users.lifetime_orders
    join: users {
      relationship: many_to_one
      sql_on: ${orders.user_id} = ${users.id}
    }
  }