Union Tables like SQL. I wonder if there is ...

Koichi_Tsuji · 02-25-2018 04:00 PM

Union Tables like SQL.

I wonder if there is existing functions within Appsheet to “union tables” alike SQL available or not.

What I’m trying to do is to create multiple virtual table (slices) out of single table which is uploaded to Appsheet, then union those slices to make a single virtual table.

For instance, 1st virtual table contains ID + Image 1, and second one contains ID + Image 2 and so on.

On my case, there is single table, contains multiple images as part of column. Slice this table so that each virtual table contain just one images and then union virtual table after that.

Originally one table contains various number of pictures within a single row. By doing this, i can convert this table which contain single column (image) with unique ID, but it turns to be multiple rows.

This is simplified data schema.

(original) table

ID

/

Image 1

/ Image 2

/

Image 3

/

Image 4

/ Image 5 xxx

xxxxx

xxxxxxx

xxxxxxx xxx

xxxxx

xxxxxxx

(after union) ID

/

Image xxx

xxxxxx xxx

xxxxxx

What im doing now is to do this works within google spreadsheet, using QUREY() and Importrange() functions.

This create additional physical files which is not ideal and slow down APP response as well.

Again, wondering if we can achieve this within Appsheet?

Aleksi_Alkio

May I first ask why can’t you create the (after union) table directly? Is there any reason for that?

Koichi_Tsuji

@Aleksi_Alkio Hi, let me explain about the background story.

Firstly we create table where we have columns, ID / Short Comments / Image 1 / Image 2 / Image 3.

This is inspection app, where inspector notices some deficiency and put comment in text into this “Comment” columns.

Against one case, inspector will take related photoes and attached to those ID.

Some cases, inspectors would take 10 photo against single case.

Off course, we are able to create another data schema, like adding just one photo to single row, and add new row with new picture, however, due to the requirement to show on the workflow template, i need to maintain this data structure, single row assigned to single deficiency case along with multiple photo attached to the case.

On this table, we are not able to get the ideal “gallery view” where are able to see all the pictures across all the deficiency case to show all the pictures in a single gallery view.

So now, on the spreadsheet side, i use "query(select) " functions to create something like virtual table (actually not virtual though), to capture single photo assigned to single table out of master/original table where all the related pictures are stored within single row.

ID / Image

1

ID / Image 2

ID / Image 3

After creating those additional table, i also use googlesheet function to “union” then the final table schema looks like this.

ID / Image

Simple. Multiple rows, but we are able to capture all the photo in a single column. Then i refer to this table and create “Gallery view” where we are able to see all the related pictures “across” all the deficiency case.

Make sense?

Is there alternative to achieve this ?

Koichi_Tsuji

@Aleksi_Alkio Thank you!

I just sent email.

Aleksi_Alkio

May I ask this one first… how do you need to show images on your template? Are you showing them like row by row?

Koichi_Tsuji

@Aleksi_Alkio

Koichi_Tsuji

@Aleksi_Alkio This is snapshot from workflow template.

Out of original master table, we show the “comment” and put the related pictures beside.

Aleksi_Alkio

If I understood your request correctly, it’s doable without additional extra table as you have.

If you create an additional & related image table, you can show images as a gallery view on inline view on the inspection defaul view. You can also create the same report than your sample.

Koichi_Tsuji

How can we achieve that? Can you elaborate bit more?

Aleksi_Alkio

I will create a sample app quickly… difficult to explain everything.

Koichi_Tsuji

That’s super helpful!

Aleksi_Alkio

What is your email address? You can send it to aleksi@1track.com.