Single Gallery from multiple sharepoint list (all same fields)

Question

Currently my powerapp is having a Gallery which is based on a single sharepoint list but i would like to implement a gallery on my app which should show items from 3 different sharepoint lists (all have same columns/fields) and i am using a filter on the galley so that user can type on the box and get filtered response only instead of showing all data

Please Help

I am already having this formula in gallery1.items :

Search('PO list', TextSearchBox.Text,"Branch_x0020_Code") For example,consider i have 2 list : list 1 & list 2 ,now when an user searches in the searchbox (i.e. TextSearchbox) i want the gallery to filter the data wherein "Branch_x0020_Code" equals search data from both the List

EDIT 1 : This is the formula i am trying to implement : Formula : ClearCollect(Aitems,'PO list',PO 2)

and error is shown as in the following image:

My "PO List" was having a column called status and its type was Single line of text whereas in "PO 2" the type of status column was Choices .since i cannot change the datatype i have changed one of the list column name to "Status 0" . Now the only difference i have between these two lists are one list has some extra columns while other don,t have. But still when i am trying ti implement i am getting this "Incompatible type" error.

Answer 1

Galleries can't directly show results from multiple data sources at one time. However one way to achieve this is to first collect data from the various lists into a single collection, then bind the gallery to that collection.

For example you could do this in the OnStart property of the app, or the OnSelect property of an "update" button:

ClearCollect( CombinedListsCollection,
 List1, List2, List3 )

Then bind the gallery.items to the collection instead:

Search( CombinedListsCollection, TextInput.Text, [columns])

Another way to get at this is to collect dynamically a filtered set of records as your users search. For example, on your Text Input control for the search box, set the "OnChange" property to:

ClearCollect(CombinedFilteredLists,
Filter(List1, StartsWith(Title, TextInput1.Text)),
Filter(List2, StartsWith(Title, TextInput1.Text)))

Then set the gallery items to CombinedFilteredLists.

Use StartsWith() wrapped in Filter() to delegate over large SharePoint lists as in the example above.

Here's a gif of that in action: Combining two SharePoint lists into one Gallery in PowerApps

Answer 2

If you have the exact same columns/fields for the two lists, then Ryan's solution would work. However, if some of the fields are different (either their name or type), then you can use one of the many functions that can help you change the "table shapes" in PowerApps.

In your specific case, if you have the following two lists in SharePoint:

• List01
  • Title (Text)
  • Status (Text)
• List02
  • Title (Text)
  • Status (Choice)

Then you can create a collection with the contents of both lists by using this expression:

ClearCollect(
    ItemsCollection,
    ShowColumns(
        AddColumns(List01, "StatusText", Status),
        "Title", "StatusText"));
Collect(
    ItemsCollection,
    ShowColumns(
        AddColumns(List02, "StatusText", Status.Value),
        "Title", "StatusText"));

The expression above creates a new column (*) called 'StatusText' on both lists, normalizing the type of the Status column to text, and then select the Title and StatusText columns from the two lists, thus ensuring that the schema (types of the fields) are the same.

(*) The AddColumns function doesn't create any additional columns in the SharePoint list; instead, it returns a new "virtual" table that can be used within expressions / controls in PowerApps.