Answered

Sorting Gallery Items By Part Of Value

(2) Share

Report

Posted on by BF-02121120-0

I have a datasource with items that look like this ["123ABC ABC"] and I am trying to sort them alphabetically on the values AFTER the initial alphanumerical code and the space, so on ABC. I have a formula that sort of works, but it uses Find and as far as I am aware Find is not delegable in Dataverse. I would like this to have no warning. Is this possible? Any help appreciated.

Here is the fromula;

Sort(
    MyDataSource,
    Mid(
        'Division Title',
        Find(
            " ",
            'Division Title',
            1
        ) + 1,
        1000
    ),
    SortOrder.Ascending
)
 

Categories:

Building Power Apps

Error handling

General topics

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Suggested answer

Haque 3,474 on at

Like (2)

Report
Copy link

Link copied!
Hi @BF-02121120-0,

The core issue is that Find() is not delegable in Dataverse, so your current formula triggers a delegation warning and may only sort correctly on the first 500 (or 2000) records.

Unfortunately, Power Apps does not support delegable string functions like Find() or Mid() for sorting in Dataverse.

Possible workaround:

Preprocess data outside Power Apps: Add a calculated or custom column in Dataverse that stores the substring after the first space (e.g., "ABC" from "123ABC ABC"). Then sort directly on that column, which is delegable.

Use a local collection for sorting: Load the data into a local collection (with ClearCollect) and then sort/filter locally using your formula.

Example of location collectin approach:

ClearCollect( LocalData, MyDataSource ); Sort( LocalData, Mid( 'Division Title', Find(" ", 'Division Title') + 1, Len('Division Title') ), SortOrder.Ascending )

Was this reply helpful? Yes No
Suggested answer

11manish 3,038 on at

Like (1)

Report
Copy link

Link copied!
If you know you'll never exceed the delegation limit (500/2000 records), you can use:

try below :

Sort( AddColumns( MyDataSource, SortText, Mid( 'Division Title', Find(" ", 'Division Title') + 1, 1000 ) ), SortText, SortOrder.Ascending )

Was this reply helpful? Yes No
Suggested answer

Valantis 6,526 on at

Like (2)

Report
Copy link

Link copied!

Hi @BF-02121120-0,

There's no way to eliminate the delegation warning while using Find/Mid in a Sort directly against Dataverse, those string functions are confirmed non-delegable.

The only approach that fully removes the warning:

Add a calculated column in Dataverse that stores the text after the first space. In Dataverse this is a Formula column using: Mid('Division Title', Find(" ", 'Division Title') + 1). Then sort directly on that column SortByColumns(MyDataSource, "new_sortcolumn", SortOrder.Ascending), which is fully delegable with no warning.

If adding a column isn't an option, load into a collection first:

ClearCollect(colDivisions, MyDataSource)
Then sort the collection:
Sort(colDivisions, Mid('Division Title', Find(" ", 'Division Title') + 1, 1000), SortOrder.Ascending)

This eliminates the delegation warning because ClearCollect loads all records into memory first. The sort then runs locally with no delegation concerns. The trade-off is that all records load on screen load, so for very large datasets it can be slow.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No
Suggested answer

WarrenBelz 155,723 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @BF-02121120-0,

Firstly, you are going to have Delegation issues here as Sort() will only work on a complete column directly with a data source. This would be a good start
Sort( AddColumns( MyDataSource, SortCol, Mid( 'Division Title', Find( " ", 'Division Title', 1 ) + 1, 1000 ), SortOrder.Ascending ), SortCol, SortOrder.Ascending )

but will only work on a record set up to your (500 - 2,000) DataRow Limit. I am however wondering why the 1,000 reference - I assume the field is not that long ? Also you will not get a Delegation warning here as AddColumns is a "local" function, but the output is limited to your Data Row Limit.

Please ✅ Does this answer your question 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 answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
Kalathiya 2,420 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hello @BF-02121120-0,

Yes, you can create the formula column where you extract the value from "Division Title" column and use that column for sorting so you will not receive delegation warning.

In this approach you don't need to use AddColumns or collection.

Just to confirm, is your data always formatted with a 6-character code, followed by a space, and then the description (e.g., 123ABC ABC)

#1. Create a Formula Column in Dataverse.
#2. Enter the following formula in the Formula field:

Trim( Right( 'Division Title', Len('Division Title') - Len(Left('Division Title', 6)) ) )

#3. Then after use this formula column in sorting in Powerapps.

Ex:

SortByColumns(TESTS,"cr35c_calculated",SortOrder.Ascending) //TESTS - Replace with your table name //cr35c_calculated - Replace with your formula column name (Logical name)

---------------------------------------------------------------------------

Glad it helped 🙂
If this fixed your issue, please click “Does this answer your question?” to mark it as verified so others can find the solution easily.
A Like 👍 is always appreciated, and I’m around if you need more help @Kalathiya

Was this reply helpful? Yes No
Verified answer

BF-02121120-0 50 on at

Like (0)

Report
Copy link

Link copied!

Hi everyone,

Thanks for your quick responses! Hitting the datarow limit is a possibility, so I ended up using a collection on Start of the app and used the formula below;

Sort(

LocalM3DivData, //LocalM3DivData is the collection

Mid(

'Division Title',

Find(" ", 'Division Title') +1,

Len('Division Title')

),

SortOrder.Ascending

)

Was this reply helpful? Yes No
WarrenBelz 155,723 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @BF-02121120-0,

Just to clarify for you, a Collection is also subject to your Data Row Limit, so really does not achieve anything other than the need to re-collect if anything relevant changes in your data - you are better simply using AddColumns, which at least does the query at that point in time. There is no Delegable way of doing what you need.

Was this reply helpful? Yes No
Valantis 6,526 on at

Like (1)

Report
Copy link

Link copied!

Hi @BF-02121120-0,

Just wanted to check in and see if everything is working now. If you still need any help, feel free to let me know.

Also, if the issue is resolved, it would be great if you could mark the answer as solved so others with the same question can find it easily.

Thanks and have a great day!

Was this reply helpful? Yes No