I recently hit a common but annoying issue while building a canvas app for Dynamics 365 Project Operations. The requirement was straightforward: display project tasks in the same order they appear in the D365 Project Tasks grid.
When I connected the Project Task table directly to a data table control, the rows showed up in creation order instead of the logical sequence users expect. Tasks that should appear at the top were buried at the bottom.
My first instinct was to sort by the msdyn_displaysequence
column in ascending order. This got me closer, but revealed another issue: the D365 grid shows clean row numbers (1, 2, 3…) while msdyn_displaysequence
contains decimal values like 1.5, 2.3, 4.7.
This makes perfect sense from a data perspective. When someone inserts a task between positions 1 and 2, the system assigns it 1.5 instead of renumbering every subsequent row. Smart design, but not great for user experience.
Most solutions I found online suggest creating a collection with row numbers added.
This works, but it creates a local copy of your data. That means dealing with refresh logic, potential sync issues, and the overhead of maintaining a collection that mirrors your data source.
I wanted a cleaner approach that works directly with the data source.
After digging through community solutions, I found inspiration in Matthew’s blog post about generating row numbers in collections. I adapted his approach to work directly with data sources instead of creating separate collections.
Here’s the formula that solved my problem: (replace with your own dynamic)
ForAll(
Sequence(
CountRows(
SortByColumns(
Filter(
'Project Tasks',
Project.Project = [@ModelDrivenFormIntegration].Item.Project
),
"msdyn_displaysequence",
SortOrder.Ascending
)
)
),
With(
{
sortedTasks: SortByColumns(
Filter(
'Project Tasks',
Project.Project = [@ModelDrivenFormIntegration].Item.Project
),
"msdyn_displaysequence",
SortOrder.Ascending
)
},
Patch(
Last(FirstN(sortedTasks, Value)),
{ Index: Value }
)
)
)
And here's the end result.
Let me break this down into digestible pieces:
Step 1: Create the sequence
Sequence(CountRows(filtered_and_sorted_tasks))
This generates a sequence from 1 to the total number of tasks. If you have 10 tasks, you get [1, 2, 3, 4, 5, 6, 7, 8, 9, 10].
Step 2: Process each position
ForAll(sequence, ...)
For each number in our sequence, we execute the patching logic.
Step 3: Get the task at each position
Last(FirstN(sortedTasks, Value))
This grabs the Nth task from our sorted list. FirstN(sortedTasks, 3)
gets the first 3 tasks, then Last()
gives us exactly the 3rd task.
Step 4: Add the row number
Patch(task, { Index: Value })
This adds an Index
column to each task record with the appropriate row number.
This approach gave me exactly what I needed: tasks displayed in the correct sequence with clean, sequential row numbers that match what users see in D365. The row numbers automatically update when the underlying msdyn_displaysequence
changes, and I don’t have to manage a separate collection.
The performance is reasonable for typical project sizes (50-200 tasks). For larger datasets, you might want to implement pagination or consider the collection approach after all.
This technique works well when:
Avoid this approach if:
Sometimes the best solution isn’t the most obvious one. Instead of fighting with collections or settling for confusing decimal sequences, a targeted PowerFx formula can solve the problem directly at the data source level.
The key insight here is using Sequence()
with ForAll()
and Patch()
to systematically add computed columns to your data source. Once you understand this pattern, you can apply it to other scenarios where you need to augment data source records with calculated values.
Credit to Matthew Devaney for the original collection-based approach that inspired this solution.
Original Post http://linnzawwin.blogspot.com/2025/05/powerfx-to-add-row-numbers-to-data.html