Creating a collection from a single Dataverse table row.

[ThomasRConnor]

I have an app where I need to create priorities so I have a table with the following rows.

Priority_1, Priority_1_Category, Priority_1_Text,Priority_2, Priority_2_Category, Priority_3_Text,...Priority_13, Priority_13_Category, Priority_13_Text,DatePrioritiesSet

The aim is to provide a point in time view of the priorities at a specific time. I've got the output displayed in a gallery where I can select the date of the entry and it shows what the priorities looked like at the point-in-time.

However, these priorities need to change and to prevent them being re-entered every time, the user can select set of priorities and it will populate the editable fields where the user can make changes - importantly, change the order of the priority (the first column is a drop down 1-13). This all saves fine, but the updated entry doesn't order the results. The reason being, if to start, 'Priority_1' was set to '1', that data field will not move in the gallery, so if it's be changed to '2', it will still be displayed in the value of thisitem.Priority_1 in the gallery. Hopefully this screenshot will make sense.

What I'd like to do, is create a collection using the Dataverse table (could be any datasource) that allows me to apply a a sort to the point-in-time view, using a single Priority column that'll contain the values from 'Priority_1' through 'Priority_13' and they'd also be columns for Category and Text - finally, each row would have the value of 'DatePrioritiesSet' to allow the point-in-time to be grouped and displayed using a filter.

For info, the table that holds the priorities has a relationship with a master table as each record will have one or more sets of priorities linked to it.

TIA

Tom

[ThomasRConnor · Accepted solution]

I approached this in a different way and moved to only 4 columns. 'Priority Number', 'Priority Category', 'Priority Text' and 'Date and Time added'. I then used a number of Patch formulas to update the list of priorities to this list, each having the same Date & Time. I then used a filter to show the entries that matched the time using a distinct filter on the dropdown to select the specific Date & Time for the entry I wanted to review.

To add the entries to the text fields to allow me to update, I used a button that created a collection based on the selected filter.

Collect(

col_CurrentStrategy,
Filter(

'TFC-InitialWorkingStrategies',

'IWS-Linked-TFCMaster'.TFCReferenceNumber = var_CurrentLog.TFCReferenceNumber,

IWSDateTime = drp_ViewIWSDate_TR.Selected.Result

)

)

I then used a lookup against each of the text boxes to show the corresponding info - For the 'Principal Number' I used -

'LookUp(col_CurrentStrategy,'Principal Number' = 1,'Principal Number')'.

For the Category I used

'LookUp(col_CurrentStrategy,'Principal Number' = 1,'Principal Category')'.

And for the 'Principal Text' I used

'LookUp(col_CurrentStrategy,'Principal Number' = 1,'Principal Text')'. For the next set of boxes I changed the number to 2 and so on.

This made sure when the priorities were seen in the gallery & when updating them, they would be shown in order of the 'Principal Number'.