Unanswered

Filter function with StartsWith Function to search gallery list. Cannot reference the correct column names. Get errors.

(0) Share

Report

Posted on by Kirsten2

383

I am trying to create a search functionality with a gallery list. I was able to do it for one column, but not all five columns I want to be able to search. I am not sure why. I do not think I'm referencing the columns correctly.

For the gallery, the Items property has the following:

Filter(AddColumns(SharePointList1 as Main, "CustomerDetails", Lookup(SharePointList2, Email=Main.'Customer Email')),

StartsWith(ColumnName1, TextInput.Text) Or StartsWith(ColumnName2, TextInput.Text) Or StartsWith(ColumnName3, TextInput.Text) Or StartsWith(ColumnName4, TextInput.Text) Or StartsWith(ColumnName5, TextInput.Text))

The first part I am joining two tables from SharePoint to be viewed in the gallery. I got help from the wonderful @LaurensM with the joining, which works:

Solved: Re: Joining multiple tables for gallery view - Power Platform Community (microsoft.com)

So, for when I try typing in one of the columns, like Program, PowerApps starts giving me options below to select from which are a Data Card for Program and Data Value for Program from the first screen of my app, but my gallery that I am doing is like 13 screens later in a different module where I think I should be referencing the text in that gallery, not whatever is on screen 1 for that field. However, PowerApps only prepopulates below those two options to type in for Program. How should I be referencing the column name? Programs, for example, is in SharePointList2.

For the column referenced that does work, it is an ID from SharePointList1. I am just referencing it like how it appears as referenced in the Text property of the gallery. When I try to do similar referencing of the text of the gallery for the other columns, it is not working. It says, "The function 'StartsWith' has some invalid arguments. Name isn't valid. 'Program' isn't recognized." However, it is referenced as Program in the Text property of the Gallery (i.e. ThisItem.CustomerDetails.Program) I am not sure how I should be referencing the text or why it isn't working

Any tips/help appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report
Hi @Kirsten2 ,

Firstly, the code you have is structurally correct

Filter( AddColumns( SharePointList1 as Main, "CustomerDetails", Lookup( SharePointList2, Email = Main.'Customer Email' ) ), StartsWith( ColumnName1, TextInput.Text ) Or StartsWith( ColumnName2, TextInput.Text ) Or StartsWith( ColumnName3, TextInput.Text ) Or StartsWith( ColumnName4, TextInput.Text ) Or StartsWith( ColumnName5, TextInput.Text ) )

but to your question - any references in ColumnNameX above would need Main.ColumnNameX - resulting from you using the As function to reference the list.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Kirsten2 383 on at

Like (0)

Report

Thanks! The columns I want to use are from SharePointList2, so would Main still apply for them?

I tried using Main, but got an error.

StartsWith(Main.Program,TextInput.Text)

Where Program is the column name on SharePointList2.

Error message: "The function 'StartsWith' has some invalid arguments. Invalid use of '.'

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report

Hi @Kirsten2 ,

You are filtering SharePointList1, so those columns need to belong to that list - you have only added one column from List 2 'Customer Details', so I am not sure what you are trying to do here.

Was this reply helpful? Yes No
Kirsten2 383 on at

Like (0)

Report

Gotcha. I am trying to have search capabilities for a gallery that combines two tables. In the gallery, those multiple fields can be seen in the item list of the gallery, like Program, Branch, Customer Name, etc., and all of that information is in SharePointList2. Do I need to add those columns to the join after email when they successfully appear together in the gallery already with the joined tables? It seems to know those fields from the join already. Email is the field that joins the lists together. It's in both SharePointLists.

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report

Hi @Kirsten2 ,

Short answer - yes. You need to add them in AddColumns. Also please forget about SQL-type join principles - SharePoint is not a relational database and you need to add the columns in Power Apps.

Was this reply helpful? Yes No
Kirsten2 383 on at

Like (0)

Report

Do you mind giving me an example of how to add more columns into that filter join for SharePointList1 and SharePointList2?

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report
@Kirsten2 ,

Just add them below - I also notices your original does not contain the field name required (added below)

Filter( AddColumns( SharePointList1 as Main, "CustomerDetails", LookUp( SharePointList2, Email = Main.'Customer Email' ).CustomerFieldName, "NextOneHere", LookUp( SharePointList2, Email = Main.'Customer Email' ).NextFieldName ),

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Kirsten2 383 on at

Like (0)

Report

I did the following:
Filter(AddColumns(Orders As Main,"ProgramDetails",LookUp(Customers,Email=Main.'Customer Email').Program, "BranchDetails",LookUp(Customers,Email=Main.'Customer Email').Branch, "NameDetails",LookUp(Customers,Email=Main.'Customer Email').Name), StartsWith(Name,TextInput1_9.Text) Or StartsWith('Order ID',TextInput1_9.Text), Status.Value = Dropdown1_2.Selected.Value)

I got the following error:
The function 'AddColumns' has some invalid arguments.

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report
Hi @Kirsten2 ,

Firstly you do not need the As statement as this will only confuse the filter. Also, this structure in itself is valid providing Program, Branch and Name, Email and 'Customer EMail' are all Text fields.

Filter( AddColumns( Orders, "ProgramDetails", LookUp( Customers, Email = 'Customer Email' ).Program, "BranchDetails", LookUp( Customers, Email = 'Customer Email' ).Branch, "NameDetails", LookUp( Customers, Email = 'Customer Email' ).Name ), StartsWith( Name, TextInput1_9.Text ) Or StartsWith( 'Order ID', TextInput1_9.Text ), Status.Value = Dropdown1_2.Selected.Value )

Was this reply helpful? Yes No
Kirsten2 383 on at

Like (0)

Report

Thanks! I just changed the Name field. I referenced it wrong. Now I do not have any errors for that formula. The As Main still works. Appreciate your help!

Error:
Invalid use of '.'

Was this reply helpful? Yes No