When creating a query in Data Miner, the data you want to include on a report may not always appear in the desired format. For example, when creating a query that will display the salespeople at your dealership, the numeric code used to identify each salesperson will be displayed instead of the salesperson's actual name (ie. "01" as opposed to "John Smith"). In many situations however, linking to PFWTAB will allow you to extract descriptive field values as opposed to their associated codes (ie. "John Smith" as opposed to "01"). Furthermore, linking to PFWTAB can also allow you to display other data that would otherwise be excluded from the report, such as the email address or telephone numbers associated to the salespeople. Because PFWTAB contains multiple tables and "flat file" information however, in order to properly extract the desired data you must use precise substring criteria when creating your query using the steps outlined below.
Note: For the purposes of this procedure we will create a query that will list salespeople names as opposed to codes, as well as the email addresses associated to the salespeople. See the Appendix below the procedure for details on how to extract other pieces of data from PFWTAB.
-
From anywhere within IntelliDealer, hover over the Management Central link and select Utilities> Data Miner from the drop down list.
-
The Data Miner screen will appear.
Data Miner screen
-
On the Data Miner screen, click the Need to create a new query? Click here to add link.
The Add Query screen will appear.
Add Query screen
-
On the Add Query screen, enter the desired Query Name and a brief Description of the query, then click Save.
The Query tab will appear.
Query tab
-
From the Query tab, click the Files tab.
The Add File screen will appear.
Add File screen
-
On the Add File screen, click the Search icon next to the File field.
The Files screen will appear.
Files screen
-
On the Files screen, search for the desired primary file and click it to add it to the query. For the purposes of this procedure we will use the CMASTR file.
The Files tab will appear displaying your selection.
Files tab
-
You must now join the PFWTAB file to the primary file selected in the previous step. To do so, on the Files tab click the Yes link in the Add Join field.
The Add File screen will appear.
Add File screen
-
On the Add File screen, type PFWTAB in the File field and click Save.
The Select Join screen will appear.
Select Join screen
-
On the Select Join screen, join the Company and Division fields from the CMASTR file (CUCO and CUDIV) to their PFWTAB file equivalents (TBCO and TBDIV), then click Save.
The joins will be displayed on the Files tab.
Updated Files tab
Note: Depending on the type of data you want to extract from PFWTAB, the Company and Division joins may cause an error when running the query, however in order to proceed they must be created in this step and then removed after creating the required virtual fields.
For example, when extracting country names from PFWTAB you must create a join between the CUCO and TBCO fields in order to access the Virtual Fields tab. After creating the LINKKEY and COUNTRY virtual fields, you must then return to the Files tab and remove the CUCO| TBCO join in order to properly run the report. See the Appendix for details. -
Now that PFWTAB has been joined to the query, you must specify the data you want to extract. To begin, click the Virtual Fields tab.
The Virtual Fields tab will appear.
Virtual Fields tab
-
On the Virtual Fields tab, you must first create a "linkage" virtual field that will dictate which section of the PFWTAB file to extract for the report.
To do so, click the Click to add field link. The Add Virtual Fields screen will appear.
Add Virtual Fields screen
Note: Depending on the type of data you want to extract from PFWTAB, you may have to create multiple "linkage" virtual fields in order to pull the correct information for the report.
For example, in order to extract Province/State information, you must first create a "linkage" virtual field that will extract the Country information stored in PFWTAB. By doing so, it will narrow down the possible provinces and states stored in PFWTAB that may be displayed on the report. See the Appendix for details. -
On the Add Virtual Fields screen, enter the desired Virtual Field Name and select Character in the Type field.
The Add Virtual Fields screen will refresh to display new field criteria.
Updated Add Virtual Fields screen
-
In the Length field type 3 and select Substring in the Operator field.
The Add Virtual Fields screen will again refresh to display new field criteria.
Updated Add Virtual Fields screen
-
In the Field field, click the Search icon to locate the TBKEY file.
The Fields screen will appear.
Fields screen
-
On the Fields screen, select the TBKEY file.
The Field field will be populated on the Add Virtual Fields screen.
Updated Add Virtual Fields screen
-
In the Start At field type 13 and in the Substring Length field type 3, then click Save & New.
The Add Virtual Fields screen will refresh allowing you to create a new virtual field.Note: The exact substring criteria for the "linkage" virtual field will change depending on the data you are extracting from PFWTAB. See the Appendix for details.
-
Now that you have created the "linkage" virtual field, you must specify the exact data you want to extract from PFWTAB (in this case, we will be extracting the salesperson name, then the salesperson email address.)
To do so, enter the desired Virtual Field Name and select Character in the Type field.
The Add Virtual Fields screen will refresh to display new field criteria.
Add Virtual Fields screen
-
In the Length field type 25 and select Substring in the Operator field.
The Add Virtual Fields screen will again refresh to display new field criteria.
Updated Virtual Fields screen
-
In the Field field, select the TBDATA field and type 1 in the Start At field and 25 in the Substring Length field, then click Save & New.
The Add Virtual Fields screen will refresh allowing you to create a new virtual field. -
Repeat steps 18 - 20 to create a virtual field for the salesperson's email address, except use 45 for the Length and Substring Length and a Start At position of 54, then click Save & Close.
The virtual fields will be added to the query and displayed on the Virtual Fields tab.
Virtual Fields screen
-
Now that you have created the required virtual fields, you must join the linkage virtual field created in step 16 (LINKKEY) to the desired field in the primary file (in this case, the CUSMN field of the CMASTR file).
To do so, reopen the Files tab.
Files tab
Note: The primary file field and the linkage field you use in this step will depend on the data you are extracting from PFWTAB. In this case, a link to the CUSMN field will allow you to extract the salesperson name, email address, phone number, etc. from PFWTAB.
In cases where you have to create multiple linkage fields (such as when you want to extract Province /State information from PFWTAB) use the primary file field that contains the desired information (CUPRV) and the linkage field that uses the correct substring criteria, as per the Appendix below. -
On the Files tab, click the CUCO|TBCO, CUDIV|TBDIV link in the Join Field field.
The Select Join screen will appear.
Select Join screen
-
On the Select Join screen, select the CUSMN field from the CMASTR file and join it to LINKKEY file, then click Save.
The Files tab will appear displaying the updated joined fields.
Updated Files tab
-
You may now select the columns you want to include on the report.
To do so, click the Columns tab. The Select Field screen will appear.
Select Field screen
-
On the Select Field screen, locate and select the desired columns and click Save.
The selected columns will appear on the Columns tab.
Columns tab
Note: To change the order in which the selected columns will appear on the report, use the arrows in the Move field.
-
To populate the rows of the report, you must now create a select for the specific table in PFWTAB that contains the desired information. In this case, CMSMN.
To do so, click the Rows tab. The Rows tab will appear.
Rows tab
-
On the Rows tab, click the Click here to add selection criteria link.
The Selection Criteria screen will appear.
Selection Criteria screen
-
On the Selection Criteria screen, enter or select TBFILE in the Field field, Equal To in the Operator field and CMSMN in the Value field, then click Save & Close.
The selected criteria will appear on the Rows tab.Note: The selection criteria will always be the same except for the table name entered in the Value field, which will vary depending on the data you are extracting from PFWTAB. See the Appendix for details.
Updated Rows tab
-
Click Run Report.
The report will be generated in the selected format displaying the salesperson name (as opposed to the salesperson code) and email address.
Complete Report
Depending on the type of data you want extract and display from PFWTAB, you will have to use different linkage and substring criteria in your query. Use the examples below to properly extract the desired information:
To extract customer salesperson information:
Primary File: CMASTR
Joins: CUCO| TBCO, CUDIV|TBDIV, CMSMN| Linkage field 1
Virtual Fields:
| Name | Data Extracted From PFWTAB |
Type | Length | Operator | Associated Field | Start At | Substring Length |
|---|---|---|---|---|---|---|---|
| Linkage Field 1 | NA (Linkage field) | Character | 3 | Substring | TBKEY | 13 | 3 |
| Data Field 1 | Salesperson name | Character | 25 | Substring | TBDATA | 1 | 25 |
| Data Field 2 | Salesperson location | Character | 2 | Substring | TBDATA | 26 | 2 |
| Data Field 3 | Salesperson telephone # | Character | 13 | Substring | TBDATA | 28 | 13 |
| Data Field 4 | Salesperson cell phone # | Character | 13 | Substring | TBDATA | 41 | 13 |
| Data Field 5 | Salesperson email address | Character | 45 | Substring | TBDATA | 54 | 45 |
Selection Criteria (Rows): 'TBFILE Equal To CMSMN'
To extract customer category information:
Primary File: CMASCP
Joins: CPCO| TBCO, CPCCAT| Linkage field 1
Virtual Fields:
| Name | Data Extracted From PFWTAB |
Type | Length | Operator | Associated Field | Start At | Substring Length |
|---|---|---|---|---|---|---|---|
| Linkage Field 1 | NA (Linkage field) | Character | 3 | Substring | TBKEY | 13 | 3 |
| Data Field 1 | Category | Character | 20 | Substring | TBDATA | 1 | 20 |
Selection Criteria (Rows): 'TBFILE Equal To AREA'
To extract customer country information:
Primary File: CMASTR
Joins: CUCTRY| Linkage field 1
Note: In order to join the linkage field to CUCTRY, you must first create a join between two arbitrary fields in the CMASTR file and PFWTAB (for example, CUCO| TBCO). After creating the linkage field and joining it to CUCTRY, remove the join between CUCO| TBCO.
Virtual Fields:
| Name | Data Extracted From PFWTAB |
Type | Length | Operator | Associated Field | Start At | Substring Length |
|---|---|---|---|---|---|---|---|
| Linkage Field 1 | NA (Linkage field) | Character | 2 | Substring | TBKEY | 14 | 2 |
| Data Field 1 | Country | Character | 20 | Substring | TBDATA | 1 | 20 |
Selection Criteria (Rows): 'TBFILE Equal To Country'
To extract customer province/ state information:
Primary File: CMASTR
Joins: CUPRV| Linkage field 2
Note: In order to join the linkage field to CUPRV, you must first create a join between two arbitrary fields in the CMASTR file and PFWTAB (for example, CUCO| TBCO). After creating the linkage field and joining it to CUPRV, remove the join between CUCO| TBCO.
Virtual Fields:
| Name | Data Extracted From PFWTAB |
Type | Length | Operator | Associated Field | Start At | Substring Length |
|---|---|---|---|---|---|---|---|
| Linkage Field 1 | NA (Linkage field) | Character | 2 | Substring | TBKEY | 12 | 2 |
| Linkage Field 2 | NA (Linkage field) | Character | 2 | Substring | TBKEY | 14 | 2 |
| Data Field 1 | Province/State | Character | 20 | Substring | TBDATA | 1 | 20 |
| Data Field 2 | Province/ State abbreviated name | Character | 2 | Substring | TBDATA | 21 | 2 |
Selection Criteria (Rows): 'TBFILE Equal To PRVTAB'
To extract customer county information:
Primary File: CMASTR
Joins: CUCO| TBCO, CUDIV| TBDIV, CUCTY| Linkage field 3
Virtual Fields:
| Name | Data Extracted From PFWTAB |
Type | Length | Operator | Associated Field | Start At | Substring Length |
|---|---|---|---|---|---|---|---|
| Linkage Field 1 | NA (Linkage field) | Character | 2 | Substring | TBKEY | 1 | 2 |
| Linkage Field 2 | NA (Linkage field) | Character | 2 | Substring | TBKEY | 3 | 2 |
| Linkage Field 3 | NA (Linkage field) | Character | 3 | Substring | TBKEY | 5 | 3 |
| Data Field 1 | County | Character | 20 | Substring | TBDATA | 1 | 20 |
Selection Criteria (Rows): 'TBFILE Equal To VTCUCTY'
To extract the category associated to a customer call:
Primary File: CMASCP
Joins: CPCO| TBCO, CPCCAT| Linkage field 1
Virtual Fields:
| Name | Data Extracted From PFWTAB |
Type | Length | Operator | Associated Field | Start At | Substring Length |
|---|---|---|---|---|---|---|---|
| Linkage Field 1 | NA (Linkage field) | Character | 3 | Substring | TBKEY | 13 | 3 |
| Data Field 1 | Call category | Character | 20 | Substring | TBDATA | 1 | 20 |
Selection Criteria (Rows): 'TBFILE Equal To CALLCAT'
To extract the type associated to a customer call:
Primary File: CMASCP
Joins: CPCO| TBCO, CPTYP| Linkage field 1
Virtual Fields:
| Name | Data Extracted From PFWTAB |
Type | Length | Operator | Associated Field | Start At | Substring Length |
|---|---|---|---|---|---|---|---|
| Linkage Field 1 | NA (Linkage field) | Character | 2 | Substring | TBKEY | 14 | 2 |
| Data Field 1 | Call type | Character | 20 | Substring | TBDATA | 1 | 20 |
Selection Criteria (Rows): 'TBFILE Equal To CALLTYPE'
To extract the reason associated to a customer call:
Primary File: CMASCP
Joins: CPCO| TBCO, CPPUR| Linkage field 1
Virtual Fields:
| Name | Data Extracted From PFWTAB |
Type | Length | Operator | Associated Field | Start At | Substring Length |
|---|---|---|---|---|---|---|---|
| Linkage Field 1 | NA (Linkage field) | Character | 2 | Substring | TBKEY | 14 | 2 |
| Data Field 1 | Call reason | Character | 20 | Substring | TBDATA | 1 | 20 |
Selection Criteria (Rows): 'TBFILE Equal To CALLPUR'
To extract the result associated to a customer call:
Primary File: CMASCP
Joins: CPCO| TBCO, CPRES| Linkage field 1
Virtual Fields:
| Name | Data Extracted From PFWTAB |
Type | Length | Operator | Associated Field | Start At | Substring Length |
|---|---|---|---|---|---|---|---|
| Linkage Field 1 | NA (Linkage field) | Character | 2 | Substring | TBKEY | 14 | 2 |
| Data Field 1 | Call result | Character | 20 | Substring | TBDATA | 1 | 20 |
Selection Criteria (Rows): 'TBFILE Equal To CALLRES'