One To Many Problem

[rebelfox]

I have a need for a report to show invoice lines with values and show all the goods received numbers for each line.

I have a database table with Invoice lines with a financial value.

Each invoice line has a unique key of invoice number and position.

Each invoice line can have more than one Goods Received Number against it in a list

on another table with no unique key but they are linked by invoice number and position.

One invoice position could have 5 Goods Received numbers.

So if I load my invoice lines and my Goods Received table and link on on invoice number and invoice position I will get a problem.

The invoice line with a financial value will be displayed many times.

Imagine an invoice line for $100 with five Goods Received Numbers displayed on a sheet in a table object.

On my table object I will get 5 rows each for $100, each with a different Goods Received Number.

What I need is 1 line with $500 on the invoice line table and another table that lists the 5 Goods Received Numbers.

Can this be done?  I suspect I am missing a concept somewhere.

[sinanozdemir]

Do you happen to have a sample data?

Thanks

[maxgro]

maybe you can split the $100 amount of the invoice table

in 5 * $ 20 amount in the records of the Goods Received table

this could be made in the script

[maxgro]

this is another option at the chart level (in the attachment)

hope to inderstand your question

[rebelfox]

This is a hasty example.

I added a single table object on my sheet that is made up of all columns and I got duplicate lines as expected.

For example I00001-001 has five rows so that looks like a value of 500.

If however I create a table object just for the invoices positions columns and another table object just for the

goods received field, I actually get what I want.  I don't get why this is the case.  I would expect the underlying

data to be the same in both cases.   Do table objects de-duplicate displayed rows by default?

Is there a way to upload a QVD on a posting?  I would have liked to upload my example.

Invoice_Positions:
LOAD * INLINE [
Invoice, Invoice_Position, Invoice_Position_Value, CompositeKey
I00001,001, 100, I00001-001
I00001,002, 200, I00001-002 
I00001,003, 100, I00001-003
I00002,001, 10, I00002-001 
I00003,001, 50, I00003-001
I00004,001, 100, I00004-001 
I00005,001, 10, I00005-001
I00005,002, 80, I00005-002 
];


Goods_Received:
LOAD * INLINE [
CompositeKey,Goods_Received_Number
I00001-001, G01
I00001-001, G02,
I00001-001, G03
I00001-001, G04,
I00001-001, G05,
I00001-002, G06,
I00002-001, G07,
I00005-002, G08
];

[rebelfox]

.

[sinanozdemir]

If you can, try to upload the qvd. You should be able to upload the qvd.

[rebelfox]

OK found the upload option.   I need to use advanced editor.  See sample attached.

[sinanozdemir]

See the below screenshot:

If you go to the presentation tab and check "Omit Rows Where Field is NULL" for the CompositeKey field, you will get the same results as in Table Object 2 and 3.

Hope this helps.

[sinanozdemir]

No, scratch this. I don't think this does the trick.