Label printing software establishes an ODBC connection to a SQL database. Since Dynamics GP transactions reside in multiple tables, and move from open, to work, to history tables during the posting process, it is very difficult to use this ODBC functionality to print labels from Dynamics GP transactions because a different report would be needed for every transaction type.
Additionally, Dynamics GP space fills strings. If you attempt to print labels directly from a GP table, the software may pick up these spaces and not print the bar code correctly (it will contain the symbol for space). When SQL Dates are pulled into the reports they will display the full datetime value. To address this, all date fields in the LabelLink table are formatted to display only date values.
LabelLink simplifies the use of the label printing software’s ODBC functionality by pulling together Dynamics GP information from multiple tables to populate a single flat-table.
The reporting table is a SQL View called LabelLink, and is located in the company database. The source fields for the Bar Code in the label printing software, (such as Item Number or Lot Number,) should come from the LabelLink table. String fields in other tables in Dynamics GP are filled with ending spaces and may not print correctly when used in the bar code.
Most GP string fields are filled with trailing spaces, so that a short Item Number such as “100XLG” will actually be filled with spaces to fill the field’s full length of 30-characters. If you use the Item Number field in any other GP table, the label printing software may print all 30 characters. The LabelLink sql view strips trailing spaces off all of the string fields.
Follow the label printing software instructions for creating an external ODBC datasource to SQL Server and creating label reports.
The main table for your label reports will be the LabelLink sql view in the Company Database.
The following table lists other Dynamics GP tables you might link to for transaction information not contained in LabelLink.
|Item Master (IV00101)||Contains information from the Item Maintenance window.||LabelLink.ITEMNMBR = IV00101.ITEMNMBR|
|Lot Attribute Master (IV00301)||Contains information from the Lot Attribute Entry and Lot Attribute Inquiry windows.||LabelLink.ITEMNMBR = IV00301.ITEMNMBR
|Purchase Receipt Line Quantities (POP10500)||Contains the quantities from the PO receipt line, for both un-posted and posted receipts.||LabelLink.POPRCTNM = POP10500.POPRCTNM
LabelLink.RCPTLNNM = POP10500.RCPTLNNM
|Purchasing Serial/Lot Work (POP10330)||Contains the serial or lot numbers received for each PO Receipt Line. This table has the Date Received, Manufactured Date, and Expiration Date.||LabelLink.POPRCTNM = POP10330.POPRCTNM
LabelLink.RCPTLNNM = POP10330.RCPTLNNM
|Purchasing Serial/Lot History (POP30330)||Contains the serial or lot numbers received for each PO Receipt Line on posted transactions. This table has the Date Received, Manufactured Date, and Expiration Date.||LabelLink.POPRCTNM = POP30330.POPRCTNM
LabelLink.RCPTLNNM = POP30330.RCPTLNNM
|Sales Transaction Header (SOP10100)||Contains the Customer Number, Address, and other information about the sales transaction||LabelLink.SOPTYPE = SOP10100.SOPTYPE
LabelLink.SOPNUMBE = SOP10100.SOPNUMBE
A key aspect of configuring your labels is to build an appropriate filter so the label report prints only the labels for one user (or one computer). LabelLink sends the data for the labels to the LabelLink table, and the label report will pull data from the LabelLink table to print the barcode labels. If multiple users are (or could be) printing labels at the same time, your label report MUST have a restriction by User ID.
Article is closed for comments.