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
3 评论

Fernando Ramos Miracle
版主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 :
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
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.
请先登录再写评论。