Skip to Content

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

http://tinyurl.com/4pcsccr

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:

  1. Get a handle of one or several B1 business objects 
  2. Run a query or read a file
  3. 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).
  4. 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  

image

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.

Using the dir() function

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:

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)

IndentationIndentation

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.

To report this post you need to login first.

71 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member
    Hi. I’ve tried the application for updating several fields on my BP. It only updates the BPs that is defined as Supplier. Any suggestions on how to update BPs that are defined to be Customers?

    Regards, Runar W.

    (0) 
    1. Former Member Post author
      Hi Runar,

      Could you please send me the script you executed? I’m sure we can work it out if I see the script.

      I am currently working on the second release of DI TCH and it will contain a wizard (or perhaps “witch” is a more appropriate name) that makes generating scripts easier.

      Henry

      (0) 
      1. Former Member
        Hi Henry.

        Sorry for the late respons to your answer regarding my trouble with updating BPs. The code I used is a stripped down version of one of the examples in the blog.

        for customer in browse(BUSINESSPARTNER, query(“select cardcode from ocrd”)):    customer.Notes=”Testing batch.”
        update(customer)

        This affected only my suppliers. But, never mind, because what I really was going to do (eventually) was adding and updating the PaymentMethod for all BPs in my system.

        Is it possible for you to provide some syntax on how to do this?

        (0) 
        1. Former Member
          When I executed:
          for customer in browse(BUSINESSPARTNER, query(“select TOP 10 cardcode from ocrd”)): customer.Notes=”Testing batch.”
          update(customer)

          and
          then i executed
          bp1=get(BUSINESSPARTNER,’C000004′)
          bp1.Cellular=’555-555′
          update(bp1)

          the result is FALSE and i can’t executed please help!
          My code i executed from tab CODE.

          (0) 
      2. Former Member
        Greetings,
        I’m trying to delete some GL codes that were wrongly created by a user (there are no transactions) and these are segmented GL codes.

        How should I go about doing it?

        Also, is there an example code showing how to browse data for example browsing info on an Item code?

        (0) 
    2. Former Member Post author
      Hi again,
      Without seeing your script, I could imagine that the problem lies within the query that it’s using as the key source. Most likely your query contains something that blocks the suppliers out.

      For instance, the sample query mentioned in the blog will only process business partners with BP group code 100. It is worth noticing that supplier and customer group codes are stored in the same table (OCRG). Normally, code 100 refers to a customer group.

      Henry

      (0) 
  2. Daryl Goodman
    Hi,

    When I try updating with a standard B1 field like Cellular everything works fine, but when trying to work with a User Defined Field I always get error message “A general error occurred. See the trace tab for details.” Will the program allow you to update a UDF?

    To recreate, I did the following:
    >>>bp1=get(BUSINESSPARTNER, ‘C30000’)
    >>>bp1.Cellular=”+358 50 4324 1332″
    >>>bp1.Notes=”Just testing.”
    >>>update(bp1)

    This worked fine and updated both the Mobile Phone and Remarks field.

    I then Added a UDF called udf to the Master Data Business Partner. System information shows this as OCRD.U_udf. When I edited the sample to:

    bp1=get(BUSINESSPARTNER, ‘C30000’)
    bp1.U_udf=”55″
    update(bp1)

    It fails.

    (0) 
    1. Former Member Post author
      Hi Daryl,

      The DI API handles UDFs in a slightly different way than the ‘normal’ fields.

      Try this:

      bp1=get(BUSINESSPARTNER, ‘C30000’)
      bp1.UserFields.Fields.Item(‘U_udf’).Value=”55″
      update(bp1)

      This is definitely something that could be done in a more user-friendly way in DI TCH. I’ll put it on my tasklist.

      Henry

      (0) 
      1. Daryl Goodman
        Thanks for the quick reply!

        That worked. I had read about the formating in the SAP SDK help, but couldn’t quite figure out that syntax. I’d definately look forward to an update, but I’ll keep going with the corrected syntax. Thanks for the help.

        One other piece of feedback, I know that when working with DTW, being case sensative is required, but I would love to see the code tab not be case sensative in DI TCH.

        Thanks for putting this out there, it is a cool tool.

        (0) 
  3. Former Member
    Hi Henry,

    I want to try di tch, but I can’t login (SBO-Common Error -111) on SBO 2007.

    Am I doing something wrong or does the tool not work on 2007) version?

    Best regards,
    Wim Kleinsman

    (0) 
    1. Former Member Post author
      Hi Wim,

      I guess you are using MSSQL 2005 ?
      This is something that I’ve run into after releasing DI TCH 1.0: with B1 2007 and MSSQL 2005, the db server type field can no longer be omitted in the login procedure, otherwise the login will fail.

      I hope to be soon able to release a new version of DI TCH that would among other things also accommodate the selection of db server type (MSSQL, MSSQL2005, Sybase, DB2).

      Henry

      (0) 
  4. Lukasz Chomin
    Dear Henry,
    Thanks for this tool. It looks very useful. But unfortunately it can’t be used with SAP B1 2007A and MSSQL2005. Do you know when will be available version with support for these?
    Thank very much
    Lukasz Chomin.
    (0) 
      1. Lukasz Chomin
        Hi Henry,
        Unfortunately I couldn’t download the new patch as there was a message:
        The requested URL /public/DITCH1.0PL3.zip was not found on this server.
        Can you check if the URL is correct or the file is uploaded?
        Thank you very much once again,
        Lukasz Chomin.
        (0) 
  5. Former Member
    Hi Henry,

    I would like some help in updating the avgprice field for a certain item say x0004 in warehouse 01.

    for item in browse(ITEM, query(“select itemcode from oitw where whscode=01” and itemcode=x0004)):
    item.avgprice=”100.”
    update(item)

    I have used whs.avgprice as well but still it does not work.

    Any suggestions??

    Thanks in advance.

    Jacques

    (0) 
    1. Former Member Post author
      Hi Jacques,

      There a couple of typos in your script. I will not go into detail with them, but here’s a couple of syntactically correct versions. Anyway, please pay attention that the fieldnames are case sensitive when calling the DI API objects (on the other hand, it doesn’t matter so much with the queries).

      As you are only updating a single item, you don’t need call browse. Instead, you can do it like this:

      item=get(ITEM,’x0004′)
      item.AvgStdPrice=100
      update(item)

      The above sample will work if your item’s valuation method is standard and you *don’t* manage stock by warehouse (thus the avprice is set on the header level).

      As I could imagine from the query you were trying to run that you *do* manage stock by warehouse, it will be slightly more complicated

      item=get(ITEM,’x0004′)
      item.WhsInfo.StandardAveragePrice=100
      update(item)

      …this one will set the std price for the first warehouse linked to the item. If you have several, you need to either browse them or call SetCurrentLine one by one. Please note that in the DI API the field for std avg price has a different name on header level and on warehouse level.

      Regards,
      Henry

      (0) 
  6. Former Member
    Hi,

    Congratulation, this is a very good tool, and it is a very good idea to use Python for this.

    I just have a question:
    How do you have registered the SAPbobsCOM dll in Python?

    Do you use the ctypes library or IronPython ? I have tried with ctypes, but it does not work.

    Thank you.

    (0) 
    1. Former Member Post author
      Hi Damien,

      It’s as simple as this:

      import clr
      clr.AddReferenceToFile(“Interop.SAPbobsCOM.dll”)
      import SAPbobsCOM

      In this case, you need to have the Interop.SAPbobsCOM.dll in the same directory with the executable. Otherwise, you could make a strongnamed version of SAPbobsCOM and place it in the Global Assembly Cache.

      Henry

      (0) 
  7. Former Member
    Hi Henry,

    Great tool, already saved me hours from the Monkey Method.

    I have a number of ITEMS I wish to cancel.
    Can you tell me if the Cancel method has been implemented?

    I would have expected the following to work:
         itm=get(ITEM,’A00023′)
         cancel(itm)

    I can see Cancel is listed from dir(itm) and so I tried:
         itm.Cancel

    But still the item record is not cancelled.

    Any ideas or pearls of wisdom.

    Gary.

    (0) 
    1. Former Member Post author
      Hi Gary,

      I’m glad you like DI TCH.

      Regarding your problem, could you try this:

      itm=get(ITEM,’A00023′)
      itm.Cancel()

      This is good input for the next version. I will add direct support for the cancel method.

      Also, I will change all the implemented methods so  that they will return a boolean value (true/false) instead of a number. This will make the scripts a bit more elegant.

      (0) 
  8. Hi Henry,

    I think this tool is just great. Is there a way to use the BeginTransaction – EndTransaction methods?

    If not, I know that you don’t have much time for this, but could you include this in the next version? It’d be very helpful.

    Congrats!
    Ian

    (0) 
    1. Former Member Post author
      Hi Ian,

      Thanks for the feedback. I’m glad there are people out there who’ve found this tool useful.

      Regarding your question about transactions: yes, surely you can do that with DI TCH. Actually all the functionality of DI API is available to you in DI TCH, although the more obscure functions have to be accessed with a bit more complex syntax.

      Here are two samples on using transactions: the first one is ended with a commit and the second one with a rollback. Thus only the first item ends up really being added to the database.

      session1.StartTransaction()
      itm=get(ITEM)
      itm.ItemCode=”ABCTEST”;
      add(itm)
      session1.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)

      session1.StartTransaction()
      itm=get(ITEM)
      itm.ItemCode=”ABCTEST2″;
      add(itm)
      session1.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)

      = = = =

      I think I might add these simplified commands in a future version:

      starttransaction
      rollback
      commit

      …these would be using the default session.
      For other sessions, the syntax would be:

      startransaction(sessionhandle)
      rollback(sessionhandle)
      commit(sessionhandle)

      (0) 
      1. Hi Henry,

        Thanks, actually things have a little more sense now ๐Ÿ˜› . I tried to push it a little more a tried to add a UserDataField (you know a metadata operation), and got the error -1120 (no error message). I thinks that’s the typical for the instanced recordset, so I guess you have an instanced recordset (maybe for the search function?). In .Net the oRecordSet = null; GC.Collect(); works  fine. Can please tell me how to do it in DI TCH?

        You know, scripting the add of UDF is better than monkey doing it, plus you can add fields to not exposed tables in the SBO client (currencies, transaction codes, banks, and a LARGE etc).

        Congrats again and thanks a lot,
        Ian

        (0) 
  9. Former Member
    Is it possible to set the XmlExportType property of the Company Object to xet_ExportImportMode using DI TCH
    Please suggest.

    Regards,

    Pankaj Mathur

    (0) 
    1. Former Member Post author
      Hi Pankaj,

      Yes it is. It goes like this (assuming you used the default session handle):

      session1.XmlExportType=SAPbobsCOM.BoXmlExportTypes.xet_ExportImportMode

      Henry

      (0) 
  10. Former Member
    Hi Henri,
    Thanks a lot for this great tool,i downloaded the new version (DITCH1.0PL3) and i’m facing a problem while after refreshing:
    I entered the Session handle, username, password, Server,DB userid and after i press the Refresh Button i got:
    “Fail to get company list(perhaps you should try DB direct):Connection to SBO-Common has failed”

    well i tried the DB direct and it isn’t working. Also i tried to change from MSSQL2005 to MSSQL and also it’s not working.

    I didn’t faced this kind of problem with the older version.
    what i should now?

    Thanks a lot
    here is my email(johnny.aboughannam@medialog.biz)

    (0) 
    1. Former Member Post author
      Hi Johnny,

      When you check the DB Direct checkbox, you should type the name of the SQL Server database instead of the name of the company in the database field (for instance, SBODemo_US instead of the name of the demo company).

      I hope this helps.

      Henry

      (0) 
  11. Former Member
    Hello Henry !

    Thank you for this usefull tool.

    I have a problem. I wish I could change the cardtype value of various BusinessPartner. I tried this code with one of them but it didn’t work.

    bp=get(BUSINESSPARTNER,’003282′)
    bp.CardType=’cLid’
    update(bp)

    DI TCH returns : ‘__ComObject’ object has no attribute ‘CardType’

    Can you help me ?
    Guillaume

    (0) 
    1. Former Member Post author
      Hi Guillaume,

      This should do the trick:

      bp=get(BUSINESSPARTNER,’003282′)
      bp.CardType=SAPbobsCOM.BoCardTypes.cLid
      update(bp)

      However, keep in mind that you can only change the CardType if there are no transactions linked to the business partner.

      Henry

      (0) 
    1. Former Member Post author
      Hi Cyril,

      Sure you can. The easiest way to do this is by using the standard Python syntax.
      Try this:

      filename = “c:\DITCHtest.txt”
      file = open(filename, ‘w’)
      file.write(“Hello from DI TCH !”)
      file.close()

      Just for information, DI TCH has full support for .NET 2.0. Thus, you can use any .NET 2.0 library such as System.IO from DI TCH. However, the above mentioned Python-oriented way is more in line with the basic idea of DI TCH (with emphasis on compact syntax that lets you do lots of things with just a few lines of code).

      (0) 
  12. Lukasz Chomin
    Hello Henry,
    Thanks again for your very useful tool.
    I have such problem. I update items group code in that way:
    for item in browse(ITEM, query(“select * from oitm where itmsGrpCod = 100”)):
         item.ItemsGroupCode=113
         update(item)
    It works great but from time to time I get return code -5002 for some items and these items group code is not changed. Changing the group code by SAP B1 Client Application is working without any problems.
    Where can be the problem? Is it related to your tool or DI API?
    (0) 
  13. Lukasz Chomin
    Hi Henry.
    I try to remove one of the assigned Warehouses from Item Master Data but I couldn’t achieve this.
    I tried in different ways but no luck e.g:
    for itm in browse(ITEM, query(“select itemcode from oitm where itemcode = ‘0125420517MB'”)):
      for whs in browse(itm.WhsInfo):
        if whs.WarehouseCode==”PL”:
             remove(SAPbobsCOM.BoObjectTypes.oWhsInfo,itm.WhsInfo)
    Is it possible? How to do this?
    Thank you very much in advance for any help.
    (0) 
    1. Former Member Post author
      Hi Lukasz,

      Unfortunately this is a major shortcoming of the DI API: there is no Remove() method for the ItemWarehouseInfo class. As DI TCH is using DI API to get things done in the database, it thus has the same problem.

      In fact there is no Remove() method for any of the “lines” objects that are accessed through a parent object (for instance, Document_Lines and EmployeeRolesInfo).

      The problem of not being able to remove ItemWarehouseInfo records is especially painful. I’ve seen lots of cases where the system has been set up to automatically link all items to all warehouses. Then, a user accidentally adds a new warehouse (the code of which is quite often ‘*’ :-). In a few seconds, a whole bunch of new ItemWarehouseInfo records has been created. It’s not a funny thing to remove them manually if you have a few thousand items or more.

      (0) 
      1. Former Member
        Hi Henry

        You Said we cannot remove warehouse from item information, but is it possible to update one warehouse insted of another.

        Example:

        I have item with code FR0008.
        And warehouses as  A,B,C.

        This item is Assigned to Warehouse A and B. Now I want to Update this item by assigning to A and C warehouse only and i want to Remove B. Is it possible to do like this.

        Or is it possible to change the code of warehouse from B to C.

        Thanks & Regards
        P Siva Reddy

        (0) 
        1. Former Member
          I tested this, and works !!!!!!!!!!!

          miOrden=get(ORDER, 19946)
          misLineas = miOrden.Lines
          misLineas.SetCurrentLine(2)
          misLineas.Delete()
          update(miOrden)

          With SBO 2007 PL46 and newest B1TCH version

          (0) 
  14. Former Member
    (Whoops, got it on the wrong thread!)
    Greetings,
    I’m trying to delete some GL codes that were wrongly created by a user (there are no transactions) and these are segmented GL codes.

    How should I go about doing it?

    Also, is there an example code showing how to browse data for example browsing info on an Item code?

    (0) 
  15. Former Member
    Hi, thaks you for your tool.
    I’m a fan of python language and now of SAP/B1.
    We are trying to create some automated processes and we are trying to do it with your tool. Writing the script in the tool, all it’s ok, but doing from outside it’s so dificult/easy than with di-api, can you show us a little example of these, perhaps only for us, functionality?
    For example: c:\> b1tch.exe importInvoice.py
    Inside importInvoice.py, manage connection, instantiate classes, etc…
    First of all, i’m not sure if it’s possible!? or even if you see thats it’s a good idea.
    (0) 
  16. Former Member
    Hello,

    I have problem updating invoices:

    rs=query(“Select T0.DocEntry AS DocEntry FROM OINV T0 INNER JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode WHERE  T0.CANCELED = ‘N’ AND AND T2.SlpCode = ‘1’ “))
    for DocEntry in browse(rs):
      myinvoice=get(INVOICE, DocEntry)
      myinvoice.UserFields.Fields.Item(‘U_I_Prov’).Value = “2”
      update(myinvoice)

    I don’t know why, but it doesn’t work. I tried it without the get-part, but it doesn’t work, too.

    Maybe you can give me a hint where my mistake lies.

    Thanks,

    Thomas

    (0) 
    1. Former Member Post author
      Hi Thomas,

      Could you give me more details regarding your problem, such as:
      – The error message that you receive ?
      – The version of DIC that you are using? (I warmly recommend version 2.0)
      – The type of your user-defined field (U_I_Prov)

      Best Regards,
      Henry

      (0) 
  17. Former Member
    Hi,
    This tool is really useful and I have already used it to remove numerous inactive items by writing a simple query.
    I have another issue at the moment and I was wondering whether this can be used.  Basically I have an excel spreadsheet containing an Item Code and UserText (this is the Item Remarks field) and I need to update the UserText field for over 2000 items. 
    I used to be able to import the UserText using DTW but now this is a 10 character field and I cannot update them.
    Can I use this tool?  If so what would the command be?  the file is located in c:\usertext.xls and the spreadsheet has 1 header row but this can be removed (or a 2nd header row added) if needed.
    Please advise, many thanks,
    Adrian  
    (0) 
    1. Former Member Post author
      Hi Vangelis,

      You need to retrieve the order by key, which in this case is the docentry of the order. The script for canceling an order with docentry 667 would be:

      myorder=get(ORDER)
      myorder.GetByKey(667)
      myorder.Cancel()

      Best Regards,
      Henry

      (0) 
  18. Hi
    I am very happy to found out this tool today.I am trying to test it if i can update the status of the order . Can you please help me on this .
    (0) 
    1. Former Member Post author
      Hi Bishal,

      Could you please give me more details in which way you wish to update the status? Do you want to close it or do something else ?

      Henry

      (0) 
  19. Former Member
    Hi there
    I’m trying to change the GL Determination method of an Item (set it to Item Level) and then set the various accounts for that item but when I run the code: MyItem.GLMethod = ‘glm_ItemLevel’ I get a general error. The trace window gives me this:
    at B1TCH.frmMain.ProcessCommands(String input, Boolean echo)System.MissingMemberException: ‘__ComObject’ object has no attribute ‘GLMethod’

    Any ideas on how to do this and also how to set the various GL Accounts?

    Thanks for the tool though, its mighty handy. Best suggestion I can make is to add more examples of how to use it: maybe some of the other users can just paste in examples of their working code/queries.

    Regards

    Edu

    (0) 
  20. Former Member
    I’m in a little bit of a pickel a warehouse was created by accident and I want to remove it from the list. No transaction or items have been made to this warehouse. The only thing I can see is it has added it’s self to the data base under the item master record for each item in the database. I need to delete it from the database so that no transaction go against it
    (0) 
    1. Former Member Post author
      Hi Ryan,

      I’m afraid I have to disappoint you with this one. Removing warehouse links from items is one of those rare things that you cannot do with DI API. Therefore you neither can do this with DI Commander, as it is dependent on DI API.

      The phenomenon you’re experiencing is a well known irritation in B1. If you have the setting “link all items to all databases” active when you accidentally create a new warehouse, B1 really creates a link from all items to that warehouse. I really think this should be fixed so that these links would only be created for *new* items that are added after the warehouse was created.

      I guess you don’t want to revert to a backup copy. Then the only “officially supported” way to get rid of the extra warehouse is to manually eliminate all those links from OITW table to the new warehouse and then finalln delete the warehouse itself.

      Best Regards,
      Henry

      (0) 
  21. Lukasz Chomin
    Dear Henry,
    Thanks again for this very useful tool.
    I have strange message when I try to update UDF for item. I run this code which I think should be correct:
    for item in browse(ITEM,query(“select ItemCode FROM OITM WHERE ItemCode=’A-001′”)):
         item.UserFields.Fields.Item(‘U_ThermDecomp’).Value=”Test”
         update(item)
    unfortunatelly I always get the error message:

    System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

    Do you maybe have any idea what’s wrong with my code?
    Thanks in advance.
    Lukasz Chomin

    (0) 
  22. When I run B1Tch  in MSSQL2008 I get de following error:

    Login failed: Failed to connect to SBOCommon(-111)

    Have any solution?

    Thanks

    (0) 
    1. Former Member Post author
      Hi George,

      In order to add support for SQL 2008, I will need to tweak the source code a bit and recompile the application against DI API 2007. I will try to do this ASAP.

      (0) 
      1. Former Member Post author
        Hi Wynand,

        I have integrated B1TCH (it’s now known as Python Console) to a new toolkit that I’ll be releasing shortly. It is called DI Construction Kit and it’s compatible with MSSQL 2008 as well as B1 8.8. (as well as the previous db/B1 versions).

        Send me your email address (for instance via LinkedIn) and I’ll deliver you a pre-release version of the new toolkit.

        (0) 
      2. Former Member Post author
        Hello,

        Now the first official release (R2.01) of DI Construction Kit is finally available free of charge with full source code at

        http://tinyurl.com/4pcsccr

        It is licensed under GNU Lesser GPL 3.0

        The Python tool that was previously known as DI Commander / B1 Turbo Command Host is now fully integrated to DI Construction Kit. Naturally, it supports both B1 8.8 (as well as 2007) and MS SQL 2008.

        I will start a new series of blogs about the ways in which the toolkit can be used. The first blog is already submitted to SDN but still pending for approval. Anyway, you can download the blog as a pdf document together with the toolkit from the mentioned link.

        (0) 
        1. Singh Davinder
          Henry, still facing issues downloading the app / tool from the Tiny URL provided. Maybe you could look at the full URL instead?

          Thank you and keep up the good work!

          (0) 
  23. Former Member
    Hi Henry

    It would be great if DIC could be updated to work with MSSQL 2008. Please let us know if this is in planning.
    I’m not a Python expert but if you’re not planning to update it would it be possible to get the source code so that I can take a shot at the update. This tool is way too handy to live without…

    Regards

    Edu

    (0) 
    1. Former Member Post author
      Hi Edu,

      I surely am planning to release a new version of DI Commander, with some nice new features and support for B1 2007/SQL 2008. I cannot give any promises about the schedule, though.

      Regarding the source code, did you notice from the more recent DI Commander blog that the full source code for DIC 2.0 is already available. You will find the link to the source code from here:

      https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/8938

      Best Regards,
      Henry

      (0) 
      1. Former Member
        Hi Henry

        A few hours after I asked for the source code I stimnbled onto your other weblog where the source code is stored.
        I downloaded it, added SQL 2008 to the options list, added a reference to the latest version of SAPbobsCom and recompiled.
        Not sure how many things I’ve broken buy doing this but so far all functions I’ve tried have worked. The best thing was that you can now delete Warehouses through DI. DIC probably saved me 2 days of pain.
        I can upload the compiled version (if you think its wise and if I can figure out how/where to upload it).
        You mentioned in your other blog that you’re working on something called myBolt. Is this a private project or is there somewhere where we can get more information on it (and I’m assuming it has something to do with integration).
        Thanks again for this amazing tool.

        Edu

        (0) 
  24. Former Member
    hi,

    I tried using the example of item delete to delete drafts.
    My code (simple when this works I will change to delete all CLOSED DRAFTS):

    for docentry in browse(query(“select docentry from odrf where docentry =282”)):  remove(DRAFT,docentry).

    Error:
    Invalid argument: expected BoObjectTypes, got int

    Kind Regards

    Eric Walker

    (0) 
  25. Former Member
    B1<->B1 integration scenarios, What exactly it does, If I am not wrong transfer data from one database to another. actually I am looking one thing, think I created Sales order in Database1 this sales order should create PO in Database2 if I don’t have a stock in Where House.

    Regards
    Shankar

    (0) 
    1. Lukasz Chomin

      As an equivalent you can script over B1 DI API with Microsoft Powershell.

      What Henry did is great but needs to be updated.

      With Powershell you can use it with any version of B1.

      (0) 
  26. Former Member

    Hi Henry ,

    Do we have a supported version for SAP 9.1 and SQL 2012 . This is one of the great tools i have used over the years to remove /clean up data . Please update if there is one available to use

    thank you

    (0) 

Leave a Reply