Sorting Column in HTML Table with Blank Fields at the Bottom

[MGIOSEFFI]

Hi everyone,

I am working on a new flow that pulls a list of tasks from a SharePoint List, converts it into an HTML table and then automatically sends this list to a group chat on Microsoft Teams. I have sorted my column in the HTML table based on due date for the taskings. However, the issue I am encountering is that any fields in my due date column that are blank (as they do not have a due date) are appearing at the top of my HTML table. However, I want them to appear at the bottom of my HTML table with all items with a due date placed in ascending order at the top of my HTML table. Does anyone have any thoughts on how to do this? I have been experimenting with using a "Compose" action with an expression "sort(variables('retrievedItems'), if(empty(item()?['DueDate']), '9999-12-31T23:59:59Z', item()?['DueDate']), 'asc')" as I thought using a dummy generic date would be a workaround, but I am getting a notification that this expression isn't valid. 

Here is a picture of my flow:

Any help or guidance would be greatly appreciated.

Thank you!

[ManishSolanki]

Hi @MGIOSEFFI 

You could break this in two steps.

Step 1:  Use "Select" action to set default date for empty dates. Pass the variable 'retrievedItems' as its input and use the below expression to set default date for blank or missing date in DueDate column:

if(empty(item()?['DueDate']), '9999-12-31T23:59:59Z', item()?['DueDate'])

Step 2: Sort the output body of previous select action using an expression. You can perform sorting in compose action by using below expression:

sort(body('Select'),'DueDate')

Finally, pass the output of compose action as input to "Create HTML table".

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[MGIOSEFFI]

@ManishSolanki thank you very much for getting back to me. Is there a way I can get blank fields to appear at the bottom of my "Due Date" column without having to enter a fake date? Ideally, I would like to find a way to have all Due Dates in my HTML table in sorted in ascending order and then just have blank fields at the bottom of my HTML table.

[ManishSolanki]

Hi @MGIOSEFFI 

Yes, there is a workaround. Use the below expression to reverse the array present in the variable:

reverse(sort(variables('retrievedItems'),'DueDate'))

Pass the output of above expression as the input to "Create HTML table" to push back empty dates at the bottom.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[MGIOSEFFI]

@ManishSolanki Based on my limited understanding of Power Automate, won't this just sort all of my dates in descending order? While I do want blanks at the bottom of my HTML table, I still want the fields that have a Due Date to be in ascending order.

[ManishSolanki · Accepted solution]

Hi @MGIOSEFFI 

Yes, you are correct. I have another workaround for this problem. Here is the sample flow.

I have stored input array in compose action and for this example, I have taken only one property 'DueDate' in the input array:

[
{
"DueDate": "2024-04-25"
},
{
"DueDate": "2024-04-01"
},
{
"DueDate": ""
},
{
"DueDate": "2024-04-03"
}
]

Next, add filter action to filter elements whose DueDate is empty. Pass the sorted array as the input of "Filter array" action. I have used expression to sort the array in compose action:

Expression used in 'Form' parameter:

sort(outputs('Compose'),'DueDate')

Click 'Edit in advanced mode' button and enter the below query in the textbox:

@equals(item()?['DueDate'], '')

Similarly, add another "Filter array" as a parallel action which filter out nonempty DueDate from sorted array. For sorting, add the same expression in the 'From' parameter:

sort(outputs('Compose'), 'DueDate')

Click 'Edit in advanced mode' button and enter the below query in the textbox:

@not(equals(item()?['DueDate'], ''))

Finally, join the result of both filter arrays in such a way that nonempty should appear first and empty will be appended at the bottom. To achieve this, add an expression using union() function with first parameter as nonempty sorted output of filter array 2 and second parameter will the output of filter array. Add compose action and enter the below expression:

union(body('Filter_array_2'),body('Filter_array'))

 The output of compose 2 action will be the resultant array with empty DueDate pushed at the bottom position.

Output:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[MGIOSEFFI · Accepted solution]

@ManishSolanki thank you! I think that may have worked so far. The issue I am encountering now is trying to figure out how to add the output to my HTML table. As you can see from the screenshot below, I have tried adding the Output of the "Compose 2" action to build my table in the "Append to array variable" action, but I am getting a notification that this must be a valid json.

[ManishSolanki]

Hi @MGIOSEFFI 

This is error due to extra colon ':' at the end of 'Requester Name' property value. To fix the json, pls remove that colon from there.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[MGIOSEFFI]

@ManishSolankithank you for that- that helped fix that issue. I am very sorry for all of my questions, but I have one more issue I am encountering that I am hoping you can help me with. I am getting the following error in my "Filter Array" action: The template language function 'sort' did not find the named sortField 'DueDate' on one or more objects in the array. After inspecting the outputs of my "Compose" action prior to the "Filter Array" action, I have realized that any "Due Date" fields that are blank are actually completely left out the outputs of the Compose action (i.e. there is no Due Date field at all). The outputs of my "Compose" action only have a Due Date field for taskings when there is actually a date listed.  I think this is what is causing the error, but I am not sure how to get around this 

[ManishSolanki]

Hi @MGIOSEFFI 

Get items action does return null or missing values in the output value object.

To get those values, use select action that accepts output value array from get items action as its input. In the map object, enter 'DueDate' as the key and for its value use the below expression:

if(empty(item()?['DueDate']),'',item()?['DueDate'])

The expression will set the empty string if DueDate value is missing or empty in the output of get items.

Use the output body of Select action further that has DueDate property in all elements.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[MGIOSEFFI]

Hi @ManishSolanki ,

I have been trying this, but am receiving the following error when I try to save my flow: "Fix invalid expression(s) for the input parameter(s) of operation ‘Filter_array" and "Fix invalid expression(s) for the input parameter(s) of operation ‘Filter_array_2". Here is a screenshot of my flow:

v

Please let me know if you have any suggestions- I am assuming my "Sort" function is not quite right, but I am not quite sure what could be causing the issue. I really appreciate all of your help!

[ManishSolanki]

Hi @MGIOSEFFI 

Pls use below query in:

Filter array:

@equals(item()?['Due Date']

Filter array :

@not(equals(item()?['Due Date'], ''))

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[MGIOSEFFI]

@ManishSolanki thank you- that worked! How can I now add the output of the "Compose 2" action (

union(body('Filter_array_2'),body('Filter_array')) to my HTML table? If you look at my "Append to Array" function below, you will see I have added the Output of "Compose 2" for my Due Date field.

The problem is now all of the due dates from my "Compose 2" action are now being applied to each tasking. Here is an example of what I mean below. Note the multiple due dates that are indicated for a single tasking.