XML and Record Buffer Template Commands

XML and Record Buffer template commands give you more control over the source and destination of the data. It also further facilitates the template writer's ability to handle both SPC and DMS data in any given template. This functionality comes in two parts: the ability to fill a temporary record buffer using mapping commands, and also the ability to store the data using pre-processed SQL syntax.

Placing the data in the record buffer is achieved by making SPC Designer understand the tags already used in the Import Export Manager to process the data. The template command will interpret any XML file that uses our tags correctly. If the XML file does not use our default tags, other commands are available which allow the user to "map" their tags into ours so they can be placed correctly in the record buffer. There are template commands to open, read and close XML files.

Record Buffer overview

The record buffer works like a collection of storage bins, each holding a piece of information. When a line from an XML file is read in, it puts the part number value into the corresponding bin in the record buffer. You can retrieve the value from the bin with the DB_VALUE command or manually set the value of a bin with the DB_SET_VALUE command. Both of these commands take a 'tag' which designates the bin from or in which the program should retrieve or store the value. If you retrieve a value from a bin that has not been set (either from reading an XML file or with the DB_SET_VALUE), it will return the default value for that bin.

Record Buffer SQL commands

The GainSeeker Suite has template commands to help you store database records with the information in the record buffer. When the record buffer contains the information you want stored, use one of the record buffer SQL commands to create the VALUES portion of an SQL 'INSERT' string. The string contains all the columns in correct order, separated by commas and the single quote character delimits the strings. If you want to perform other SQL commands, such as UPDATE, you must build the string yourself using the DB_VALUE command to retrieve the information. The PREP_Q command can be used to prepare strings to be inserted into tables. Following are the four record buffer SQL commands:

DB_DATA_SQL

This will return the VALUES portion of an INSERT string for the DDATA (DMS Data) table. Currently, only the "DMS" database type is implemented for this command. To store "SPC" data, retrieve values from the record buffer into template columns and use the template to store the data.
DB_DATA_AUX_SQL

This will return the VALUES portion of an INSERT string for the DDAT_AUX (DMS Auxiliary Data) table. Currently, only the "DMS" database type is implemented for this command. To store "SPC" data, retrieve values from the record buffer into template columns and use the template to store the data.

If you have removed unused columns from the DDAT_AUX table in the current configuration, this command will automatically detect which traceability columns remain and will adjust the SQL string accordingly.
DB_NOTE_SQL

This will return the VALUES portion of an INSERT string for the DNOTE (DMS Notes) table. Currently, only the "DMS" database type is implemented for this command. To store "SPC" notes, retrieve values from the record buffer into template columns and use the template to store the data.
DB_STD_SQL

This will return the VALUES portion of an INSERT string for the VSTDS (SPC Standards) table or the DSTDS (DMS Standards) table.

GainSeeker tags

Record buffer commands use "tags" to identify the storage bins used to set or retrieve information.

There is a storage bin in the record buffer for each column in the following database tables: VDATA, VDAT_AUX, VNOTE, DDATA, DDAT_AUX, DNOTE, VSTDS. Some of the columns in these tables may be shared. For example, the Part Number column exists in all seven of these tables, but there is only one storage bin for Part Number.

You may change the name of a tag using the XML_MAP template command. For details, see Mapping Tags.

Following is a list of the GainSeeker tags for use with the record buffer commands. The tags are grouped by database table. All tags are case sensitive.

SPC Data tags

VDATA table:

Tag Name	Shared	Additional Information
"Part_Number"	X
"DateTime"	X
"Trace_1" through "Trace_6"	X	traceability fields 1-6
"Bypass"
"Event"	X	returns full Event description
"Cause"	X	returns full Cause description
"Action_Taken"	X	returns full Action Taken description
"RTF"	X	real-time failure information
"VFlags"		data record information
"Subgroup_Size"		automatically set from value in the standard, but can be overwritten
"Data_Value1" through "Data_Value8"		the first 8 data values in the subgroup The DB_SET_VALUE command converts any non-numeric value (such as the * symbol for a missing data value ) to a blank value. The DB_VALUE command returns the number -999 if a blank value is found. The XML tag named (or mapped to) "Data_Value" will also read data into these data bins. The first "Data_Value" tag in a record will be read into the "Data_Value1" bin, the second "Data_Value" tag in a record will be read into the "Data_Value2" bin, and so forth.

VDAT_AUX table:

Tag Name	Shared	Additional Information
"Part_Number"	X
"DateTime"	X
"Trace_7" through "Trace_48"	X	traceability fields 7-48
"Data_Value9" through "Data_Value72"		data values 9 through 72 in the subgroup The DB_SET_VALUE command converts any non-numeric value (such as the * symbol for a missing data value ) to a blank value. The DB_VALUE command returns the number -999 if a blank value is found. The XML tag named (or mapped to) "Data_Value" will also read data into these data bins. The ninth "Data_Value" tag in a record will be read into the "Data_Value9" bin, the tenth "Data_Value" tag in a record will be read into the "Data_Value10" bin, and so forth.

VNOTE table:

Tag Name	Shared	Additional Information
"Part_Number"	X
"DateTime"	X
"Note"	X
"Last_Field"		read-only
"RTF"	X	real-time failure information

DMS Data tags

DDATA table:

Tag Name	Shared	Additional Information
"Part_Number"	X
"DateTime"	X
"Process"	X
"Trace_1" through "Trace_6"	X	traceability fields 1-6
"Event"	X	returns full Event description
"Sample_Size"
"NCU"
"Total_Defects"
"DMS_Note_Flag"		cannot be re-mapped
"Defect_Description1" through "Defect_Description20"		The XML tag named (or mapped to) "Defect_Description" will also read data into these data bins. The first "Defect_Description" tag in a record will be read into the "Defect_Description1" bin, the second "Defect_Description" tag in a record will be read into the "Defect_Description2" bin, and so forth. The XML tag named (or mapped to) "Defect_ID" will also read data into these data bins. The first "Defect_ID" tag in a record will be read into the "Defect_ID1" bin, the second "Defect_ID" tag in a record will be read into the "Defect_ID2" bin, and so forth. Best practice is to use either Defect_Description or Defect_ID tags. You can use both at the same time, but this can become confusing. Note: When doing a DB_SET_VALUE for Defect_Description, it is best have the defect, process, and Part Number already created.
"Defect_ID1" through "Defect_ID20"
"Defect_Count1" through "Defect_Count20"		The XML tag named (or mapped to) "Defect_Count" will also read data into these data bins. The first "Defect_Count" tag in a record will be read into the "Defect_Count1" bin, the second "Defect_Count" tag in a record will be read into the "Defect_Count2" bin, and so forth.

DDAT_AUX table:

Tag Name	Shared	Additional Information
"Part_Number"	X
"Process"	X
"DateTime"	X
"Trace_7" through "Trace_48"	X	traceability fields 7-48

DNOTE table:

Tag Name	Shared	Additional Information
"Part_Number"	X
"Process"	X
"DateTime"	X
"Note"	X

SPC Standard tags

VSTDS table:

Tag Name	Shared	Additional Information
"Part_Number"	X
"Descript"		Description
"Num_Dec"		Number of decimals. For information on setting the default value for this tag, see Defaults for new SPC standards.
"R_Chart"		R Chart type. For information on setting the default value for this tag, see Defaults for new SPC standards.
"Sub_Size"		Subgroup size. For information on setting the default value for this tag, see Defaults for new SPC standards.
"Meas_Unit"		Measurement unit. For information on setting the default value for this tag, see Defaults for new SPC standards.
"De_Const"		Data Entry Constant Note: If used, the decimal symbol stored in this field must be a period (.). When the standard is accessed during data entry, data analysis, or editing of the standard, the decimal symbol will be automatically formatted for the Regional Options configured in Microsoft Windows.
"RT_Chk"		Real-time checks to perform For information on calculating valid settings for this tag, see Real-time Failure codes. For information on setting the default value for this tag, see Defaults for new SPC standards. Note: To reset this tag to the default real-time checks specified in this configuration, set this tag to '-1'.
"Ind_LS"		Lower specification	For these tags: The DB_SET_VALUE command converts any non-numeric value to a blank value. The DB_VALUE command returns the number -999 if a blank value is found.
"Ind_US"		Upper specification
"Sub_LG"		Lower subgroup gate
"Sub_UG"		Upper subgroup gate
"Rng_LG"		Lower range gate
"Rng_UG"		Upper range gate
"Ind_LG"		Lower individual gate
"Ind_UG"		Upper individual gate
"Reas_Lo"		Lower reasonable limit
"Reas_Hi"		Upper reasonable limit
"Scale_Lo"		X chart lower scaling value
"Scale_Hi"		X chart upper scaling value
"Scaler"		R chart scaling value
"Target_X"		Target X-bar
"Target_R"		Target R-bar
"VStd_Flags"		This tag determines when data records for this standard will be added to the monitor tables. It also determines whether the setting for Lower Control limit cannot be less than 0 will be selected or cleared on this standard. To set the value for this tag, choose the settings you want to apply and then add their values together: • 0 - Never write to monitor tables; clear the check box for Lower Control limit cannot be less than 0 • 1 - Write to monitor tables for data without failures • 2 - Write to monitor tables for real-time failures • 4 - Select the check box for Lower Control limit cannot be less than 0 For example, setting this tag to a value of 7 (1 + 2 + 4) would write to monitor tables for all data (with and without real-time failures) and select the check box for Lower Control limit cannot be less than 0. For information on setting the default value for the Write to monitor tables option, see Defaults for new SPC standards.
"Short_Run_Index"		'0' for standards, '1' through '6' for process specifications
"Short_Run_Method"		Coding method for process specifications Valid settings for this flag include: • 1 - Target/Nominal • 2 - Short Run • 3 - Standardized • 4 - Uncoded
"Metric"		Measurement system. Use '0' for English and '1' for metric measurement system.
"Use_Exp"		Number formatting information
"Expo"		Number formatting information
"Dms_PN"		Linked DMS Part Number
"Dms_Proc"		Linked DMS Process
"Std_Var1" through "Std_Var4"		Standard Variables 1-4

DMS Standard tags

DSTDS table:

Tag Name	Shared	Additional Information
"Part_Number"	X
"Process"	X
"Ncu_Cost"		Cost of each bad part
"Num_Opp"		Number of opportunities. For information on setting the default value for this tag, see Defaults for new DMS standards.
"SSize"		Sample size. For information on setting the default value for this tag, see Defaults for new DMS standards.
"LGate"		Lower Gate (positive number) or Acceptable Yield % (negative number down to -100) The DB_SET_VALUE command converts any non-numeric value to a blank value. The DB_VALUE command returns the number -999 if a blank value is found.
"UGate"		Upper Gate (positive number) or Goal Yield % (negative number down to -100) The DB_SET_VALUE command converts any non-numeric value to a blank value. The DB_VALUE command returns the number -999 if a blank value is found.

Mapping Tags

The GainSeeker Suite provides two commands to map override the default GainSeeker tags. The first, XML_MAP, is used to map most XML tags to the from an XML file to the record buffer. The second, XML_DEFECT_MAP, is used to map XML defect descriptions to the GainSeeker DMS Defects.

Using XML_MAP, you can map a tag (field) in an XML file into multiple GainSeeker tags (multiple bins in the record buffer). For example, you can map the < PN> field in the file to both the Part_Number tag and the Trace_6 tag. Doing this would put the value for the < PN> field of the XML file in both bins of the record buffer. However, you can only map one field into each GainSeeker tag. If you do more than one, the last one takes precedence. For example, if you map the < PN> tag to Trace_6 and then map the <Operator> tag to Trace_6, the <Operator> mapping replaces the < PN> mapping.

Using XML_DEFECT_MAP, you can map a defect description in an XML file into only one GainSeeker defect description. If you do more than one, the last one takes precedence. For example, if you map the <b1> tag to "Broken" and then map the <b1> tag to "Busted", the "Busted" mapping replaces the "Broken" mapping. Also, you can only map one defect description into each GainSeeker tag. If you do more than one, the last one takes precedence. For example, if you map the <b1> tag to "Broken" and then map the <b2> tag to "Broken", the <b2> mapping replaces the <b1> mapping.

If need to clear mappings, use the INITVARS command. Closing the XML file does not clear the maps.

Resetting Lists and the Record Buffer

Sometimes it is necessary to clear fields in the record buffer. For example, if you have set the record buffer with values and stored a record, you might want to clear some of the fields before you start setting values for the next record. The DB_RESET command can be used to clear a number of fields in one easy call.

For speed reasons, the GainSeeker Suite will read some smaller tables into memory when the software is loaded. The software doesn't know about changes that are made to these lists after it has loaded. The RESET_LIST command will allow the user to reset some of these lists by re-reading them directly from the database tables.

Sample templates

Following are three very basic templates that use the XML and record buffer commands in a variety of ways. See the Template Commands Reference for more information on the commands used in the formulas.

Sample template #1 description:

This template uses commands to read data from a XML and store SPC data records. A sample record of the XML file looks as follows:

<High_Spec>1.300</High_Spec>

<Low_Spec>-999</Low_Spec>

</Record>

Sample template #1 flow:

Map data to the correct bins in the record buffer
Open the XML file
Read a record from the XML file into the record buffer
Store the SPC data in the database
Go to step 3

Sample Template #1

Cell	Column	Method	Formula
A1	Calculated	Formula	XML_MAP "PN", "Part_Number" : XML_MAP "DT", "DateTime": XML_MAP "T6", "Trace_6": XML_MAP "DP", "Data_Value" : XML_MAP "High_Spec", "Ind_US" : XML_MAP "Low_Spec", "Ind_LS"
B1	Calculated	Formula	XML_OPEN "c:\SPC_Data2.xml"
C1	Calculated	Formula	XML_NEXT
D1	Part Number	Formula	DB_VALUE "PN"
E1	Operator	Formula	DB_VALUE "T6"
F1	Data 1	Formula	DB_VALUE "DP1"
G1	Data 2	Formula	DB_VALUE "DP2"
H1	Data 3	Formula	DB_VALUE "DP3"
I1	Calculated	Formula	SaveRow: Goto [C1]

Sample template #2 description:

This template uses commands to read data from a text file and store SPC data records. It uses the record buffer bins as temporary storage. A sample of the text file looks as follows:

BMW,4/8/2002,Juan,1.14,1.15,1.24,1.3,-999

Sample template #2 flow:

Open the text file
Read a record from the text file
Set the data to the correct bins in the record buffer
Store the SPC data in the database
Go to step 2

Sample Template #2

Cell	Column	Method	Formula
A1	Calculated	Formula	FOPEN 1, "R", "c:\SPC_DB.csv"
B1	Calculated	Formula	FREAD 1
C1	Calculated	Formula	DB_SET_VALUE "Part_Number", COL_S 1: DB_SET_VALUE "Trace_6", COL_S 3 : DB_SET_VALUE "Data_Value1", COL_N 4 : DB_SET_VALUE "Data_Value2", COL_N 5 : DB_SET_VALUE "Data_Value3", COL_N 6 : DB_SET_VALUE "Ind_US", COL_N 7: DB_SET_VALUE "Ind_LS", COL_N 8
D1	Part Number	Formula	DB_VALUE "PN"
E1	Operator	Formula	DB_VALUE "T6"
F1	Data 1	Formula	DB_VALUE "DP1"
G1	Data 2	Formula	DB_VALUE "DP2"
H1	Data 3	Formula	DB_VALUE "DP3"
I1	Calculated	Formula	SaveRow: Goto [B1]

Sample template #3 description:

This template uses commands to read data from a XML and store DMS data records. The DMS records are stored through an ODBC connection. A sample record of the XML file looks as follows:

<DMS_Record>

<PN>Z-RAW MATERIAL 1</PN>

<Proc>Z-INCOMING</Proc>

<T6>Carlos</T6>

<Non_conf>1</Non_conf>

<DDesc>Broken</DDesc>

<DDesc>Damaged</DDesc>

</DMS_Record>

Sample template #3 flow:

Map data to the correct bins in the record buffer
Connect to ODBC datasource to store DMS data
Open the XML file
Read a record from the XML file into the record buffer
Store the DMS data in the database through ODBC Connection
Go to step 4

Sample template #3:

Cell	Column	Method	Formula
A1	Calculated	Formula	XML_MAP "PN", "Part_Number" : XML_MAP "Proc", "Process" : XML_MAP "DT", "DateTime": XML_MAP "T6", "Trace_6": XML_MAP "SSize", "Sample_Size" : XML_MAP "Non_Conf", "NCU" : XML_MAP "TDefs", "Total_Defects" : XML_MAP "DDesc", "Defect_Description": XML_MAP "DCount", "Defect_Count"
B1	Calculated	Formula	Q_CONNECT 1, "GainSeeker71_B", "Y", "N"
C1	Calculated	Formula	XML_OPEN "c:\DMS_Data.xml"
D1	Calculated	Formula	XML_NEXT
E1	Part number	Formula	SVar 1=Concat "Insert into ", Table "ddata", " values (", DB_DATA_SQL "DMS", ")"
F1	Operator	Formula	NVar 1=Q_START Svar 1
G1	Calculated	Formula	Goto [D1]

Sample template #4:

Click here for a sample template that stores defect data.