CrystalWerx Overview

Loading Data

The first step is to load some data into CrystalWerx. That can be done by importing data from an SAP gateway or from and Excel spreadsheet.

Excel

Download and install the CrystalWerx Excel Add-In. Use the Add-In to import any Excel Table directly into a CrystalWerx table.

SAP

Use the SAP Gateway to download data directly from an SAP gateway service.

Table Types

CrystalWerx uses a Table Type to understand the columns of data that are in a Table. The names of the columns that occur and the characteristics of each column are described by a Table Type. Table Types are usually created automatically when data is imported but may require further adjustments to work as needed.

Key Name is the name of the column that contains the primary key.

For each of the Columns in a Table Type:
  • Name: The name of the column
  • Label: The default column Header
  • Type: The type of data contained in the column.
  • Rules: Optional rules to validate the data contained in the column.

Built-in Validation Rules

CrystalWerx provides three types of built-in rules:

  • Data is required: Indicates that a null value is invalid.
  • Data must match pattern: A regular expression the value must match to be valid.
  • Data must exist in Table: The name of a Table that contains a set of values in which the value must occur.
  • Column: The name of the column that contains the set of values in which the value must occur.
  • Filter: An optional filter that can restrict the set of values in which the value must occur.

Custom Validation Rules

A Custom Rule includes a Validation Message that will be displayed any time the value is invalid. A Condition is used to compare a value with constant values or values from other columns and produces a true or false result. To be valid a list of Conditions must be met.

Conditions:
  • Value: The name of the column containing the value for the left side of the logical test.
  • Test: The logical test that will be performed between the left and right sides.
  • Operand: An expression constructed with constants or the names of columns for the right side of the logical test.
  • And/or: How the Condition will be logically combined with the next Condition in the list.

A Value Setter is used to set the value of a column with an expression constructed from constants and column names. The list of Value Setters is executed whenever the data is invalid or there are no Conditions.

Value Setters:
  • Column: The name of the column that will be updated.
  • Set To: An expression constructed with constants or the names of columns.

Table Views

CrystalWerx uses a Table View to display the columns of data that are in a Table Type. The names of the columns that are included and how each column is displayed are described by a Table View. Each Table Type can have many different Table Views. A default Everything view is usually created when data in imported.

For each of the Columns in a Table View:
  • Order: Columns are positioned left to right after sorting them by Order. Usually this is just the column number starting with 1.
  • Name: The Name of the column (as defined in the Table Type)
  • DisplayName: The Header to display for the column.
  • Input Type: The type of input control to use when the data is edited.
  • Format: Controls the way the data is displayed.
  • Width: Controls the width of the column in the table.
  • SourceTable: The name of a table that holds data for a drop-down list.
  • SourceColumn: The name of a column that holds data for a drop-down list.
  • SourceFilter: An expression that filters the data in a drop-down list.
  • ListData: A static list of data to display in a drop-down list.

Table Copiers

CrystalWerx uses a Table Copier to describe how one Table Type can be copied or merged with another Table Type. The source and destination Table Types can be the same or different. A Table Copier explicitly defines the contents of each column in the destination Table Type with an expression constructed using the names of columns in the source Table Type and constant values.

Every Table Copier has a Name. The number of Row Copies determines the how many times each source row is repeated in the destination table. The Source Table Type identifies the set of columns that can be mapped to the destination Table Type.

For each of the Columns in a Table Copier:
  • Name: The name of the destination column.
  • ReplaceWith: An expression describing the final value to be placed in the destination column.
  • Prepend: A comma delimited list of strings to add to the start of the final value.
  • Append: A comma delimited list of strings to add to the end of the final value.
  • Start: Indicates the starting position of a substring that should be extracted from the final value.
  • Length: Indicates the length of a substring that should be extracted from the final value.

Folders

CrystalWerx organizes tables into Folders. Each folder has a Name and its own Access Control List. Unless a specific list of team members is provided, by default all users have full access to a folder. If a specific list is provided then each team member on the list can be restricted to read-only access or tagged for edit logging.

Team Members

CrystalWerx provides a Team Editor that allows multiple individuals to edit the same table at the same time. Access to the Team Editor requires the username and password that is assigned to each Team Member.

Each Team Member has the following properties:
  • UserName: The unique username that the Team Member will use to logon.
  • Name: The actual display name of the Team Member.
  • Password: The password the Team Member uses to logon.
  • Excel User: Allows the user to operate the Excel Add-In.

Excel Add-In

The Excel Add-In is used to move data in and out of CrystalWerx. Any data that can be loaded in Excel can be uploaded to CrystalWerx, and any data in CrystalWerx can be downloaded into Excel.

DOWNLOAD EXCEL ADD-IN HERE

Team members that have the "Excel User" attribute will be able to log-in and use the Add-In.

Excel Gateway

CrystalWerx can transfer data into or out of an Excel Table.

  • Excel Sheet: The name of the Sheet that contains the Table that is the target or source of the data transfer.
  • Excel Table: The name of the Table that is the target or source of the data transfer.

Download

When data is downloaded from CrystalWerx an Excel Table is populated with data from a CrystalWerx Table. The Excel Table must have the same column headers as the selected CrystalWerx Table View.

  • CrystalWerx Folder: Choose a Folder to list Tables in that Folder.
  • CrystalWerx Table: The Table that contains the data source.
  • View: The Table View to use when retrieving data from the selected Table.
  • Columns: For convenience the columns in the selected View are displayed.
  • Max Rows: The maximum number of rows that will be downloaded.

Copy to Clipboard: Copies the columns of the selected View to the clipboard. The columns can then be pasted into Excel and then a new Excel Table can be inserted.

Upload - New Table

When data is uploaded the contents of an Excel Table can copied to a new CrystalWerx Table. When a new CrystalWerx Table is created it can be based on an Existing Table Type or a new Table Type can be automatically created.

  • CrystalWerx Folder: The name of the Folder where the new Table will be created.
  • CrystalWerx Table Name: The name of the new Table that will be created.
  • CrystalWerx Table Type: The name of the Table Type that will be used for the new Table. If the name of an existing Table Type is provided it will be re-used -- the columns in the Excel Table must match the columns in the existing Table Type. If no matching Table Type is found a new one will be created automatically using the column headers of the source Excel Table.

Upload - Append

When data is Uploaded the contents of an Excel Table can be appended to an existing CrystalWerx Table.

  • CrystalWerx Folder: Choose a Folder to list Tables in that Folder.
  • CrystalWerx Table: The Table that the data will be appended to.

SAP Gateway

Auhorized users can download data directly from an SAP gateway service into a CrystalWerx table using the SAP Import page.

  • Username: Enter the credentials of a user authorized to access the SAP gateway service.
  • Password: Enter the credentials of a user authorized to access the SAP gateway service.

Before downloading SAP data CrystalWerx needs to have a Table Type that describes the contents of the data. This can be done automatically by providing the MetaData URL of the SAP Gateway. CrystalWerx will download the MetaData and automatcially create Table Types that represent the SAP data.

  • MetaData URL: Typically just the base service URL with the /$metadata parameter at the end.

Once the necessary Table Types have been created data can be downloaded by entering the service URL and the location where the data will be saved.

  • Service URL: The OData query URL to fetch the SAP data.
  • Name for new Table: The name for a new Table that will contain the SAP data.
  • Create In Folder: The Folder where the new Table will be created.