Queries for beginners

Hi Everyone,

Today I got a request from a customers user for a report with contactpersons and their e-mail adresses. I said it would be very simple to generate this with the query generator. Unfortunately this user had never heard of queries, let alone how to make one, so I thougt I would make an easy to read tutorial to do so.

If you want to get information that is stored in your SAP system and represent it on your screen, the SAP Business One Query Generator is your friend. You can find the tool under Tools -> Queries in your menubar.

When the Query Generator is opened you will see this screen with the cursor blinking in the upper left field.

Then when you hit the Tab key you will get a list of all available tables from the database. Information is stored in tables and for each type of information a diffrent table excists. You see that most of the tables consists of 4 letters. When you doubleclick on the discription columnhead the tables are sorted alphabetacally by their logical name.

In our example we are looking for contactpersons of our customers with their e-mail addresses. So we are looking for Business Partner information (Customers) and Contactpersons (Names, E-mailaddresses). If you type Business Partner in the search field you can select the OCRD tabel by doubleclicking it. You will be returned to the previous screen.

Here you see all fields available in the table. You can doubleclick on any of the fields to select them in your report. In this case I have selected CardCode and CardName. You see that they appear in the upper right Select field.

But we are still missing the Contacperson info. To find that you will have to return to the upper left field, place your cursor in it and hit the Tab key again.

As we did before we are now searching for the Contactpersons table.

When found you can again doubleclick this tabel and it will be added to your Query Generator form. You see that automaticaly a link to the Business Partner Table is created by an Inner Join

You alse see that behind the table names (OCRD, OCRP) a so called alias has been made (T0, T1). This alias can be used throughout the query. Aliases can be useful when there are more than one table involved in a query and column names are big or not very readable and they will make it easier to create and read your query.

So now we have to select the information from the Contactpersons table we want to display in our qeury result.

Make sure you select the OCPR table on the left side of the form.  Then select the Name and E_Mail field while your cursor is again in the upper right Select field.

With this we are almost done. But if we would execute this query, we would get a result of all our business partners, while the report must show only contactpersons of our customers. Therefore we place our cursor in the Where field on the right side of the form. On the left we select the OCRD table and in the middle we look for the CardType field. Doubleclick it tho place it in the Where field. Then you will have to add  = ‘C’ manually. C stands for Customer, S for Supplier and L for Leads.

And there we are. Just hit the Execute button and you will have your first query result.

If you are not happy with the result, then just click on cancel and you will be returned to the Query Generator form so you can alter your query. Off cours you can also do this manually in the upper part of the Query Results screen, but this will require some extended knowlegde of queries. If you are satisfied with your result, then you can save the query so you can call the query from the Query Manager.

Off course you can do much more with queries like sorting and grouping but we will get to that in a next episode.

I hope this was usefull for you and triggered you to get more out of this function of SAP Business One.

Kind regards,
Andy Grootens

Asecom.