Setup LabelLink in GP
It will be helpful when writing the label report to have data in the LabelLink table. Follow the steps below to perform enough setup in LabelLink that you will have some data to work with in your labeling software.
LabelLink requires that you have a label defined, however the “File” field simply needs to be populated with something (as shown above the word “Testing” has been entered).
Then bring up a transaction in GP, such as a PO Receipt. On Receivings Transaction Entry, select Additional >> LabelLink. Then on the LabelLink window click PREVIEW.
Once you reach the Preview window, LabelLink has populated the LabelLink table with data. You will be able to test your label in the labeling software to see how it prints data. Leave GP and LabelLink open during the following steps.
Create an ODBC
NOTE: See the LabelLink Setup section for instructions on how to use our ODBC utility to automatically create a SQL User ID and ODBC to be used for the label reports.
Navigation: Start >> Control Panel >> System and Security >> Administrative Tools >> Data Sources. NOTE: if you are using a 64-bit machine, you need to launch the 32-bit version of ODBC Admin, which is located in C:\Windows\SysWOW64\ODBCAD32.exe.
Select System DSN, then click ADD
Select SQL Server Native Client, then click FINISH
Enter a Name, Description and select the Server, then click NEXT
Enter login credentials, then click NEXT.
Mark “Change the default database to” option, and select the Company Database. Unmark the other boxes. Click NEXT.
Unmark all checkboxes, click NEXT
Unmark all checkboxes, click FINISH
Click TEST. If it tests ok, click the OK button.
Using the LabelLink table
The LabelLink table is actually a SQL view. It joins a large number of GP tables so that you have access to all of the most commonly used information without having to do any table linking yourself. If the LabelLink view does not contain what you need, use the LabelLink view as the starting point for creating your own custom view.
The LabelLink table contains a field called BarCode. Depending on the Setup of LabelLink, this may contain:
- “Item Number”, or
- “Item Number”+”Lot Number” or
- “Item Number”+”Serial Number”
For the combined Item Number and Serial/Lot Number the two fields are appended with a plus (+) so that bar code readers can parse the information.
LabelLink allows the user to manually enter some user-defined fields, if desired. That data is in the five LBLUDF fields.
GP stores dates as date/time values. The LabelLink table provides pre-formatted date fields which strip out the time information. These formatted date fields end with “Str”, such as LSRCPTDTStr.
Depending on the source of the data in GP, different columns will be populated in LabelLink. There will always be a large number of empty columns. As shown above, these are all fields coming from the Fixed Assets (FA) module.
When printing labels for sales transactions, the columns coming from the Sales Transaction tables can be found with the prefix SOP.
User ID Restriction
If multiple Dynamics GP Users will print labels from different work stations, you will need one copy of the report, per user, saved in a shared location on the network. Each user-specific copy of the report will need a User ID restriction. As shown below, this would a copy of the report used by “SA”. Your labeling software may have a different method of creating a Filter or Restriction. Please consult the documentation for that product.
If multiple users print labels, but they all use the same workstation (i.e. depends who is working in Receiving, they all use the same PC, but only one user at a time), you do not need a User ID restriction. There is a table in the Overview section of this document that explains when different types of restrictions are needed.
Sales Line Data
The LabelLink view does not contain keys to the Sales Line table (SOP10200). However, the LabelLink LNSEQNBR column is a row number that corresponds to the default view displayed in the LabelLink window. By default, the window will show one line per Item Number (for non-tracked items), Serial Number, Lot Number and/or Bin. The SQL views below will only work when the LabelLink window is configured as follows:
- Non-tracked Items are set to display Per Item Number. If multi-bins is in use then Per Base UofM/Bin.
- Serial-tracked Items are set to Per Serial Number
- Lot-tracked Items are set to Per Lot Number
To create a label that pulls data from the Sales Line (or links to other tables that are themselves linked to the Sales Line), create the view shown below:
create view wvSOPLineLink as
select row_number() over(
partition by a.SOPNUMBE
order by a.SOPNUMBE, a.LNITMSEQ, a.CMPNTSEQ) as RowNum,
a.SOPNUMBE, a.SOPTYPE, a.LNITMSEQ, a.CMPNTSEQ, b.SLTSQNUM
from SOP10200 (nolock) a
left join SOP10201 (nolock) b on
a.SOPTYPE = b.SOPTYPE and
a.SOPNUMBE = b.SOPNUMBE and
a.LNITMSEQ = b.LNITMSEQ and
a.CMPNTSEQ = b.CMPNTSEQ
left join SOP10203 (nolock) c on
a.SOPTYPE = c.SOPTYPE and
a.SOPNUMBE = c.SOPNUMBE and
a.LNITMSEQ = c.LNITMSEQ and
a.CMPNTSEQ = c.CMPNTSEQ
The Row Number statement above is how the LNSEQNBR field is populated in the Label Link table, so linking between LabelLink.LNSEQNBR and wvSOPLineLink.RowNum will return the correct SOP10200.LNITMSEQ field in wvSOPLineLink.
Next, create a new view combining the LabelLink view and wvSOPLineLink:
create view wvLabelLinkSOPLine as
select a.*, b.LNITMSEQ from LabelLink a
join wvSOPLineLink b on
a.DOCNUMBR = b.SOPNUMBE and
a.LNSEQNBR = b.RowNum
Note that this view contains all fields from the LabelLink view and only LNITMSEQ from SOP10200. Add other fields as needed.
Grant the LabelLink SQL User ID access to the new view:
grant select on wvLabelLinkSOPLine to LABELLINK
Use the wvLabelLinkSOPLine view to create labels with SOP Line information. This view can ONLY be used for sales labels since other documents, such as a PO Receipt, will not link to the SOP Line table.
Article is closed for comments.