Skip to main content

What data and relationships are in Data Share?

Data Share is a read-only copy of your company's Assignar data that you can load into Excel or Power BI.

Data Share is a read-only copy of your company's Assignar data that you can load into Excel or Power BI. It is organised into 13 data feeds (tables), such as timesheets, allocations and forms. This article lists each feed and its fields, explains which columns link the feeds together, and tells you how to get Data Share switched on or ask for a field that isn't there.

Data Share is set up by Assignar, not from a page on the Assignar dashboard. There is no Data Share menu on the dashboard.

How do I get Data Share switched on?

Data Share is set up for your company by Assignar. To ask for it, contact your Assignar account manager or Assignar Support. Assignar's Data Team then sends you:

  • the server address

  • a user name (or confirms which email address to use) and a password

  • access to the data feeds you need. Each login is given access to particular feeds.

The database name is always assignar. To connect, see How do I connect Data Share to Excel? or How do I connect Data Share to Power BI?.

How do I ask for a field that isn't in Data Share?

If a field you need isn't in any Data Share feed listed below, contact your Assignar account manager or Assignar Support with the feed name and the field you want. If the field exists in a feed but you can't see the feed, ask for access to that feed: your Data Share login only shows the feeds you've been given.

Data Share feeds at a glance

The table below shows each Data Share feed and what one row represents.

Feed

What it holds

Allocations

Order allocations (workers and assets assigned to shifts), with the order, task, project, client and check-in and check-out details

Assets

Assets and the orders and projects they were allocated to

Competencies

Workers' competencies, with issue and expiry dates

Dockets

Digital docket answers, one row per question answer

Fieldworkers

Workers' details, tags, tasks and leave

Forms

Form results, one row per question answer, with the form's journal entries

Inductions

Workers' inductions, with issue and expiry dates

Order_progress

Daily progress entries against order activities

Order_status_history

Each change of an order's status

Orders

Orders with their shifts, tasks, project, client, supplier and journal entries

Sitediaries

Site Diary answers, one row per question

Timesheets

Timesheets with activities, pay codes, allowances and check-in and check-out details

Costs

Timesheet pay items, timesheet charge items and chargeable item (material) transactions

Every feed has tenant_id, the name of your Assignar company.

How Data Share feeds link together

Data Share feeds share ID columns, so you can join them in Excel or Power BI. The table below shows the main columns that link feeds.

Column

What it identifies

Feeds that have it

order_id

An order

Allocations, Assets, Dockets, Fieldworkers, Forms, Order_progress, Order_status_history, Orders, Timesheets, Costs

timesheet_id

A timesheet

Allocations, Timesheets, Costs

fieldworker_user_id

A worker

Allocations, Competencies, Fieldworkers, Forms, Timesheets, Costs

project_id

A project

Allocations, Dockets, Forms, Orders, Timesheets

client_id

A client

Allocations, Dockets, Forms, Orders, Timesheets

task_id

A task on an order

Allocations, Orders, Timesheets

asset_id

An asset

Allocations, Assets, Forms, Timesheets

site_diary_id

A Site Diary

Sitediaries, Timesheets, Costs

Text columns that combine several tags (such as fw_tags, order_tags and project_tags) separate each tag with a tilde (~).

Allocations feed in Data Share

The Allocations feed in Data Share holds order allocations and the details around them. Fields: tenant_id, asset_external_id, asset_id, asset_name, avg_hourly_rate, client_company_name, client_external_id, client_id, client_name, fieldworker_user_id, fw_dob, fw_emergency_contact_name, fw_employee_id, fw_employment_type, fw_first_name, fw_full_name, fw_last_name, fw_tags, fw_user_age, fw_user_type, notified_comment, order_active_flag, order_date_created, order_end_date, order_id, order_job_description, order_latitude, order_location1, order_longitude, order_purchase_order_number, order_resource_status, order_resource_total_time, order_shift_allocation_time_in_hours, order_shift_date, order_shift_end_time, order_shift_index, order_shift_start_time, order_start_date, order_status, order_tags, order_task_description, order_type, order_work_order_number, project_end_date, project_external_id, project_id, project_name, project_start_date, task_external_id, task_id, task_name, task_qty, timesheet_id, work_item_code, work_item_id, work_item_label, order_resource_check_in, order_resource_check_out, order_resource_check_in_location, order_resource_check_out_location, distance_from_project_to_check_in, distance_from_project_to_check_out, fw_usi, fw_supplier_name.

Notes on the Allocations feed:

  • order_resource_status is the allocation status: pending, confirmed or declined.

  • order_resource_total_time is the time between check-in and check-out.

  • distance_from_project_to_check_in and distance_from_project_to_check_out are in kilometres.

  • order_active_flag is true for an active order and false for an archived one.

Assets feed in Data Share

The Assets feed in Data Share holds assets and where they were allocated. Fields: tenant_id, allocation_order_resource_allocation_date, allocation_order_shift_allocation_time_in_hours, asset_active_flag, asset_external_id, asset_id, asset_make, asset_model, asset_name, asset_rego_number, asset_serial_number, asset_year, avg_hourly_rate, comments, number_reading, order_id, project_name, tags.

asset_active_flag is 1 for an active asset and 0 for an archived one. number_reading is the asset's reading, such as odometer or operating hours.

Competencies feed in Data Share

The Competencies feed in Data Share holds workers' competencies. Fields: tenant_id, competency_id, competency_name, competency_user_date_of_expiry, competency_user_description, competency_user_expired, competency_user_issue_date, expiry_date_required_flag, fieldworker_user_id, fw_active_flag, fw_employee_id, fw_employment_type, fw_full_name, fw_no_of_competencies, fw_tags, issue_date_required_flag, skill_name.

expiry_date_required_flag and issue_date_required_flag are 1 when the competency needs an expiry date or issue date.

Dockets feed in Data Share

The Dockets feed in Data Share holds digital docket answers, one row per answer. Fields: tenant_id, answer_asset_id, answer_asset_name, answer_shift_break_time, answer_shift_end, answer_shift_start, answer_shift_total_time, answer_task_id, answer_task_name, answer_worker_id, answer_worker_name, client_id, client_name, created_by_fieldworker_name, created_by_fieldworker_user_id, digital_docket_id, digital_docket_template_id, digital_docket_template_title, docket_date, fieldworker_supervisor_user_id, order_id, project_id, project_name, question, question_array_index, question_id, result_answers, sub_question, submission_date.

Fieldworkers feed in Data Share

The Fieldworkers feed in Data Share holds workers' details. Fields: tenant_id, allocation_order_resource_allocation_date, fieldworker_user_id, fw_active_flag, fw_address, fw_contact_info, fw_email, fw_emergency_contact, fw_emergency_contact_name, fw_employee_id, fw_employment_type, fw_full_name, fw_postcode, fw_state, fw_suburb, fw_tags, leave_end_datetime, leave_start_datetime, leave_type, order_id, order_work_order_number, fw_usi, worker_roles.

worker_roles lists the worker's tasks (old name: roles), separated by a tilde (~).

Forms feed in Data Share

The Forms feed in Data Share holds form results, one row per answer. Fields: tenant_id, answer_asset_name, answer_datetime, answer_number, answer_status, answer_text, answer_worker_name, asset_id, asset_name, asset_rego_number, asset_tags, client_external_id, client_id, client_name, fieldworker_user_id, form_id, form_name, form_result_created_date, form_result_id, form_result_status, form_result_submitted_date, fw_employee_id, fw_full_name, order_id, order_job_description, order_location1, order_tag, project_external_id, project_id, project_name, question, question_array_index, result_answers, sub_question, supplier_name, form_result_journal_comment, form_result_journal_show_in_fieldworker_app, form_result_journal_created_by, form_result_journal_created_at, modified_by, form_result_modified_date.

Each answer sits in the column for its question type: answer_text for free text, answer_number for numbers, answer_datetime for dates and times, answer_asset_name for asset questions and answer_worker_name for worker questions.

Inductions feed in Data Share

The Inductions feed in Data Share holds workers' inductions. Fields: tenant_id, client_name, fw_active_flag, fw_employee_id, fw_employment_type, fw_full_name, fw_no_of_inductions, induction_expiry_date_required_flag, induction_id, induction_issue_date_required_flag, induction_name, induction_user_date_of_creation, induction_user_date_of_expiry, induction_user_disabled, induction_user_issue_date, project_name.

Order_progress feed in Data Share

The Order_progress feed in Data Share holds progress entries against order activities. Fields: tenant_id, activity_id, activity_name, activity_quantity_unit, daily_activity_progress, daily_labour_hours, order_id, order_job_description, order_progress_created_at, target_activity_progress, total_activity_progress, total_labour_hours.

For example, if 50 m of pipe were laid today, bringing the total to 400 m against a target of 1,000 m, daily_activity_progress is 50, total_activity_progress is 400 and target_activity_progress is 1,000.

Order_status_history feed in Data Share

The Order_status_history feed in Data Share has one row for each status an order has had. Fields: tenant_id, job_description, order_id, order_status_created_at, order_status_name.

Orders feed in Data Share

The Orders feed in Data Share holds orders with their shifts, tasks and related records. Fields: tenant_id, client_external_id, client_id, client_name, dashboard_user_full_name, order_active_flag, order_comments, order_end_date, order_id, order_job_description, order_location1, order_purchase_order_number, order_shift_date, order_shift_end_time, order_shift_index, order_shift_start_time, order_start_date, order_status, order_tags, order_task_description, order_type, order_work_order_number, parent_project, project_active_flag, project_duration_in_days, project_duration_in_months, project_end_date, project_external_id, project_id, project_latitude, project_longitude, project_name, project_start_date, project_state, project_suburb, project_tags, supplier_external_id, supplier_name, task_active_flag, task_external_id, task_id, task_name, task_qty, task_require_assets_flag, task_supervisor_flag, order_journal_comment, order_journal_show_in_fieldworker_app, order_journal_created_by, order_journal_created_at, project_journal_comment, order_owner.

Sitediaries feed in Data Share

The Sitediaries feed in Data Share holds Site Diary answers, one row per question. Fields: tenant_id, actual_amount, answer, answer_status, created_time, has_status_change, is_conditional, is_required, quantity_comment, question, question_id, question_type, site_diary_date, site_diary_id, site_diary_note, weather.

Timesheets feed in Data Share

The Timesheets feed in Data Share holds timesheets and their activities. Fields: tenant_id, activities_external_id, activity_billable, activity_end_time, activity_id, activity_name, activity_start_time, activity_time, activity_time_in_hours, allowance_list, asset_id, asset_name, client_external_id, client_id, client_name, fieldworker_user_id, fw_active_flag, fw_employee_id, fw_employment_type, fw_full_name, order_id, order_work_order_number, pay_code, pay_code_description, pay_rates, pay_units, project_id, project_name, site_diary_id, task_external_id, task_id, task_name, timesheet_break_time, timesheet_checked, timesheet_comments, timesheet_end_datetime, timesheet_end_time, timesheet_id, timesheet_is_deleted_flag, timesheet_modified_at, timesheet_signature_name, timesheet_start_datetime, timesheet_start_time, timesheet_submitted_date, timesheet_total_time, timesheet_travl_from_time, timesheet_travl_to_time, total_time, timesheet_url_code, timesheet_checked_in_at, timesheet_checked_out_at, timesheet_check_in_location, timesheet_check_out_location, timesheet_created_source.

timesheet_is_deleted_flag marks deleted timesheets. Filter them out when you total hours.

Costs feed in Data Share

The Costs feed in Data Share holds timesheet pay items, timesheet charge items and chargeable item (material) transactions. Fields: tenant_id, fieldworker_user_id, order_id, timesheet_id, site_diary_id, description, units, rates, comments, cost_item, client_name, fw_employee_id, fw_full_name, project_name, project_external_id, project_tags, supplier_name, order_work_order_number, order_purchase_order_number, order_job_description, order_start_date, order_end_date, task_name, asset_name, material_name, material_rate, timesheet_start_datetime, timesheet_end_datetime, timesheet_break_time, timesheet_total_time, timesheet_submitted_date, timesheet_checked, timesheet_comments, timesheet_office_use_comments, site_diary_date, work_item_code, work_item_label, activity_name, pay_code_code, pay_code_label, material_transaction_question, material_transaction_answer_number, material_transaction_answer_text.

cost_item says what each row is: Timesheet Pay Items or Timesheet Charge Items.

Related articles

Did this answer your question?