About the Data Source Editor

Once you have saved a new data source record, the Data Source Editor tab becomes available. Described below are the different areas in the Data Source Editor and how to use them to build your data source.

Suggested Fields and Field Library lists

The Suggested Fields are the simplest to add to a data source. Drag the field form the left and add it to the column area on the right. Any field in this list will work without any additional steps or editing. The Suggested Fields list displays the following sub lists, all of which are based upon the Module selected on the Main tab:

• Common Fields - The most commonly used fields groups and their associated fields, like the Required and Location field groups.

• Additional Fields - Fields less commonly used in data sources, like Item Code or Cause Code.

• Custom User Fields - These are custom fields that you might have created in the selected module.

• My Joined Tables - These are the tables being automatically joined by your field selections. For example, if you were to select the WO Number field and the Resource Last Name field, the vWOAssignments table view would appear in this sub list, and you could select other fields from this table.

• Related Tables - A list of tables related to the Work Orders module, such as Time Charges tables and Assets tables.

• Summary Data - A list of table views, such as IDWorkOrderParent.

The Field Library has a list of fields organized by each section of the database. To add a field to the Field Library, drag it from the field pane on the left and drop in the column area. Fields from the Field Library are not always directly related to the main module of the data source. For TMS to understand how to relate this information, you may need to add a join in the Advanced tab of the data source.

Field icons

To clarify what type of data is displayed by a certain field, table, or view, there is an icon next to the name. These are the five type icons:

	Numeric field	WO Number, Total Cost
	Text or string field	WO Description, Asset Number
	True or false field	Has Assignments, Has Documents

This icon only appears next to a field in the Column Area drop zone created by selecting Add Formula Field and then a Function Type:

Formula field

MINUS, MULTIPLIED (Mathematical Functions); GETDATE, MONTH (Date Functions)

Formula Fields

Formula fields can be added using the Add Formula Field button in the column area. Formula fields allow you to get specific information from a field, perform calculations between fields or get other useful information. You can hover the mouse over each formula field in the list to see what that formula field does.

Most formula fields have options and/or parameters that need to be set for that formula field to function. After adding the formula field you can click the gear icon on the right side of the field to set the options. Some options have the drop-down button to switch between a specific value and a drop-down to select another field from the data source. The list of fields available when using the drop-down is the same as the fields currently in the column area.

Drop Zones

Drop zone are the areas in the data source editor where you will place the fields you select for inclusion in the data source, whether as a column, row, the data, or as a field by which the retrieved data should be filtered. There are two drop zones in the editor, discussed below.

Column Area

The Column Area drop zone is where you will drop fields to use as the body of your data source, the columns, rows, and returned data. This drop zone contains an Add Formula Field button, to allow you to create a new formula field, and a Cross Tab toggle button. Selecting Cross Tab: On allows you to structure your data in an Excel-like format.

To add a field to this zone from the Suggested Fields or Field Library lists, you click the field in the list to add and then drag it into the Column Area drop zone. You don't need to worry about the order of the fields as you drag them over, because you can rearrange their order at any time.

Once you add them to this drop zone, you can establish how the fields will appear on the data source. These options are discussed in the Data Display section below.

Each field that is added to this drop zone (as long as no errors are generated) will return a data set that displays below the Column Area zone. Any field options you set, or field or filter additions you make are reflected in the data set. When you make a change to the data source, the data set is refreshed to reflect a real-time view of your data.

Filter By Area

The Filter By Area drop zone is available to you if you have not selected a saved query on the Main tab and the Use On-The-Fly Ad-Hoc Query is not checked. If either of these conditions are in effect, the Filter By Area will be absent from the data source editor. Clear the saved query function or the "On-The-Fly" option will add the drop zone to the editor.

Move a Field

If you want to move a field once you add it to the Column Area, you can hover your mouse cursor over the field's handle on the left side of the field box. The cursor will turn into a four-sided arrow. Click the field's handle and drag it to where you want the field to display.

Data Display

In the data source editor, you are given the ability to adjust certain, basic properties for how your data fields will display on the data source (the column field label, data summary functions, and sort by options). More advanced formatting is reserved for report definitions, to which your data source can later be linked.

You also have the ability to select fields by which the data retrieved by this data source will be filtered. For example, you can limit the work order records returned by a data source to only specific Status codes.

Field Options

When you add a field to the Column Area, you will notice that there is a small, circle-shaped icon to the right of the field name. It looks like a cog or a gear.

When you click this icon, a drop-down menu appears, where you can define the options for the selected field. See the table below for detailed descriptions of each option.

Field	Description
Delete (the X button)	If you want to delete this field from the Column Area, you click this button. You can also delete the field by right-clicking on the field box and clicking the Delete label that pops up.
Field Name	This field displays the TMS field name. It provides a reference for you, in case a Caption is applied to the field to change the displayed field name. The Field Name cannot be changed, only Caption can.
Caption	This field allows you to define how the data field's label will display on the data source.
Summary Function	This drop-down menu is where you can define summary options for the field (like Count or Sum).
Sort By	If you want the data source to be sorted by a specific field, select the Sort By option in this drop-down menu, either Ascending or Descending.
Mask	For date fields only - if you want to apply a mask to a date field you add to your data source, you can select one of the preset masks in the Mask drop-down menu.
Date Part	For date fields only - if you want only part of the date, such as the Day, to display for date values returned by the data source, you can select one of the preset date parts from the Date Part drop-down menu.

Filter By Options

Filters allow you to narrow down the information that the data source will show. For example, if you had a work order data source and you only wanted to see active work orders, you could add a filter that would look for a status of Active.

To add a filter drag the field you want to filter by from the field pane and drop it in the filter by area.

When you add fields to the filter by area, you have the option of specifying the field's filter criteria, or making one or more of the filter fields a "prompt" filter field (meaning the user is prompted for the filter value, when the data source is run). If you specify the field's filter criteria, just like with an Advanced Query created on a module's Query screen, you can select more than one item of criteria per field.

You'll notice in the screen shot below, we have added more than one field to filter by. When more than one field is added to the filter by area, it is automatically added as an And to the filter. Again, just like with an Advanced Query, both sets of field criteria must be met for a record to be returned.

The controls available for each filter field are as follows:

Control	Description
	This button deletes the field from the Filter By Area zone.
	This button opens the Prompt options menu. The menu supplies you with a Prompt check box to enable the option and a Prompt Label text box so that you can specify the label to display when the user is prompted for input.
	Like the fields in the Column Area zone, filter fields have a handle next to the Prompt Options button shown above. Hovering over this handle creates a four-sided arrow cursor, indicating that you can drag the field to another position in the filter.
	This button deletes the item of criteria, not the filter field, if you have more than one item of criteria for the field.
	Use this drop-down menu to select the conditions for the criteria item, such as Is Not Empty or Contains.
	Use this drop-down menu to select the criteria's value.
	This button allows you to input an additional item of criteria for the field. For example, ACTIVE OR PENDING.

When you add more than one item of criteria for a filter field, the additional criteria is added below the first item of criteria, and so on. If we use the example in the screen shot above and add additional items of criteria for both fields, it would look like this:

According to the criteria above, work order records must be in a Status of Active or Pending, and they must have a Type code of Corrective Maintenance or Service Call, in order to be returned by this data source. To set the filter, you click the Apply Filter button.

Prompt filter

Prompts allow you to enter filter information at the time the data source is generated (or when a report using the data source is generated). For example, if you created a report that showed information about a specific work order type code, you could make a prompt filter for the type code. When the data source is generated, you could enter the type you wanted to run the data source or report for at that time.

To make one of your filters a prompt, select the check box next to Prompt. This will add a Prompt Label field. If the Prompt Label is left blank, it will use the field name for the prompt.

When using the Prompt option on a date field, the application will prompt for a From date and a To date. This way you can enter the date range that you want the date field to be within.

Dynamic filter

Dynamic filters allow a data source to filter based on the currently logged in user. The dynamic filter is not available for all fields. The most common use of dynamic filters is for the Resource Number. This way one data source can show different users the work orders that are assigned to them, or the time charges for their resource.

To make one of your filters a dynamic filter, select the check box next to Dynamic Value.

The dynamic option for resource number will only work if the logged in user has a resource tied to their user account.