Adding a formula

In some cases, you may need to use a formula to transform one or more values from the query result sets into a valid entry for a GainSeeker Field that can be stored in SPC and/or DMS.  

You can use step 5 of the Database Integration Wizard to set up two types of formulas: basic formulas and cycle time formulas.

Basic formula

To enter a basic formula into a cell on the grid, drag the TODO: Edit formula later item into the cell, or right-click on the cell and select Add TODO: Edit formula later. This displays a window where you can enter a formula or comments.  

Whether you enter a valid formula or simply a comment in this window, the Database Integration Wizard will format your input as 'TODO: your input' in the cell.  

Before using or testing the template to import data, you must edit the template using the Template Wizard to remove the quotation marks and the TODO: text, and to verify that the formula is correct.

Cycle Time formula

You can use a Cycle Time formula to calculate the difference between two dates and/or times, and then report that difference in units of days, hours, minutes, or seconds.  This is particularly useful when measuring service data such as the amount of time a customer spends on hold waiting for a support representative, or the amount of time needed to process orders or service requests.

To enter a Cycle Time formula, right-click on the cell and select Cycle Time Formula.  This displays the Calculate Date Difference window as shown below:

Note:  The cycle time formula you enter here will replace any values or formulas that already exist in this cell of the grid.  You can re-enter additional values and formulas after entering the cycle time formula.

  1. Right-click in the Start Date box and select the value for the starting date of the event.

  2. Choose the date format and order that best represents the data in your query result set.

  3. Right-click in the End Date box and select the value for the starting date of the event.

  4. Choose the date format and order that best represents the data in your query result set.

  5. Choose the unit of time used to report the date difference.

  6. Click Finish.

  7. The finished template formula is entered in the selected cell on the grid.

Viewing formulas

In many cases, the grid displays a simplified or abbreviated form of the actual template formula that will be used for the GainSeeker Field.  

To view the actual formula for a cell on the grid, right-click the cell and select View Designer Formula.