Free Articles, Free Web Content, Reprint Articles
Monday, October 15, 2018
Free Articles, Free Web Content, Reprint ArticlesRegisterAll CategoriesTop AuthorsSubmit Article (Article Submission)ContactSubscribe Free Articles, Free Web Content, Reprint Articles

SAP Business One SQL Level: Tables and Fields

When you are designing reports in Crystal or other tool or when you are exporting documents from Business One you should be familiar with SQL tables and fields.  It is described in good details including relations in Software Development kit.

You can also find names of these objects directly in user interface if you switch on ‘Show System Information’ flag in View menu.  We would like to walk you through and make few exercises.  This paper is written on introduction level and is not intended to be complete reference.  If you are programmer then next step is probably read SDK documentation.  After paragraphs we will try to answer FAQ:

1. View System Information.  Login client application and in menu mark ‘view system information’.  At this point open one of the forms.  Let’s try Purchase Quotation.  Place cursor over Vendor field (where Business Partner code supposed to go) and read info in lower left section of the application window: tables is OPQT and field name is CardCode.  As exercise try to find information about AR Invoice.  If you were able to find Header is OINV and item lines INV1 then you are probably on the right trail

2. Details in SDK.  Open Help Center (it is in HTML format) and go to Contents -> Customization Tools.  Let’s now find detail info about objects from previous paragraph.  By whatever reason all AR and AP documents are classified as ‘Marketing Documents’ in English interface.  It might be a little bit confusing.  Expand ‘Marketing Documents’.  And here you see AR Invoice.  Expand it and find all related files: INV1 through INV16.  At this point we believe that for most of us the rest is clear and we are good to begin working on integration or reporting.  Let us give you several advises

3. Crystal Reports and SQL base.  In CR wizard everyone can create report in a matter of minutes, right?..  Here important is to understand the level of complexity.  If all you need to do is pull your customers then wizard will do the job in a matter of minutes.  However if you plan to join several tables and possible user defined ones as well then we would recommend different strategy.  Create SQL View or even better stored procedure and test it prior to opening Designer tool.  We have seen numerous problems when developer is in learning curve and he or she is relying on wizard only: incorrect summaries, duplicate rows, etc.  CR is for ‘presentation’ and SQL is for ‘data selection’

OK we are ready to answer popular questions:

Q.  In our case we need to know that structure in order to create several stored procedures to feed ongoing data from our ecommerce portal shopping carts to SAP B1.  Does it make sense?
A.  We would not recommend do it that way.  Theoretically it is doable but you will come through long learning curve in importing with such side effects as data compromising and business logic violation.  More elegant approach is to program integration in Microsoft Visual Studio with SDK libraries and code samples in C# or VB.  It is possible to deploy Data Transfer Workbench ODBC or CSV based import

Q.  We agree that for Crystal it is good idea to create View or Procedure.  Our concern is about version upgrade.  Does it wipe out all ‘alien’ objects from company database?
A.  It does not or better say we tried update from 2005A to 8.81.  Now it might sound like something outdated.  In any case it is good idea to make test upgrade and verify procedures and reports prior to production update

Q.  We would like to export shopping carts from our Linux/PHP based custom ecommerce portal and send them to B1 as AR Invoices.  Do you have any technical recommendations?
A.  Consider Web Service project in MS Visual Studio with Software Development Kit.  Web Service is computer platform neutral and could be called from Linux/PHP/MySQL

Please call us 1-866-304-3265, 1-269-605-4904,  We have local presence in Chicagoland, Southern California, Atlanta Georgia, Southwest Michigan, Houston and Dallas areas of Texas. We serve customers USA and Canada nationwide and internationally via web sessions and phone conferences (Skype is welcomed).  We are working on Brazilian ERP consulting market in Sao Paulo since 2004

Source: Free Articles from


Andrew Karasev is SAP BO consultant and Great Plains Certified Master, MVP, 1-866-304-3265, 1-269-605-4904. He is also the initiator of eFaru project and founder of Alba Spectrum information space

Home Repair
Home Business
Self Help

Page loaded in 0.248 seconds