How to update SQL when printing

Issues related to label design (working with databases, data processing, RFID encoding etc.) and printing (from NiceLabel Express, NiceLabel Pro, NiceForm and NicePrint)

Moderators: Georges, milos, NiceLabel Support Team

Post Reply
joecw
Newcomer
Posts: 2
Joined: Thu Nov 08, 2018 7:53 pm

How to update SQL when printing

Post by joecw » Thu Nov 08, 2018 11:31 pm

Hello all, I’m demoing NiceLabel 2017.

I’ve created a label and a simple Access database to hold the label info. The database has 3 field (UPC, amzTrans, printed).

The plan is to use the “printed” field to make sure each record is only used on 1 label. I’d like to have NiceLabel update the database and set the “printed” field to 1 for each record where the “amzTrans” field is used whenever a label is printed. Then I’ll use the dynamic Data Manager to filter out records that have the “printed” field set to 1.

I’m following this post viewtopic.php?f=7&t=22543&p=66063&hilit ... ase#p66063 and created a new action for the Print button that runs a SQL command to update the database.

I created an Execute SQL Statement action with this code:

UPDATE amzTransCodes
SET printed = 1
WHERE amzTrans = amzTrans

The new action is working but its updating all records instead of only the records used when printing the label. I’m believe the last part of the SQL query (WHERE amzTrans = amzTrans) is the problem. Does anyone know how I should write it so that only the “printed” field is updated on records where “amzTrans” is used when printing the label?

Thanks in advance for your help

User avatar
Kyleodonnell
NiceLabel Support
NiceLabel Support
Posts: 185
Joined: Mon Aug 28, 2017 3:07 pm

Re: How to update SQL when printing

Post by Kyleodonnell » Mon Nov 12, 2018 2:14 pm

Joecw

If the amzTrans is not unique, it will find all records matching it and update those accordingly. You will need a unique field such as ID or another field that will only return one record (depending on your use case)

joecw
Newcomer
Posts: 2
Joined: Thu Nov 08, 2018 7:53 pm

Re: How to update SQL when printing

Post by joecw » Mon Nov 12, 2018 3:49 pm

@Kyleodonnell thanks for your reply.

The amzTrans field is unique. I'm getting stuck on how to use the amzTrans value for the label getting printed for the WHERE part of the SQL query.

I created a NiceLabel variable (nlAmzTrans) to store the amzTrans value of the label being printed and use that variable in the SQL query.

UPDATE amzTransCodes
SET printed = 1
WHERE amzTrans = :nlAmzTrans

The query works and updated the record for the amzTrans value I hard coded for the nlAmzTrans variable. My question now is, how do I use the nlAmzTrans variable to capture value of amzTrans for the label being printed?

User avatar
Kyleodonnell
NiceLabel Support
NiceLabel Support
Posts: 185
Joined: Mon Aug 28, 2017 3:07 pm

Re: How to update SQL when printing

Post by Kyleodonnell » Mon Nov 12, 2018 4:04 pm

JoeCW,


We recommend opening a support case https://www.nicelabel.com/support/technical-support as the Support team will need screenshots to better see the issue and better give recommendations.

Post Reply