Answered

Modify automatic created formula

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I am moving my first steps in formula editing. The automatically created formula from a SharePoint list for searching and sorting is:

SortByColumns(Filter(TestKanban, StartsWith(ComplianceAssetId, TextSearchBox1.Text)), "ComplianceAssetId", If(SortDescending1, Descending, Ascending))

However in my list there is no 'ComplianceAssetId' and indeed the search box does not work. How do edit the formula to search into the column 'ACF'?

Thanks in advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (22)

Answers (1)

Sort by

Verified answer

leyburn19 2,157 on at

Like
a
(0)

Report
Copy link

Link copied!

Just insert the name of the column where the ComplianceAssetId apprears - Eg:

SortByColumns(Filter(TestKanban, StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

Where Title is likley to be Column one in your list. You can insert most text columns. Any special column types like choice are not likely to work

Note every tile you change the words, the default fields in the gallery are likley to change back to teh originals and you need to reslect every time

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I thought that too.
But if I do

SortByColumns(Filter(TestKanban, StartsWith(ACF, TextSearchBox1.Text)), "ACF", If(SortDescending1, Descending, Ascending))

(where ACF is the column I want) I get a StartsWith invalid argument error.

Was this reply helpful? Yes No
leyburn19 2,157 on at

Like
a
(0)

Report
Copy link

Link copied!

What type of column is it? Text, Numeric etc. Is it a look up etc

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Apparently the field ACF is not in my gallery, I assume this is why StartsWith returns the error. Does it matter column being formatted as numbers?

Was this reply helpful? Yes No
leyburn19 2,157 on at

Like
a
(0)

Report
Copy link

Link copied!

Starts with is a text search. If you want the galley to search based on numbers you would need to something else. If it was a field value problem then you would have a red line under te formula. can you do a couple of screen dumps of teh formula and one of the gallery,

The ACF field is simply a matter of putting it into teh gallery

If you are wanting to search using the textsearchbox based on a number tiy would have to do something like

ortByColumns(Filter(TestKanban, StartsWith(ACF, Value(TextSearchBox1.Text))), "Title", If(SortDescending1, Descending, Ascending))

This assumes teh ACF is a numeric value field

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your help.

One issue I have is that 'ThisItem.' does not list the field I want (ACF). I will have to troubleshoot this first and then apply the formula suggested in your first post.

Was this reply helpful? Yes No
leyburn19 2,157 on at

Like
a
(0)

Report
Copy link

Link copied!

It will not list the field if it is a dropdown type list. To reference this thyme of fiel in the search you need to add Value. Eg ACF.Value.

I am am not sure this works with thisitem in the gallery. You will find it the dat list, but add a label to a data card and in its text put thisitem.acf.value

this is one of the reasons I do not use anything in SharePoint other that text, number and date type fields. I use powerapps controls for this. To populate the controls I create small SharePoint lists. I only need to add or change to that list if the control needs to have more or less etc

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Not sure why, but the 'ACF' field I wanted goes under the name 'OData__x0063_wb9'. If I add 'ThisItem.OData__x0063_wb9' to a field in the Gallery I get the value of the column ACF I wanted. However, the formula

SortByColumns(Filter(TestKanban, StartsWith(OData__x0063_wb9, Value(TextSearchBox1.Text))), "OData__x0063_wb9", If(SortDescending1, Descending, Ascending))

Will not work and return an 'invalid argument type (number). Expecting a Text value instead' error.

Was this reply helpful? Yes No
leyburn19 2,157 on at

Like
a
(0)

Report
Copy link

Link copied!

Can you do a screen dump of the column list in the settings of SharePoint? This may help to workout what the issue is

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I am after sorting in accordance to ACF.

Was this reply helpful? Yes No