Datasets: reference tables to look up and prefill from

How to: create and use a dataset

1. 1
   In the menu, open Templates and the Datasets tab.
2. 2
   Click **+ at the top left, enter a name such as “Suppliers” and click Create**.
3. 3
   Add the columns under Columns. The first becomes the key column that is searched.
4. 4
   Click Add row and enter the values. Or load them via Import file.
5. 5
   In a template, give a field the type From dataset and pick your dataset there.

12345

1. 1**+** creates a dataset
2. 2Connect to Excel
3. 3Import file
4. 4Columns: the schema of every row
5. 5Add row

A dataset belongs to the organization, not to one template. Maintain it once and it is up to date in every template. Datasets and columns need the Manage templates right; for rows, Maintain datasets is enough.

Define the columns

The columns are the schema for every row. Each column has a label, a type and a technical name that is created for you. Once the dataset has rows, the Columns card is collapsed; click its header to open it.

12345

1. 1Type of the column
2. 2Key: exactly one column
3. 3Required
4. 4Add column
5. 5Billing details

If you switch a column with values to number, date, email, yes/no or reference, Bricksta checks every value first. If one doesn’t fit, such as “ca. 90k” in a number column, the type stays. The message lists the values and the cells are marked red.

Maintain rows

Click Add row and type into the cells. Bricksta saves a value when you leave its cell. If someone maintains the same dataset at the same time, you see their changes without reloading.

The end of each row shows how many cases use the entry, such as “3 cases”. Next to it, ⋯ opens the row menu. Drag the handle ⋮⋮ to reorder rows.

Import rows from CSV or Excel

1. 1
   Select the dataset and click Import file at the top right.
2. 2
   Choose a CSV or Excel file (.xlsx). Its first line holds the column names.
3. 3
   If the file has several worksheets, you pick one. Bricksta creates missing columns.
4. 4
   Check the preview: new, changed, unchanged and skipped, each with the reason.
5. 5
   Click Apply rows. If one row fails, the whole dataset stays unchanged.

1

1. 1Import file

Export rows as CSV

Click Export CSV next to Import file. The file holds every non-archived entry and opens straight in Excel. Import it unchanged and the preview reports 0 changes.

If a value starts with =, +, - or @, an apostrophe goes in front of it. That stops Excel from running it as a formula. Bricksta strips the apostrophe when reading the file back.

Sync with an Excel file

If a list already lives in Excel on OneDrive or SharePoint, Bricksta syncs it hourly in both directions. The file needs a formatted table for this (“Format as Table” in Excel).

1. 1
   Click Connect to Excel and, if needed, connect your Microsoft account with file access.
2. 2
   Pick the file under Recent, My files or Shared with me, or search for its name.
3. 3
   Pick the table and map the columns. The key column must be included.
4. 4
   The last sync now shows above the rows table. Sync now starts it right away.

1

1. 1Connect to Excel

Fill a field from a dataset

1. 1
   Open the template and the task that should look up.
2. 2
   Add a field and choose From dataset as its Type.
3. 3
   Pick your dataset under Select dataset.
4. 4
   Under Fill other fields, map columns to target fields, such as Stadt → field Stadt.

12345

1. 1Type: From dataset
2. 2Select dataset
3. 3Fill other fields
4. 4read-only
5. 5When the dataset changes

In the case, the field is a search box. Picking a row fills the field with the key and the mapped fields with the other values.

Entry missing? Add or suggest it

Open suggestions show at the top of the dataset page, and the Templates menu item shows how many. Accepting creates the entry. When rejecting, you pick a replacement or clear the field; whoever suggested it is notified.

Against duplicates in another spelling, the add entry shows “(similar: …)”, such as for “MSD GmbH” next to “MSD”. Case, punctuation and legal form don’t count here. You can still add the entry.

Protect and update values

With read-only, a target field only shows the value from the dataset, such as a list price. It shows a lock and only changes when someone picks a different row. This works for text, long text, email, number, date and yes/no.

Deadlines that depend on an updated date are recalculated. Completed cases and tasks waiting for approval stay unchanged.

Make the choice depend on another field

Example: in a quote you first pick the customer, then only that customer’s contacts. For this, the dataset “Contacts” needs a reference column “Company” → “Customers”.

On the field “Contact”, pick the field “Customer (via “Company”)” under Only entries matching. Until a customer is chosen, it reads “Choose “Customer” first”. If the customer changes, the contact is cleared along with the fields it filled.

Set target and deadline for the stacked view

If you stack the case list by a field from this dataset, each stack reads its Target and deadline from its entry’s row. The target is a number column, the deadline a date column.

Unless you choose otherwise, Bricksta uses the first number and the first date column. You pick others when saving a view, under “Target and deadline”. If a row has no target, the stack shows “No target set”.

Add billing details

If a dataset holds your invoice recipients, turn on Billing details below the columns. Then you pick one column per detail, never the key column.