Creating a Sorted Collection

[Dpalmer]

Hello,

I'm using a SQL datasource and want to create a collection. The datasource has a "StartDayTime" field that is a datetime type.

I want the collection to be created with 1) the first record to be the earliest "StartDayTime" and 2) Each row of the collection to have an ascending row number for each record in the collection.

So the source data might look like this:

And I want the resulting collection to be ordered like this with the additional column of row number:

Any help would be greatly appreciated. 

Thanks,

David

[AbdulWahab]

Hi @Dpalmer ,

The link: https://www.matthewdevaney.com/powerapps-collections-cookbook/sort-a-collection-by-one-or-more-colum... might be the solution to your problem.

If you need additional help please tag me in your reply and please like my reply.

If my reply provided you with a solution, pleased mark it as a solution

Thanks
Regards,

Abdul Wahab
Power Platform & Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: https://www.linkedin.com/in/abdul-wahab-a5b8b011a/
Youtube Channel: https://www.youtube.com/channel/UCBjgLqLK_2kU-3bwo4McrTw

[VishalJhaveri]

Please try this :

ClearCollect(col1, SortByColumns(SqlSource, datecolumntosortby, "Ascending"));


ForAll(col1,
           Collect(col2,
          Patch(Last(FirstN(col1, CountRows(col2)+1)),
           { RowNumber: CountRows(col2)+1})
        )
)

Thank you.
If my answer helps you, please mark it as solution.

[sopatte · Accepted solution]

The documentation of "ForAll" states, "When writing your formula, keep in mind that records can be processed in any order and, when possible, in parallel. The first record of the table may be processed after the last record."

Therefore I question Vishal's solution. This would work regardless of the order in which the items were processed:

Clear(SomeCollection);
With({
  SortedTable: Sort(
    YourDataSource,
    StartDayTime
  )
},
  ForAll(Sequence(CountRows(SortedTable)),
    Collect(SomeCollection,
      {
        Index:Value,
        StartDayTime:Index(SortedTable,Value).StartDayTime
      }
    )
  )
)

[Dpalmer]

Thank you everyone for the responses!!!

The solution that @sopatte provided worked.  I didn't try the other recommended solutions for one reason, countrows() is non-delegable for SQL as a data source.

One last addition to this now that I can get the collection data correctly, in your solution @sopatte , I also now want to only collect records between a certain StartDayTime and (another column in the collection) EndDayTime. Just not sure where that filter would go in the code you provided.

I am so appreciative of your help!!!!!

Thanks,

David

[Dpalmer]

Update: I did figure out how to add the filter:

SortedTable: Sort(Filter(MySQLTable, DateValue(StartDayTime) > _minDate && DateValue(EndDayTime) < _maxDate),StartDayTime)},

But here's the issue, again, SQL does not like doing that comparison in the Filter.  Give a delegation warning that it may not work on large data sets.

Thoughts?
Thanks,

David

[sopatte]

Liberal use of the With() function can really help prevent complex calculations inside your filters. 

Clear(SomeCollection);
With({
  SortedTable: Sort(
    YourDataSource,
    StartDayTime
  )
},With({
  RowNumbers: Sequence(CountRows(SortedTable))
},
  ForAll(RowNumbers),
    Collect(SomeCollection,
      {
        Index:Value,
        StartDayTime:Index(SortedTable,Value).StartDayTime
      }
    )
  )
)) // end nested Withs

You could also try setting a variable to compare to.  

When it comes to complex calculations involving actual table rows, in the past I've done multiple levels of filtering...so retrieve a larger number of rows from a Datasource into a local Collection, then filter those down from there. It really depends on how many rows you expect to be dealing with, because you don't want to be running up against record count limits. I hope this helps.

MyEmail = User().Email;
Collect(MyData,
  Filter(UserData,
    'Created By'.Email = MyEmail
  )
); // this is expected to be on the order of 3 to 10 records and couldn't possibly be hundreds.

Another possibility is to use a sort of inline multi-level filtering but again you have to make sure your first filter isn't going to be in the thousands of records.

Do you need to use the DateValue() function? Isn't the column type already a DateValue?

[Dpalmer]

Thanks these are good ideas around the With function.

For now I've just created an integer column in the data table, a computed column based on the date, so datetime "03/14/2023 08:30:00.000" would be integer 20230314. I can then use that in the Filter condition.

I appreciate the help, you got me moved forward with my project tremendously.

Thanks!