Combine Lists

(Alfredo Pou) #1

Hello Community

I need to combine information from several tables to generate a report. The tables do not all have the same structure. But they do share references and KeyId’s. My idea is to select the columns that interest me from each table and generate a new virtual table with all those columns. Is it possible to do this?

I started to try something simple like this:

SELECT (Guarantees [Guarantee Id], [Entry Date] = TODAY ()) +
SELECT (Returns [Return Id], [Return Date] = TODAY ())

The result is strange. You only see the columns in the first list, but count the total of records.

Is there a way to take the columns that interest me from each table from the KeyId and generate a new combined structure?

FILTERCOLUMNS (SELECT (Returns [Return Id], [Return Date] = TODAY ()), [Return Id]. [Column 1], [Return Id]. [Column 5], [Return Id]. [Column 14]) +
FILTERCOLUMNS (SELECT (Guarantees Id), [Entry Date] = TODAY ()), [Guarantee Id]. [Column 6], [Guarantee Id]. [Column 58], [Guarantee Id]. [Column 4])

In my case, I need to send an email at the end of the day that lists all the activity of the day. Deliveries, Merchandise Income, Income in guarantee, Returns, etc. And be able to sort them all by their respective timestamps.

What would be the most appropriate way?
Thank you

(Steve Coile) #2

You cannot usefully combine lists of references to different tables.

You cannot you return multiple columns from a single table using functions, but a slice can approximate this.

A slice cannot merge data from multiple tables.

(Fernando López) #3

If you need the data just for report purpose, run the query in the report workflow.

(Alfredo Pou) #4

But how do I select data from different tables and combine data of the same type but with different column names and different table structures?

(Steve Coile) #5

You cannot.

(Aleksi Alkio) #6

If you create one table with two rows (template), you can show all the common data in that same table. The first row would have it’s own Start & End expression where as the 2nd row have the same but from different table. You can’t sort the order over tables, but you can have data first from the 1st table and then data from the 2nd table.

1 Like
(Alfredo Pou) #7

And if I change the names of the columns that I want to combine and I make slices of each table with the columns that interest me for the report. Could the combination of lists work?

(Aleksi Alkio) #8