Worksheet Systems is a cloud-based data management platform that allows anybody to organize their data online and build custom business applications with no coding. Worksheet Systems is used when off-the-shelf products are not enough and Excel spreadsheets is not an option, because they are not controlled, secured and sharable.

Basic Building Blocks of Worksheet Systems

Before you start using Worksheet Systems, you need to understand the basic building blocks that it uses to structure and manage your data. These building blocks are Data Table, Dashboard, and Query Panel. With data tables, you can define the structure of your future online database, whereas query panels and dashboards help you to customize the look and feel of your data.

dashboard grid

Structuring Your Database

Before you create and customize your Worksheet Systems database, you need to define its structure and fill it with data. For this purpose, Worksheet Systems offers Data Tables, Virtual Tables, and Custom SQL Tables.

Data Tables

A Data Table is your data stored in a structured format with fields (columns) and rows, in a way similar to Microsoft Excel data table. In Worksheet Systems, you can import your data from an Excel file, a .CSV file, or Quandl database. You can also create a new table from scratch and enter the data manually.

When you import your data or create a new table, the system defines data types for columns in your table. Worksheet Systems offers the following data types:

  • Short Text – Text up to 250 characters.
  • Auto Number – This data type is reserved for Key Fields. With each new row, a number is automatically generated in this field.
  • Long Text – Text up to 4,000 characters
  • Float Number – e.g. 1,234.21
  • Whole Number – e.g. 123
  • True/False
  • Date Time

By specifying the data type for each column, Worksheet Systems knows how to manipulate the data in this column. The system will issue an error in case of a wrong data type, for example, if someone tries to put the name of a product into a column that is reserved for date or time. Columns can have empty cells if desired.

Your Data Table must have at least one unique field, which is labeled in Worksheet Systems as the Key Field. This is a column where each entry is unique. When you import or type in your data, you can specify which column is the Key Field.

Having a unique field ensures that each single row in your data table is unique, and that the data is not repeated. In the example below, the ID field is a unique key.

database table

Virtual Tables

A virtual table combines records from different tables. It consists of a main table and one or several joined tables. A joined table has a common field with a main table. If you change data in the main table or joined tables, these changes are also reflected in the virtual table. You cannot import data to a virtual table – it just takes data from previously created tables.

Let’s suppose that we want to combine data from two tables that were previously imported to Worksheet Systems: NorthwindOrders and NorthwindCustomers, to see records of customers that are related those orders. We define NorthwindOrders as a main table and NorthwindCustomers as a joined table, and then choose which fields of the two tables we would like to display in our resulting virtual table. We can also apply filters, for example, to display the customers only from the United Kingdom.

And here is the resulting table:

So, if you change something either in the NorthwindOrders table or in the NorthwindCustomers table, then our resulting virtual table, CustomersWithOrders, will show the same changes. You can also edit the main table fields directly in the virtual table, which is impossible in case of read-only custom SQL tables.

In Microsoft Access 2007, you can also join tables. For more information, see Microsoft Office help articles.

Custom SQL Tables

A custom SQL table is a more advanced virtual table, which is based on SQL query results. You can use the SQL Helper to fetch table rows, and then use the built-in SQL Query Editor to modify the existing queries or create new ones. Custom SQL tables give you more flexibility, because they are not limited by user interface options.

Data Queries

Data Query is a named and permissioned data query what can be used in external Add-Ins

Data Query has a predefined set of fields and filters

Customizing Your Database

Now, when your database is structured and filled with data, you can customize its look and feel with Dashboards and Query Panels.

A Dashboard gives you access to information that you need and allows you to share this information with colleagues or customers who might be interested.

Below are examples of different Dashboards. Each of these Dashboards was created by using the same Data Table. However, they display different information. All of these sheets were created by using just one set of data. Unlike with a spreadsheet, you do not need to copy and paste the data to create different views:

dashboard cloud database

dashboard

dasboard cloud database

A Dashboard can contain one or several Query Panels, which, in their turn, can access information from different Data Tables. This allows you to build up unique sets of information depending on what the user needs to see, without revealing the information that you do not need to show them.

A Query Panel allows you to select which data you want to read or modify from your Data Table. Depending on who is viewing the data, you might want to display it in a certain way, or even hide some information.

For example, you might want to share the above data without displaying the revenue or expense column. A Query Panel allows you to select, deselect, and manipulate data for others to see. Simply put, a Query Panel is a request for information from your Data Table.

To request this information, take two simple steps:

  1. Define your query.

There are two types of queries:

  • Table: Table queries return rows (from columns that you specify) from one Data Table.
  • Pivot Table: Just as in Microsoft Excel, pivot tables in Worksheet Systems allow you to group your data by a shared field. For example, in the data above we might want to query the revenue according to each country. This would produce a table of results showing how much revenue each country produced, without any calculations or equations. For more information about Excel pivot tables, see Microsoft Office help articles.

With both these query types you can apply filters to select which data you are manipulating.

  1. Customize your view.

Next you can choose how you want to display the information that you have selected. You have the following options:

  • Table: spreadsheet-like data table that allows you to read and modify your data.
  • Line Chart: graphically represents your data as Line Chart.
  • Bar Chart: graphically represents your data as Bar Chart.
  • Pie Chart: graphically represents your data as Pie Chart.
  • Donut Chart: graphically represents your data as Donut Chart.

Each query can access data either from a single Data Table or from several Data Tables, if you request information from Virtual Tables. Each Data Table can have as many queries as you want, for example, you might have different queries for different information that you want to present to your sales teams, operations teams, and clients.