Unanswered

Sort dropdown with leading numerical order (column type is text field that uses numbers and text) - Power Apps

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I have a dropdown that is full of values such as: "1a, 1b, 2a, 3, 4, 5 , ... , 11a, 11b"

From time to time, I add new values to this list that may appear out of order in my SharePoint list: "1a, 1b, 2a, 3, 4, 5 , ... , 10a, 7b" because it auto sorts these by date of entry. So if I just added "7b", it appears last.

Is there a way I can sort this dropdown by the leading numerical values? I need to keep this column as a text column because I can't record the letter and number separately. This list is not the same for all cases, I use a switch for my items depending on other calculations I'm running so I need this statement to be used for all sorting examples.

An example of one of my current statements is the following:

"SortByColumns(Filter('ILS Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var),"IC_ID",SortOrder.Ascending)"

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (0)

Sort by

TheRobRush 11,128 Moderator on at

Like (0)

Report
Copy link

Link copied!
made a test column in wrong order

Placed a dropdown with the items of

Sort(TestColumns, numberSort,SortOrder.Ascending)

and value set to numberSort (my column with the out of order numebr list)

and I get

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

Like (0)

Report
Copy link

Link copied!

What happens if you add more examples (11a, 11b, etc) and what column type did you use?
I changed mine to sort and I still 7P at the bottom
"Sort(Filter('ILS Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var),"IC_ID",SortOrder.Ascending)"

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like (0)

Report
Copy link

Link copied!

*edit double digit numbers throw it off, one secodn I will adjust

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like (0)

Report
Copy link

Link copied!
Sort( ForAll( //replace test column with sharepoint list name TestColumns, //replace BOTH numberSort with the column containing your values {displayValue: ThisRecord.numberSort, sortValue:Concat(Filter(Split(ThisRecord.numberSort,""),IsNumeric(Value)),Value)}), Value(sortValue), SortOrder.Ascending)

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

Like (0)

Report
Copy link

Link copied!

I am getting a strange error... never seen it before

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like (0)

Report
Copy link

Link copied!

can you share the entire items code you have in your dropdown currently? the error is covering part of it, and I see a 23, above that. Need all that to see whats wrong on your side, because this codes working properly on my side

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

Like (0)

Report
Copy link

Link copied!

Switch(
// varSearch ( New 1, Insp 2, or All 3) ---- varReq (Req 10 or Not 20) ---- varSt (Pass 100, Fail 200, NA 300, or NF 400)
varSearch + varReq + varSt,
// Match 11 New Required Only
11,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="New", Required="Yes"),"IC_ID",SortOrder.Ascending),
// Match Inspected Required Only
12,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Required="Yes"),"IC_ID",SortOrder.Ascending),
// Match ALL, Required
13,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var, Required="Yes"),"IC_ID",SortOrder.Ascending),
// Match New Not Required
21,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="New"),"IC_ID",SortOrder.Ascending),
// Match Inspected Not Required
22,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected"),"IC_ID",SortOrder.Ascending),
// Match Not Required

23,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var), "Item_x0020_Ball", SortOrder.Ascending),
/*
Sort(
ForAll(
Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var),
{displayValue: ThisRecord.'Item Ball', sortValue: Concat(Filter(Split(ThisRecord.'Item Ball',""),IsNumeric(Value)),Value)}), Value(sortValue), SortOrder.Ascending),
Sort(
ForAll(
//replace test column with sharepoint list name
TestColumns,
//replace BOTH numberSort with the column containing your values
{displayValue: ThisRecord.numberSort, sortValue:Concat(Filter(Split(ThisRecord.numberSort,""),IsNumeric(Value)),Value)}), Value(sortValue), SortOrder.Ascending)
*/
// Match Pass Inspected Required Only
112,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Pass", Required="Yes"),"IC_ID",SortOrder.Ascending),
// Match Pass Inspected Not Required
122,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Pass"),"IC_ID",SortOrder.Ascending),
// Match Fail Inspected Required Only
212,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Fail", Required="Yes"),"IC_ID",SortOrder.Ascending),
// Match Fail Inspected Not Required
222,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Fail"),"IC_ID",SortOrder.Ascending),
// Match NA Inspected Required Only
312,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="NA", Required="Yes"),"IC_ID",SortOrder.Ascending),
// Match NA Inspected Not Required
322,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="NA"),"IC_ID",SortOrder.Ascending),
// Match Not Found Inspected Required Only
412,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Not Found", Required="Yes"),"IC_ID",SortOrder.Ascending),
// Match Not Found Inspected Not Required
422,
//Result
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Not Found"),"IC_ID",SortOrder.Ascending),
SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var),"IC_ID",SortOrder.Ascending)
)

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like (0)

Report
Copy link

Link copied!
Haha, man that's a long switch. In your text there you have the code we just worked on commented out with /* */ but assuming that was a mistake and you have it set right in your actual dropdown. Try wrapping our filter in a dropcolumns, think because you have so many sources it is just auto generating the field in the dropdown which it is having issues with since our sort() made a 2 column set.

DropColumns( Sort( ForAll(TestColumns, {displayValue: ThisRecord.numberSort, sortValue:Concat(Filter(Split(ThisRecord.numberSort,""),IsNumeric(Value)),Value)}), Value(sortValue), SortOrder.Ascending), "sortValue")

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

Like (0)

Report
Copy link

Link copied!

So the code you wrote works on it's own, but it won't work within my switch statement unfortunately. Also, I forgot to mention for some of my dropdowns the items can be written as follows...
1
2
3(4)
3(5)
3(6)
4a
4b

This is getting a little complicated but do you have any advice? We have the switch there to help filter based on dropdowns (see top of switch statement I sent: // varSearch ( New 1, Insp 2, or All 3) ---- varReq (Req 10 or Not 20) ---- varSt (Pass 100, Fail 200, NA 300, or NF 400)
varSearch + varReq + varSt,)

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like (0)

Report
Copy link

Link copied!
Ok so I tried editing your code directly, see if this works or not.

It could have errors because that many switches without me having the sources on my side is a lot of opportunities for me to make a mistake since everything in my app gets a red line, but think this should work since all the results would be using a column all named same thing.

Switch( // varSearch ( New 1, Insp 2, or All 3) ---- varReq (Req 10 or Not 20) ---- varSt (Pass 100, Fail 200, NA 300, or NF 400) varSearch + varReq + varSt, // Match 11 New Required Only 11, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="New", Required="Yes"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match Inspected Required Only 12, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Required="Yes"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match ALL, Required 13, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var, Required="Yes"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match New Not Required 21, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="New"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match Inspected Not Required 22, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match Not Required 23, //Result RenameColumns(DropColumns(Sort( ForAll( Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var), {displayValue: ThisRecord.'Item Ball', sortValue: Concat(Filter(Split(ThisRecord.'Item Ball',""),IsNumeric(Value)),Value)}), Value(sortValue), SortOrder.Ascending),"sortValue"),"displayValue","Value"), // Match Pass Inspected Required Only 112, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Pass", Required="Yes"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match Pass Inspected Not Required 122, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Pass"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match Fail Inspected Required Only 212, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Fail", Required="Yes"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match Fail Inspected Not Required 222, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Fail"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match NA Inspected Required Only 312, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="NA", Required="Yes"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match NA Inspected Not Required 322, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="NA"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match Not Found Inspected Required Only 412, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Not Found", Required="Yes"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), // Match Not Found Inspected Not Required 422, //Result RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var,Title="Inspected", Status="Not Found"),"IC_ID",SortOrder.Ascending),"IC_ID","Value"), RenameColumns(SortByColumns(Filter('ILS Inspection Results',DOP_ID=DOP_ID_Var,ICS_ID=ICS_ID_Var),"IC_ID",SortOrder.Ascending),"IC_ID","Value") )

Was this reply helpful? Yes No