Problem with two SharePoint-Lists in one Gallery

[Danny2810]

Hello everybody,

I need your help. I have two tables with a primary key.The primary key is the same in both tables.Then I have different fields in both tables.Now I have created a gallery in Powerapps based on Table 1. Now I want to add the records from Table 2 in the gallery of Table 1 per record.I tried it with "ClearCollect", but unfortunately without success.
The data should appear immediately when you open the gallery.The data in the gallery are then filtered using a "combo box".This works for the records in Table 1. But I find no way to merge the data from Table 1 and Table 2. When I insert a column using the LookUp function, I can not filter afterwards.

Thank you, for your help.

Greetings from Germany

Danny

[yashag2255 · Accepted solution]

Hi @Danny2810 

If you want to create such a gallery then you can build a collection at the App Start, so it will collect the data when the app is opened/ refreshed.

Expression:
App -> OnStart -> ClearCollect(MyCollection,Table1);Collect(MyCollection,Table2)

Note: If you are in edit mode, you need to manually run onstart to collect the data in collection.

Gallery -> Items -> MyCollection

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Danny2810]

Hello yashag2255,

thank you, but now i have the problem.

Now I see two entries in my gallery. An entry from Table 1 and an entry from Table 2.

But I need this below:

Name an other Data from the first table and some other datas (only for look and filter) from the second table.

Both table have the same primary key. 

Example in the gallery:

Columne 1: primary Key (User can´t see this colmn)

Columne 2: data from the first table (user can filter the column of the gallery)
Columne 3: data from the first table (user can filter the column of the gallery)

Columne 4: data from the first table (user can filter the column of the gallery)

Columne 5: data from the second table (user can filter the column of the gallery)

Columne 6: data from the second table (user can filter the column of the gallery)

Columne z: data from the second table (user can filter the column of the gallery)

Do you understand what i mean?

Thank you for your help

Danny

[yashag2255]

Hi @Danny2810 

To tackle such a case, instead of adding two sources directly to the collection, you can directly use the expression on the Items property of the gallery as:

AddColumns(Table1, "Col5", LookUp(RenameColumns(Table2,"Name","Name1"),Name1 = Name).ColumnToShow1, "Col6", LookUp(RenameColumns(Table2,"Name","Name1"),Name1 = Name).ColumnToShow2,"Col7", LookUp(RenameColumns(Table2,"Name","Name1"),Name1 = Name).ColumnToShow3 )

Here, You can access the columns from Table2 using Col5, COl6 and Col7. Also, ColumnToShow1, ColumnToShow2, ColumnToShow3 needs to be replaced with the name of columns you want to fetch from table2.

I am assuming that only one record per name exist in both the tables. In case you need further help with the formula, please share the schema of the data source, so that we might be able to help you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Danny2810]

Hello yashag2255,

thank you very much for your help.

Here is the schema of my data sources:

Table A
Column 1 = ID-Number
Column 2 = Name
Column 3 = Text1

Column 4 = Text2
Column 5 = Text3
Column 6 = Text4

Table B
Column 1 = ID-Number
Column 2 = Name
Column 3 = Text1
Column 4 = Text2
Column 5 = yes/no - Field 1
Column 6 = yes/no - Field 2
Column 7 = yes/no - Field 3

The columns 1 and 2 contain the same records in both tables.
The "ID number" is a unique ID and the same in both tables.
The "ID-Number" exists only once in both tables. The "ID-Number" exist in the gallery but

is not visible.
Now the gallery should be structured as follows:

I hope you can understand my problem

Grettings from Germany

Danny