Avoiding Syn Keys - Please help

[#1]

Hi guys

I am busy with a sales report, but are having a problem with all the Syn keys. I am accessing SAP Business One Tables.

I have a table OINV (Invoice Header) which I left joined with INV1 (Invoice Lines) as INV

I have a table ORIN (Credit Memo Header) which I left joined with RIN1 (Credit Memo Lines) as RIN

My Sales and Qty's gets calculated by taking the fields of INV and subtrackting the fields of RIN

Both INV & RIN has the same fields & automatically gets linked together.

The problem is when I add another table. (see this example)

Both INV & RIN has a field called ItemCode. As soon as I add another table that contains the fields ItemCode & ItemName, I get

a Syn key on ItemCode. This is only one instance.

At the moment I have Syn keys for ItemCode, SlpCode, WhsCode etc.

[#2]

This is a SAP db, I see...

I had the same problem. I wanted to see INV and RIN as the SAME object, (but the minus sign on quantities and prices for RIN). So, what I did is: load first INV table giving it a name:

Document:

SELECT

....

FROM INV

And then, I loaded RIN table forcing the concatenation of the two tables:

CONCATENATE (Document) SELECT

.....

FROM INV

I am simplifying to show the method, hope it's clear. This will result in a single "Document" internal table without syntetics keys. Of course, you will have fields that you can use, if you need, to distinguish the invoice from the return, in the analysis. By the way, I used the same approach for DLN, RDN, RDR tables....

[#3]

Thanks alot guys for all your help.

I got it working by using a combination of your methods.

Thanks again

[#4]

Christo Roder wrote:
I got it working by using a combination of your methods. <div></div>

Can you please put the combination of those methods which was working finally.

[#5]

I did a inner join on the Invoice Lines & Invoice Header (which joins on DocEntry).

Stored the joined table into a qvd file & then dropped the table.

I did a inner join on the Credit Note Header & Credit Note Lines (also joins on DocEntry)

Stored the joined table into a qvd file & then dropped the table.

I then loaded the INV qvd file and then CONCATENATE it with the RIN qvd file.

Now I have one table (document). See attached how this looks now.

Thank you for all your help.