Combine Lists

Alfredo_Pou · 03-17-2019 09:28 AM

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?

Imaginarily:
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

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_Lopez1

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

Alfredo_Pou

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

You cannot.

Aleksi

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.

Alfredo_Pou

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

Rajesh_Sharma

@[Aleksi] (Combine Lists)
In Workflow or Report Section Using 2 slices for the same table how can we expand and merge lists (Merging 2 lists to 1
[A], [B], [C],[D]
by spliting 1 ROWS into 2 ROWS

START: <<[A]>> , <<[B]>> , <<[C] >> where ([D] ="")
START: <<[A]>> , <<[B]>> , <<[D] >> where ([C] ="")

in One List

please advice

Steve

Rajesh_Sharma

<<Start: Filter(SLICE1, true)>><<[A]>> <<[B]>> <<[C]>><< End >>
<<Start: Filter(SLICE2, true)>><<[A]>> <<[B]>> <<[D]>><< End >>

In Single Workflow/Report using single template

The Column numbers remaining the same , but Rows get doubles.

Rajesh_Sharma

<<Start: Filter(SLICE1, true)>><<[A]>> <<[B]>> <<[C]>><< END >> BLANK
<<Start: Filter(SLICE2, true)>><<[A]>> <<[B]>> BLANK <<[D]>><< END >>

Aleksi

Better to read data directly from the original table and then filter the data in the Workflow without slices.

Rajesh_Sharma

Thank you , your recommendations proves worth.

Aleksi

You’re welcome

Rajesh_Sharma

aggregate/ accumulate expression required in Workflow/ reports templates

With Total at the end of report

[DATE] [TEXT] [NUM 1] [NUM2] REQUIRED ([BALANCE] = [NUM1] -[NUM2]

if there is any facility / way to reach that.

thanks

Abdul_Basit_Mir

Hi guys i want to combine data of two tables like this how can i translate this in AppSHeet? Can you please translate this in Appsheet query?

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Steve

There is no AppSheet translation or your SQL query. AppSheet’s SELECT() can only return a single column.

Sandro_Ingrao

My workaround with this has been with virtual columns.

So in my example I have a master table and a child table and I want to show the information from both in one view. On the Child table I created virtual fields that reference the columns from the master table.

Then I create a slice for the child table that only includes child records and use that for the places that I don’t want to use the merged info. Then I create another slice that includes both the child records plus the new virtual columns to the master table. Then create your view based on the merged slice and voila you have a “merged” table.