Home | Access Data for Previously Printed Labels – LABEL ARCHIVE and TEKLYNX CENTRAL Instructions
Print or Email this article

Access Data for Previously Printed Labels – LABEL ARCHIVE and TEKLYNX CENTRAL Instructions

Summary

This Reference Article defines and explains the topic of how to retrieve data used in labels (e.g. serial numbers, manufacture dates, etc.) from LABEL ARCHIVE for labels printed.

Information

Note: this article assumes that you have a working knowledge of LABEL ARCHIVE, Microsoft Excel,SQL Management Studio and have basic understanding of database concept.

You can access label data (i.e. the variable data used in the label) for a particular label by one of two methods:

  1. Viewing label data using the ‘History…’ function – this is located in CODESOFT under the LABEL ARCHIVE menu (available when CODESOFT is connected to LABEL ARCHIVE)
  2. Use the Export feature – this can be found in the LABEL ARCHIVE Server Administrator BUT a customized SQL query is required to pull the information together

Method #1 – Viewing Label Data Using History…

  1. Select History from the File/Label Archive menu
  2. Select the label you want history then click on History tab
  3. Make sure that you select proper date range, and select the record on with the type Print then click History Data button.
  4. You will see the data that was used to print the label(s). Unfortunately, the information display on this window is for viewing only. You cannot copy or export it.

Method #2 – Using the Export feature and SQL Management Studio

The method above is sufficient is you just want to view small set of information.  If you need to get list of all variables for label printed during a certain time frame, there is an alternative. This alternative is, however, a bit more complicated and requires some knowledge of SQL.

  1. Delete existing data from the export destination, tbl.HistoryVars, table.
    1. Open SQL Management Studio and connect to the SQL server where LABEL ARCIVE database is located. Click on New Query
    2. In the new query window, enter the following command (you can copy and paste) and click on Execute.. Please exercise extreme caution when entering the command since it will delete everything from a table.  Incorrect table name will most likely resulted in to a corrupted system.  If you are not sure, please request assistant from IT professional.
  2. From LABEL ARCHIVE Server Administration, select Tasks/Export variables data.
  3. Select the proper date range and click Export.  You will get “Do you really want to export variables data?” message. Click Yes. You may question – Why did it not ask me we to export the information?  It is a fair and valid question.  The program exports the information into the tblHistoryVars table in the LABELARCHIVE database in SQL.  This may seems odd but the reason will become clear in the next step.The information that LABEL ARCHIVE Server Administration console exports only contain HistID, ItemIndex, VarName and VarValue. By themselves, they do not have sufficient information. We need to combine it with another table tblHistory.  The SQL statement in the steps below joins the information between the 2 tables (tblHistory and TblHistoryVars) to produce a more usable output.
  4. Switch back to SQL Management Studio. Click on New Query
  5. n the new query window, enter the following command (you can copy and paste) and click on Execute. –  “Select tblhistoryvars.histid, docid, docversion, histdate, username, workstation, histtype, histcomment, quantityprinted, itemindex, varname, varvalue from labelarchive.dbo.tblhistoryvars full outer join labelarchive.dbo.tblhistory on labelarchive.dbo.tblhistory.histid = labelarchive.dbo.tblhistoryvars.histid where histtype = 1  Order by histID, docversion”
  6. Right-click in the result window and select Select All
  7. Right click in the result window again and select Copy with Headers
  8. Open Excel with a new blank document and paste the data into the document

Here are the definition for each of the column in the spreadsheet

HistID Sequencial number assigned for each ‘group’ of printed label.
DocID The document number. Use in conjunction with DocVersion to locate a specific label template use for printing
DocVersion Version number of the document
HistDate Date printed
UserName User who printed the label
Workstation Name of the computer user used to print the label
HistType Always 1 in this case.  It indicates ‘print’ history
HistComment Generally contains the name of the printer that printed the label
QuantityPrinted number of label to be printed in the same request
ItemIndex number sequence number of label printed in the same request. For example, if QuantityPrint is 5, the ItemIndex for this print job will range from 0-4 (it starts at 0).
VarName Name of the variable in the label design
VarValue Value of the variable

Once you are familiar with the process, you can adjust the SQL statement in step 4 to better fit your requirements.

Author

Dave Klement

Was this article helpful?

Related Articles