Framework For Two Separate Database Connections And Queries
Hello,
I have developed labels that will take two pieces of information from the user via query and pull up the appropriate records to populate production labels. This works for 80% of our needs and is run through the print station or the web server very nicely. The two pieces of information are the work order number and the trace number, basically our internal serial number that allows us to trace the history of the part and what lot of steel went in to producing it. These are typically make to order and have a clean customer order linked to the work order.
The other 20% are take from stock orders. In these cases, the production information is not linked to the customer order information. We make these parts to stock the warehouse and will ship them out to a customer when it is needed.
The solution in these cases, I believe, would be to collect the customer order number and the line number - this will populate all of the customer order side fields - and then collect the work order number that the stocked part was created under. Initially, I had accomplished this via a user defined field in the order line number. My colleagues then informed me that there can be multiple work orders associated with one shipment, which blew up that route.
So I believe I need a second query. After the user has inputted the Customer Order # and the Line #, there needs to be a completely separate SQL query with the Work Order # as an input. Joining these two tables is not possible because the fields that they share SHOULD NOT match with these two data sets in the take from stock case.
I'm hoping that you all can help me to figure out a best practice and maybe give me some direction in where I can get started, as I have limited experience with scripting. In first thinking the problem through, I came up with two routes:
1) Have the Bartender query get the CO # and Line #. Then, in Data Entry, have a VB Script field that they input the Work Order # and through VB Script magic, it could run a query and populate the rest of the Bartender fields. I played around with this and concluded that it was either impossible or incredibly inefficient.
2) Write a custom script that I could use in a macro in our ERP software. I think this would be through .NET or a custom VB Script. But basically have my user highlight the Customer Order Line and click on a "Print 'Take From Stock' Label" macro. The ERP software would feed Bartender the info it needs to generate the Customer Order side and then prompt the user for the Work Order number and populate the rest of the information. This seems like the more elegant solution, but I don't know where to start. I've installed the .NET SDK and read the "BarTender’s .NET SDKs - Programmatically Controlling BarTender using C# and VB.NET" white paper, but haven't found any more information on where to go next. At the end of the white paper it says to "see the Getting Started topics in the accompanying SDK documentation," but I haven't been able to find that!
Let me know what you think! I really appreciate any guidance, examples, or learning sources that I can tap into.
Thanks.
Adam
-
Hello Adam,
What you require can be done using both methods:
1. In deed an option would be to use the already implemented database connection to query the database, and then use a VB script in a data entry control to query a second database and to load the resulting data into label objects or data sources.
Attached you'll find an example you could use as reference. This sample contain a couple VB Scripts that do two things :
a. Fills a drop down list from an Excel spreadsheet.b. Uses the choice selected in the drop down list to query the database again to retrieve records that match the selection, and loads that data into a label object.2.1. A second option would be to query your two databases directly from your ERP system, and then output the resulting data for BarTender to print it. This could be done for example using our middle ware application Commander. This way your ERP could export a simple text file with all the required data (already from the two databases) which Commander would automatically detect and produce the print job with all the required data. For more information please read the below white papers:*This option would require that your ERP prompts the user for all the required data (for both queries), as your application would be the one filtering the database instead of BarTender.2.2. If you wish to fully automate BarTender through our .NET SDKs you would still need to perform one custom database connection to one of the two databases as BarTender only supports one database connection per document. Which means that you could prompt one query designed directly from your ERP, and the other using our supported query prompt for the second database.Then, with all the correct data already at your application's disposal (at least the one produced by your ERP's own query, as the other query would have loaded the information automatically on the document) load the appropriate data to the label using the "SubString" class.You'll find detailed information in our help documentation.Best regards.0 -
Fernando,
Thanks so much for the detailed response. I will study the attached example and see what I can come up with.
I think eventually an ERP Macro and integration with Commander is the route that I want to go. It would be very nice to just have users click a button in the ERP suite, enter the data, then have the label generate automatically. Until I have time to develop that, I'll give the VB scripting in Bartender a whirl.
Adam
0 -
Another way of fixing this problem is creating a database "View" that combines the information from your two databases and likely multiple tables. You will have to create the view with the record for the CO# and Line # whether it is a "made to order" item or stock item. Since you say a Join doesn't work then maybe UNION the two queries - which should work as long as the columns from each query are the same.
I do something similar quite often since Bartender label formats cannot change their connection string on the fly. Not sure if you have the ability to create something like this in your database but I would say that this is a possible option for you.
If you need more guidance on this, let me know.
0
Please sign in to leave a comment.
Comments
3 comments