SQL JOINs and Relationship Maps

The relationship map is a powerful feature of SAP Business One linking different documents together, but can you use the relationship map links to your SQL queries. Let’s learn how. 

Start with a closed sales order. I can look at all the links it has by right-clicking and selecting Relationship Map. 

If you want to learn more about relationship maps, check out the Relationship map video in SAP B1 essential training.

This relationship map has a sales quote, a sales order, two delivery documents, one return, and one A/R invoice. The delivery of the order was split into one shipment of the 35 tablets and one shipment of the three servers. The tablets are invoiced, but the servers got returned to inventory. 

SAP B1 is very flexible with the links to documents. This relationship map shows that level of flexibility. Documents have multiple destinations such as the delivery document having A/R invoices and return documents. One sales document may split into two delivery documents.

 To keep that organized, the links between documents are in the rows. It is the rows that copy to other documents. For a SQL report, find the links in a related document in the rowtables, not the headers. For example, If I wanted to see the links in delivery document items, I find them in the DLN1 table. 

There are pointers to source and destination documents for each row item. Base columns point to the source document while Target columns point to destination documents. If we look at a delivery document, it has a base of a sales order and a target of an A/R invoice. 

In the table’s rows, bases and targets point to the DocEntry of the referred document. Every marketing document row table will have a BaseEntry and TargetEntry. This pairs with a type of document, found in the columns BaseType and TargetType.

 I can make a query like this to see the sales order for the delivery document rows

SELECT T0.[DocEntry], T0.[LineNum], T0.[ItemCode], T0.[Dscription], T0.[TargetType], T0.[TrgetEntry], T0.[BaseType], T0.[BaseEntry] FROM DLN1 T0

with a result of

On this table, there is no golden arrow on the T1.DocEntry column. To get a golden arrow, you must join tables together and use the joined table’s DocEntry. 

SELECT T0.[DocEntry], T0.[LineNum], T0.[ItemCode], T0.[Dscription], T0.[TargetType], T0.[TrgetEntry], T0.[BaseType], T0.[BaseEntry],
t1.docentry
 FROM DLN1 T0
INNER JOIN ORDR T1 ON T0.baseEntry = t1.docentry

I joined the DocEntry to the DocEntry of the header. This  JOIN creates a table with a golden arrow from the delivery document to the original sales order. 

What if I try this to the target? That’s tricky here. If you aren’t thinking this out and assume everything goes to an invoice, you might assume you want to join the A/R invoices table OINV. I might do something like this:

SELECT T0.[DocEntry], T0.[LineNum], T0.[ItemCode], T0.[Dscription], T0.[TargetType], T0.[TrgetEntry], T0.[BaseType], T0.[BaseEntry],
t1.docentry
 FROM DLN1 T0
INNER JOIN OINV T1 ON T0.TrgetEntry = t1.docentry

This query does give me links, but the highlighted ones are wrong.

The current query gives me an A/R invoice when I want a return document. I determine that by the number stored in the type. Here’s a table for some common types, though there are a lot more.

I can exclude the returns by adding more logic to my join, looking target type of 13 only.

SELECT T0.[DocEntry], T0.[LineNum], T0.[ItemCode], T0.[Dscription], T0.[TargetType], T0.[TrgetEntry], T0.[BaseType], T0.[BaseEntry],
t1.docentry
 FROM DLN1 T0
INNER JOIN OINV T1 ON T0.TrgetEntry = t1.docentry AND T0.targetType = 13

That query will also exclude anything without a target as well. Instead, I might use a LEFT OUTER JOIN like this:

SELECT T0.[DocEntry], T0.[LineNum], T0.[ItemCode], T0.[Dscription], T0.[TargetType], T0.[TrgetEntry], T0.[BaseType], T0.[BaseEntry],
t1.docentry
 FROM DLN1 T0
LEFT OUTER JOIN OINV T1 ON T0.TrgetEntry = t1.docentry AND T0.targetType = 13

If I want the returns in the report, I can use a Union to add another query that references returns.

SELECT T0.[DocEntry], T0.[LineNum], T0.[ItemCode], T0.[Dscription], T0.[TargetType], T0.[TrgetEntry], T0.[BaseType], T0.[BaseEntry],
t1.docentry, t2.docEntry
 FROM DLN1 T0
LEFT OUTER JOIN OINV T1 ON T0.TrgetEntry = t1.docentry AND T0.targetType = 13
LEFT OUTER JOIN ORDN T2 ON T0.TrgetEntry = t2.docentry AND T0.targetType = 16

This report gives me rows for both A/R invoices and returns. I also know which delivery documents do not have a target yet, as there is a default -1 in the TargetType Column. 

This query gives us arrows to other documents, but any column in the joined tables can be placed on the report. More joins can link more tables to the query if necessary. 

Joining tables along relationship map connections can be useful. It does take a bit of care to do, but it produces some amazing reports.