Sheets

As a business grows, managing large volumes of data in Excel becomes cumbersome and inefficient. Visual DB enables businesses to transition to relational databases when they outgrow Excel. With its spreadsheet-like interface, Visual DB Sheets allows users to interact with data as they would in Excel, while securely storing that data in a robust relational database.

Using a sheet you can insert new records, update existing records, and delete records. You can also view up to 100,000 records at a time. Records can be grouped, sorted and filtered. Incremental search is also supported.

Don’t worry if your database has millions of rows. Visual DB can accommodate databases of any size by using query parameters to fetch subsets of data.

Start by Defining a Query

Creating a sheet begins with defining a query. The sheet displays all records returned by this query. Each query consists of a main table and optional lookup tables. While you can update fields from the main table, fields from lookup tables are display-only and cannot be modified.

Changing the Sort Order of Records

Visual DB allows you to modify the order in which records are displayed using the sorting feature. To sort records, click the Sort button located on the top panel, towards the left. You can select a field whose values will be used to determine the order of the records. Additionally, you can choose either ascending order (A -> Z) or descending order (Z -> A).

Group Records Together

In Visual DB, you can group records together based on shared field values. To create groups, click the Group button located in the top menu, towards the left. Then select a field by which records should be grouped. Groups can also be nested within other groups, for example Cities can be nested inside State. The group listed at the top of the grouping menu will serve as the outer group, while lower-listed groups will be nested within it.

Grouping by IDs

In some cases you may want to group records by an ID field. For example, if you have two employees with the same name, then grouping employees by name will cause them to be treated as the same person. To avoid this you can group employees by Employee ID. In this case you will also want to set the Group Caption field to Employee Name so that headings of each group continue to show employee names as opposed to IDs. To set the Caption field open the Group Settings dialog by clicking the gear icon.

Sorting Groups

Groups can be sorted in several ways. You can sort by the group-by field or by the Caption field if you have set one. Additionally, groups can be sorted by any field for which totals are displayed; in this case groups will be sorted on those totals.

Filtering Records

You can systematically filter records based on the values of specific fields in your sheet. To create a filter, click the Filter button located in the top menu, towards the left. Select the field you want to filter by and enter the specific values you want to display. Note that the data type of the field will affect the available filtering options.

When more than one condition is added to the filter you can specify whether all of those conditions must be satisfied (condition1 AND condition2 AND condition3) or any of them (condition1 OR condition2 OR condition3). Visual DB supports nested filters, where one group of conditions are nested in another group of conditions. Each group can have a different All/Any setting.

Quickly Search for a Specific Record

Incremental Search allows you to highlight and filter for records that match the string entered in the search bar. To use this feature, go to the top right and type your query into the search bar. Incremental Search will filter out records that don’t match and highlight the parts of the records that correspond to your search term.

How to Provide Available Values

To set available values for a given field, start by clicking the field name at the top of the sheet. Then, click Settings and select Available Values. You will have several options for specifying the available values:

  • Offer Existing Values: Displays all values previously entered in the column for the respective field.

  • Get From a Database Table: Allows you to provide a new table that contains all accepted values.

  • Get Using a Query: Enables you to create or select a query that retrieves the accepted values.

  • Specify Manually: Lets you manually enter each accepted value one by one.

Color Coding Values

If a field has a limited set of possible values, Visual DB allows you to color-code these values for better visualization. To do this, click the field name at the top of the sheet, then select Settings followed by Available Values. Choose a method for specifying the available values, and then click Color-Code Values.

How Save Changes Works

To maintain record consistency, Visual DB sends changes to the database on a record-by-record basis, rather than field-by-field.

Here’s why this matters:

  • If changes were saved field-by-field, User A might update a medication’s dosage strength from 50mg to 100mg while User B updates the pill count from 1 to 2 pills. These independent changes could get dangerously mixed together, resulting in an incorrect total dosage amount.

  • By saving entire records at once, Visual DB ensures that all related fields within a record stay synchronized and reflect the complete set of intended changes from each user.

When you’re done updating fields press the Save button. You can then review new records, updated records, and deleted records before sending them to the database.

Note

Visual DB does not automatically update the database as soon as you modify a field. Doing so could compromise record consistency by intermingling field changes from multiple users. Instead, Visual DB updates the record atomically when you press the Save button.

Overwrite Protection

If another user has modified the record since you loaded it, Visual DB will reject your changes to prevent overwriting their updates. In this case, you will need to refresh the page to load the latest version of the record and re-apply your changes.