Technical Solution

How to create a query in SAP Business One and access it using URL through Zedsuite interface

Varokky.jose | | 2 min read

We had a requirement in one of our projects to pull the order status from SAP Business One to the orders of our Ecommerce site. Our site was integrated to SAP Business One using Zedsuite. Zedsuite allows custom queries created in SAP Business One to be accessed through an external interface. The query results can be fetched by calling the external URL provided by Zedsuite in our site and the customizations can be made as per our requirement in Drupal. Here in this case we are going to update the order status in E-Commerce site based on the status updates in SAP Business One. Read on to know more.

Steps to create custom queries in SAP Business One.

  • Login to SAP Business One
  • Goto Tools > Queries > Query Generator.
  • Specify the Fields, Table, Conditions etc in the Query Generator window.
  • Click on Execute Button
  • Click on Save
  • Specify the Name of query lets say "Updated-orders"
  • Specify the Category of the query as "B1WebAPI". Only query category names starting with B1WebAPI (followed by a space or underscore) are accessible through the B1WebAPI.

Following is the example of query added to SAP Business One for retrieving the orders updated within a specific date.

SELECT NumAtCard,DocStatus FROM ORDR WHERE UpdateDate >=[%0] and UpdateDate <=[%1]

The placeholders [%0] and [%1] are the first and second arguments passed to the URL.

The query can be accessed through an external interface by calling the following URL.
"http://{username}:{password}@{host}:{port}/query/Updated_Orders.aspx?0={date_from}&1={date_upto}" where {username} and {password} are the HTTP credentials for Zedsuite.

Hope this helps. Please fell free to share your thoughts and doubts regarding this here.

 

 

Related Topics