Get it done fast'n'easy
[UPDATE February 13, 2011]
PLEASE NOTE!
DI Commander has been embedded to DI Construction Kit, which supports also MS SQL 2008 and SAP B1 8.8.
You can download DI Construction Kit with documentation and full source code from these sites:
http://www.mediafire.com/di_construction_kit
Please Note! A new patch version (released on September 21, 2007) of DIC is now available on the DIC download page. The new version fixes the login problem with B1 2007 & MSSQL 2005 as well as introduces a new helper object.
If you have been involved with SAP Business One implementation projects, I'm sure you have at some point faced a situation where you needed to quickly modify a large amount of records in the customer's database. Here are some real-world examples from my past projects:
- Change the default warehouse for 2,000 items in the database
- Remove 4,000 items that were imported with an incorrect item number
- Remove a few thousand items (from a total item count of 50,000) that have not been used in any transaction
- Copy the account settings from one itemgroup to all the rest of the item groups (about 200 of them)
Being familiar with relational databases, I've pretty often found myself mumbling something like this:
"Dude, if they just would allow me to run a simple SQL update query in the database. This task would be completed in a few seconds."
Well, this is prohibited by SAP for rather obvious reasons. We need to get by with somewhat less powerful tools.
The most usual alternatives available to the consultant (ordered by hairyness factor, not by preference) to handle these tasks are:
- The Monkey Method: brute force
Just do it manually using the B1 client. Don't get me wrong: despite the name, this method should not be overlooked. If the number of records to modify is small enough, the Monkey Method may well give the best effort/result ratio. I use it all the time, with good results. Especially during the implementation project, typing in a bunch of customer or item records might be an excellent excercise for the users of the new system. - The Layman Method I: B1 Import
SAP Business One client includes a rough but easy-to-use tool for importing some of the basic stuff such as items, business partners etc. Performancewise, this is by far the fastest method as it seems to bypass the DI API and import the data directly into the database. I haven't used this tool so much, but I've found it rather useful in doing massive pricelist updates. The main problem with B1 import is that there are so few objects it can manipulate and even for those objects, a limited subset of fields are exposed. - The Layman Method II: Data Transfer Workbench
DTW is most often used in the initial master data migration, but it can also be used to modify data in a production database. One appealing feature of DTW is that the import files can be built with spreadsheet tools such as Open Office Calc or MS Excel, which are familiar to both end users and ERP consultants. While DTW is more flexible than B1 import, there are still lots of areas it leaves uncovered. Perhaps the most notable limitation is that while you can add and modify records with DTW, you cannot delete records. Sometimes doing a reimport also results in kinky stuff that you really want to avoid in a production environment (such as double invoicing addresses in a customer record). - The Geek Method: DI API
When it comes to handling masterdata or transactions in the B1 database, the Data Interface API can do 'almost' everything that can be done via the B1 client. It is implemented as a COM object and thus can be accessed with any language and development tool that supports COM. The most typical languages used are Visual Basic and C#, but you could even use VBA from inside MS Office.
Unlike the notorious UI API, DI API is actually a rather solid and stable piece of software. Interestingly enough, Data Transfer Workbench is actually built on top of DI API and just adds the possibility to map CSV files or SQL resultsets to the fields of DI API objects. This means that anything that can be done in DTW can also be done in DI API, but not necessarily vice versa.
With DI API, the biggest problem is not the API itself, but the fact that it's pretty much targeted only for people with a developer background - that is, the 'geeks'. To use DI API directly, you need to have a good grasp of elementary programming concepts and you need to be able to read API documentations. The tools required (such as Visual Studio or Delphi) for writing and debugging code are not always available (not installed and/or no license bought) in the customer's production environment.
For a single-shot data modification task, there's quite an overhead having to do the full cycle of writing, compiling, testing, deploying and finally running the code in the customer's environment. There is also quite a lot of 'plumbing code' involved in order for such basic stuff as initiating the B1 session. Of course, the basic plumbing stuff can be copied from another project, but it's still a bit messy.
For those things that DI API cannot do, one might try some even hairier kludges such as the SendKeys method in either the UI API or from .NET Framework. However, these are even less approachable to the wider audience than the DI API.
None of the methods discussed above are intrinsically superior over each other. Sometimes a Caterpillar is better than a shovel and a shovel is better than a spoon, but not always. The selection of the most optimal tools depends on several variables, such as:
- The number of records that need to be modified
- The kind of modification required (create/change/delete)
- The overall complexity of the task
- The skills of the people available for the job
- The tools available in the customer's environment
- Recurrence: will this task be executed just once or perhaps repeated later ?
For instance, if you only need to delete 20 items once, the fastest method is obviously just to do it manually. However, when the number of items rises or the task is more complex and needs to be repeated over time (for instance, monthly contract invoicing), quite soon we are in a situation where taking the time to write a small application to do the task might save you valuable time and effort. That is, of course, if you have a developer available. While there are a big number of B1 consultants out there that do have a developer background, I think it's safe to assume that most of them don't.
Introducing DI Commander
Wouldn't it be nice to have a tool that had the full power of DI API but was about as easy to use as the Data Transfer Workbench or SQL queries ? Something that could be learned and used by a non-developer who is somewhat familiar with such tools as spreadsheet formulas/macros, SQL queries and/or command line shells. This is where DI Commander (DIC) comes into picture. DIC makes it easier, faster and more flexible to execute data modification tasks such as those mentioned above, but simultaneously remain within the safe boundaries provided by DI API. While the main target audience is consultants who are not developers, I think there are lots of developers who also might find some use for DIC.
Get straight to the business
With DIC, you don't need to care about initiating B1 sessions, as DIC does it for you. DIC supports multiple simultaneous sessions, so that you can for example read stuff from one B1 instance and write to another. The most interesting part of DIC is however the command host. My aim was to hide the complexities of "traditional" languages such as C# and VB in order to provide a more business logic -oriented user experience. While you can't totally avoid writing code when using DIC, there are obvious quantitative and qualitative differences. In the quantitative sense, there is a lot less code to be written in order to get a job done. While the quantity of the code has been reduced, the code is also much more transparent, revealing what is happening on the business logic level. This should make it much more approachable for non-developers. DIC has a code editor window, but it also has a shell window that imitates a command prompt. For many potential users, the command prompt may be more familiar than a developer-oriented IDE.
I am not endorsing DIC as a replacement for real task-specific business applications. However, if you just want a job done quick'n'dirty, it will provide you with the tools of the trade.
DIC = IronPython with extensions
When I was reflecting on all the stuff I had previously done with C# and DI API, I realized that it was pretty much always variations from the same theme:
- Get a handle of one or several B1 business objects
- Run a query or read a file
- Based on the results of step 2 and using the handle retrieved in step 1, either create new records or modify/delete existing ones (or perhaps transfer the retrieved. data into another system).
- Repeat your selected mixture of steps 1-3 until you're done.
Based on these findings, I decided to equip DIC with some extras in order to get the much needed performance boost for the mentioned tasks. To be more precise, I added the following functions to DIC by modifying the embedded IronPython engine:
get()
The get function is used for retrieving existing objects from the database or handles for creating new objects. It has four different variants:
- get(objecttype) ==> this will retrieve a handle for the specified objecttype from the default session.
- get(objecttype, key) ==> this will retrieve the instance of the specified objecttype with the specified key, if it exists in the database.
- get(objecttype, session) ==> this will retrieve a handle for the specified objecttype from the specified session.
- get(objecttype, key, session) ==> this will retrieve the instance of the specified objecttype with the specified key from the specified session, if it exists in the database.
For instance, to get a handle to the order nr. 50 that exists in the database, all you need to type is:
myOrder=get(ORDER, 50)Now that you've got the handle, you could access the values like this:
myOrder.CardCode...or perhaps update some value in the order:
myOrder.Comments="Just testing"
update(myOrder)The objecttype parameter is a member of the BoObjectTypes enumeration. DIC generates a helper constants for each BoObjectType in DI API as follows:
- oInvoices ==> INVOICE
- oItems ==> ITEM
- oBusinessPartners ==> BUSINESSPARTNER
- oAccountSegmentationCategories ==> ACCOUNTSEGMENTATIONCATEGORY
- etc.
If you are familiar with DI API documentation or DTW, you perhaps noticed that the helper constants are without the 'o' prefix and in singular form for clarity. If you prefer to use the original DI API notation for BoObjectTypes, you can do that as well. Just remember to type the whole namespace (for instance, SAPbobsCOM.BoObjectTypes.oInvoices instead of INVOICE).
browse()
By far the most important of the added functions is browse. Because DI API has been implemented as a COM object, its enumeration support in .NET environment leaves a lot of room for improvement. The browse function is used to create a multipurpose .NET enumeration wrapper for a variety of B1 objects:
- Objects that have the Browser property that can be mapped to a resultset
- "Line" objects that have the property Count and method SetCurrentLine. For Instance, Document_Lines and BPAddresses.
- Resultsets
With the enumeration interface in place, these object collections can be treated as any list in Python. With remarkable ease, that is.
There are five variants of browse():
- browse(objectinstance) ==> The specified object instance must have the property Count and the method SetCurrentLine (for instance, Document_Lines and BPAddresses). It will return an enumeration of all the line instances of the given object instance.
- browse(objecttype, recordset) ==> Gets a handle of the specified object type and returns an enumeration of the object instances specified by the recordset (using the default session).
- browse(objecttype, recordset, session) ==> As above, but uses the specified session instead of the default session.
- browse(recordset) ==> Returns an enumeration of the first column in each line contained in the recordset.
- browse(recordset, columnname) ==> Returns an enumeration of the specified column in each line contained in the recordset.
For instance, to iterate the items in an order, you might type:
myOrder=get(ORDER, 50)
for myline in browse(myOrder):
print myline.ItemCode
print ","query()
The query function is used to retrieve a Recordset from DI API according to the specified query. It has two variants:
- query(querystring) ==> this will retrieve a Recordset object from the default session using the specified query.
- query(querystring, session) ==> this will retrieve a Recordset object from the specified session using the specified query.
The Resultset retrieved by the query function can then be enumerated using the browse function.
add()
The only reason for the existence of the add function is syntactical consistency with the other new functions. All is does is call the Add() method of the specified object instance):
- add(objectinstance)
update()
As with the add function, the only reason for the existence of the update function is syntactical consistency with the other new functions. All is does is call the Update() method of the specified object instance):
- update(objectinstance)
remove()
The remove method can be used to removing any object that has the Remove() method. Naturally, the object can only be deleted if the consistency rules of B1 allows it to be deleted.
There are three variants of remove:
- remove(objecttype, key) ==> Removes the object instance of the specified type if found by the key using the default session.
- remove(objecttype, key, session) ==> Removes the object instance of the specified type if found by the key using the specified session.
- remove(objectinstance) ==> Added only for syntactical consistency. Simply calls the Remove() method of the given object instance.
The DIC user interface
As you can see from the image above, the current user interface of DIC is rather spartan.
Top of the screen
Shell tab
The shell window imitates a typical command prompt. It includes such functions as command history that can be accessed using the arrow keys. Currently, the shell window does not support statements that span several lines. However, you can combine multiple commands on a single line by separating them with semicolons.
Trace tab
The shell window will only show a short error message when an internal exception is caught. The Trace window will contain more details (stack traces etc). Normally you should not need to use the trace tab at all.
Bottom of the screen
B1 Sessions tab
DIC can do the standard Python tricks even without logging in. However, in order to use the DI API, you need to initiate at least one B1 session by using the login screen (To be precise, you could do it straight from the code, but why bother?). DIC also supports the promiscuous mode, in which you can initiate multiple simultaneous sessions into different databases and do cross-database operations. For each session you initiate, you need to select a handle that can be used to refer to the session from the code. If you only have one session open, you don't need to use the handle (as the single session is automatically defined as the default session), but even then you need to give the handle when logging in. The system suggests "session1" as the handle, but you can change it if you wish.
Code tab
This is an alternative to the Shell window. It works like a typical text editor: in addition to typing text, you can for instance copy and paste stuff around. Executing the code works a bit like the Query Analyzer window in MS SQL 2000: if nothing is selected, the application tries to execute every bit of code on the screen. If something is selected, the application will only execute the selected commands. Even when using the code window, the executed commands will be added to the command history of the shell.
Compared with the shell, the code window has a couple of bonuses. First of all, it supports multiple-line statements (just remember the indentation). Second, it also provides colour coding for the standard Python commands as well as for the functions added in DIC. Sorry folks, there's currently no IntelliSense function in DIC. However, in the Shell window you can get a list of the available fields and methods of any object by using the dir() function (please see the image below). I guess you could call that poor man's IntelliSense.
Working with Python
I will only scratch the surface of Python here. If you wish to get more thorough overview of the language, check out the Python.org website for a description of Python.
I chose Python as the language of DIC because Python has a very clear, human-readable syntax and it also lets you interact with live objects as you build your code.
As there are loads of excellent documents on Python out there in the web, I don't want to write another Python tutorial here. Instead, I will just give a couple of pointers to some of the available tutorials:
- The 'official' Python tutorial provided by the Python Software Foundation
- A beginner's Python tutorial by Steven Thurlow
If you prefer real books, I warmly recommend Learning Python by Mark Lutz and David Ascher (publiched by O'Reilly).
Learning to program Python is not really required in order to benefit from DIC. Basically you just need to know the new functions added in DIC and some very basic commands in Python to get by. That's definitely not any harder than learning SQL.
I hope to be able to establish a public library of well-written DIC snippets in the future. This would make DIC even more approachable by the Layman.
Playing around with DIC
Before diving deeper into the sample scripts, there's a few issues and conventions you should be aware of.
First of all, indentation (spaces and tabs) matter a lot in Python. They are used for identifying blocks of code that span several lines but belong to a single statement. These include for loops, function and class definitions etc. When you see an indentation in the sample code, it is there for a purpose. Omitting it will cause the code to fail (Please see the following image for an example on incorrect and correct indentation)
If you are a seasoned Python developer, you should also notice that there are a couple of Python conventions that are not currently supported by DIC (although they are supported in IronPython):
- While line spanning is supported in def and class statements, DIC does not currently support spanning lines ending with backslash (/)
- The 'open pairs' rule is not supported. Thus:
MyList = ["A","B","C"] ...is valid but the following isn't (although it is valid according to Python conventions):
MyList = ["A",
"B",
"C"]I apologize for these limitations. I was forced to skip some cleaning up in order to get DIC out in a decent amount of time. After all, remember that I'm distributing DIC for free and doing it just for fun.
Adding stuff
Let's add a new order (with two lines) to the system:
myorder=get(ORDER)
myorder.CardCode='CUSTOMER1'
myorder.DocDate=System.DateTime.Now
myorder.DocDueDate=System.DateTime.Now
myorder.Lines.ItemCode='DEMOITEM1'
myorder.Lines.Quantity=10
myorder.Lines.UnitPrice=5.5
add(myorder.Lines)
myorder.Lines.ItemCode='DEMOITEM2'
myorder.Lines.Quantity=8
myorder.Lines.UnitPrice=3.7
add(myorder)Modifying existing stuff
To update a couple of fields for a single Business Partner with the CardCode 'CUSTOMER1', you might type something like this:
bp1=get(BUSINESSPARTNER, 'CUSTOMER1')
bp1.Cellular="+358 50 4324 1332"
bp1.Notes="Just testing."
update(bp1)To do a similar change for all the business partners who belong to the default customer group:
for customer in browse(BUSINESSPARTNER, query("select cardcode from ocrd where groupcode=100")):
customer.Notes="Testing batch."
customer.Freetext="Testing batch."
update(customer)To change the password for all users in the database (for instance after making a copy of the production database for testing purposes):
for user in browse(USER, query("select user_code from ousr")):
user.UserPassword="test"
if update(user)==0:
print user.UserName + ":OK "
else:
print user.UserName + ":Update failed "To lock the warehouse 01 on all items in the database:
for itm in browse(ITEM, query("select itemcode from oitm")):
for whs in browse(itm.WhsInfo):
if whs.WarehouseCode=="01":
print " Locking warehouse 01 for item "+itm.ItemCode+":"
whs.Locked=SAPbobsCOM.BoYesNoEnum.tYES
update(itm)Deleting stuff
The following examples focus on deleting items from the database. This is something you cannot do with the DTW.
To delete a single item with ItemCode 'DEMOITEM1' from the database, just type:
remove(ITEM,'DEMOITEM1')...you may also build the list of items by defining a list such as this:
for itemcode in['A123','B124','C125']:
remove(ITEM,itemcode)
The above samples are nice for testing, but in a real world scenario you might perhaps want to read the itemcodes from a textfile with one itemcode per line:
for itemcode in open("c:\\itemstodelete.txt"):
remove(ITEM, itemcode)...OR you might wish to use a resultset from a query as a source for the item codes:
rs=query("select itemcode from oitm where qrygroup64='Y'")
for itemcode in browse(rs):
remove(ITEM, itemcode)Function calls can be nested for a more compact expression:
for itemcode in browse(query("select itemcode from oitm where qrygroup64='Y'")): remove(ITEM, itemcode)(As the above can be expressed on a single line, you can use it in the shell window)
Naturally, any of the above samples could just as well have been done for any DI API object types (BusinessPartners, Quotation documents etc). Simply replace the value of the objecttype parameter with the one you need. of the box (as long as the objecttype in question has the Remove() method in DI API).
For instance:
for cardcode in browse(query("select cardcode from ocrd where cardtype='L'")):
remove(BUSINESSPARTNER, cardcode)...this will remove all the BP's with type 'Lead' from your database (Please note! The same limitations apply as for the Remove() method in DI API. Thus, if there are transactions linked to a BP, it cannot be deleted).
Installing DIC
Click here to get the latest version (R1.0 PL 3) of DIC. Currently only the binary version is available. I have not yet decided whether to publish the source code.
Installation is about as quick'n'dirty as you might expect: just unzip it and you're ready to rock'n'roll.
System Prerequisites
In order to get DIC up and running, you need to have a correct version of DI API installed. DIC was compiled against DI API 2005 SP1 PL23, but hopefully it will work fine against any version of DI API 2005.
In addition to DI API, you also need to have version 2.0 of the .NET Framework installed.
UPDATE: Are there safety issues with DIC ?
I've recently received comments and warnings from several people who've assumed that DIC is poking the database directly using SQL queries. I thought it would become clear from all that's written above, but obviously it wasn't: ALL THE DATA MANIPULATION OPERATIONS DONE WITH DIC ARE EXECUTED VIA THE DI API CALLING THE Update(), Add() and Remove() METHODS PROVIDED BY THE DI API OBJECTS. Thus, DIC is safe as milk. Or at least as safe as the DI API.
UPDATE 2: The name is lame, but the tool works all the same (Jan 18, 2008)
Recently, I was contacted by the moderators of this portal. It turned out that some people had somehow associated the acronym formed from the previous name (B1 Turbo Command Host) with some kind of an insult. Well, I apologize for that.
Still, if we start going down that road, it quickly becomes a slippery slope. There are simply too many words with several meanings and too many acronyms that may give wrong impressions when pronounced as a word. Try replacing "1" with "i" and pronouncing some of the most common acronyms used in the B1 Forum, then look up those words in Wikipedia. You might be surprised by what you find.
Anyway, I decided that it's better to change the name than to be totally censored. Thus, B1 Turbo Command Host is now DI Commander. I sincerely hope that this new name does not aggravate anyone. If it does, then the Beauty is certainly in the Eye of the Beholder.
Disclaimer
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
- SAP Managed Tags:
75 Comments
