1. MATRIX

    1.1. Matrix table

    All the submissions made in a matrix question are stored in a different table.
    To find this table, you need the workflow id and the tag of the matrix question.
    The table is called w_<workflow_id>_<matrix_tag>

    For example, let’s take this workflow:




    In this workflow, we can find this matrix attached to the list Products (list id : jxbkia8qlhu3)



    The matrix table is w_akrb7ybm39pmge8k_sku

    In this matrix table, you will find the following fields:
    _id : row id
    _submission_id: the corresponding id in the workflow table
    _object_id: the corresponding id in the list

    1.2. Workflow - Matrix

    If you need to do a left join between the workflow table and the matrix table, you need to map the workflow id to the matrix submission id:
    w_<workflow_id>_<matrix_tag>._submission_id = w_<workflow_id>._id

    In our example, the mapping would be: w_akrb7ybm39pmge8k_sku._submission_id=w_akrb7ybm39pmge8k._id

    1.2. Matrix - List

    If you need to do a left join between the list attached to the matrix and the matrix table, you need to map the list id to the matrix object id:
    l_<list_id>._id = w_<workflow_id>_<matrix_tag>._object_id

    In our example, the mapping would be: l_jxbkia8qlhu3._id= w_akrb7ybm39pmge8k_sku._object_id

  2. LISTS

    1.1. Scope : teams

    If a list is in scope teams or single team, it means only the teams that are the owners of the items can see this item.
    If you need to do a left join between the list and the team, you need to use the mapping table items_teams.

    In this table you will find:
    id : mapping id
    item_id: corresponding id in the list
    team_id: corresponding team id

    If you have the list id : jxbkia8qlhu3,
    You will map this list to the teams using two left join:

    FROM l_jxbkia8qlhu3
    LEFT JOIN items_teams ON items_teams.item_id=l_jxbkia8qlhu3._id
    LEFT JOIN teams ON teams.id = items_teams.team_id

    1.2. Scope: mobile users

    If a list is in scope mobile user or single mobile user, it means only the mobile users who are the owners of the items can see this item.
    If you need to do a left join between the list and the mobile users, you need to use the mapping table items_mobile_users.

    In this table you will find:
    id : mapping id
    item_id: corresponding id in the list
    mobile_user_id: corresponding mobile user id

    If you have the list id : jxbkia8qlhu3,
    You will map this list to the mobile users using two left join:

    FROM l_jxbkia8qlhu3
    LEFT JOIN items_teams ON items_teams.item_id=l_jxbkia8qlhu3._id
    LEFT JOIN mobile_users ON mobile_users.id = items_teams.mobile_user_id

    1.2. Creation details

    _created_at: creation timestamp (local time)
    _created_by: phone number if the item has been created by a mobile user, email if the item has been created by a web user
    _created_by_id: mobile user id if the item has been created by a mobile user, web user id if the item has been created by a web user