Sort Dataverse on empty dates

[GilbertS]

Hi,

We have a Dataverse database in which we store our CV data. All work experiences have a start date since it is mandatory. Some work experiences have an end date as well, but some will have an empty end date. Now I want to list the work experiences based on end date descending, where an empty date should be on top. This does not work in the simple order statement: cr438_enddate desc, cr438_startdate desc. In that way I get the empty end dates at the bottom.

Now I added a calculated field which will fill an end date on Now() in new field enddate_calculated if field enddate is empty. That works out fine. But I cannot fill the end date in that same calculated field as well if it is filled. That is caused by being a date only and not a date-time field. So I have a field enddate which contains either empty field or a filled field and I have a calculated field which contains either filled field or empty field. If I order it first on caculated enddate field and then on real enddate, my empty enddate fields (so a filled calculated enddate field) always at the bottom. Whether I sort it descending or ascending, it does not make a difference. I tried other work-arounds to have other calculated fields to get a good sorting.

What I need is sorting on enddate first and startdate second. So experience with no enddate and the most recent startdate needs to be on top. Followed by the experience with no enddate and an older startdate. After that the most recent enddate needs to be on top. If two experiences have the same enddate, the most recent startdate must be on top.

So order:

• 1-1-2021 until now
• 31-7-2020 until now
• 1-1-2021 until 1-4-2021
• 31-8-2020 until 1-4-2021
• etc.

Any suggestions?

[abm]

Hi @GilbertS 

If you filter your list rows by enddate as ascending and start date as descending you will get your desired results.

Please see below.

[GilbertS]

Hi @abm,

That does not work. For 2 reasons. First reason is that the emptie dates are getting listed as last. Second reason is that oldest end date is getting at top. I need the emptie dates at top (ordered by newest startdate at top) and after that all enddates sorted with newest end date at top. I already created a calculated field for empty enddates where I put in an AddYears(1, Now()). And if I sort on that calculated field, then enddate and then startdate it is still not working. I can also not copy the enddate to that calculated field, since my enddate is a date only field.

Do you have another solution?

[abm]

Hi @GilbertS 

I thought it should work and here is my test results.

[GilbertS]

Hi @abm ,

Is there a difference between sorting a date/time field or a date only field?

[GilbertS]

Hi @abm ,

I ran the folowing sorting:

First: cr438_einddatum_sort desc, cr438_einddatum desc, cr438_startdatum desc

Second: cr438_einddatum_sort asc, cr438_einddatum desc, cr438_startdatum desc

cr438_einddatum_sort contains a date in the future if enddate is empty

cr438_einddatum contains real enddate. This is an optional field, since if it is actual assignment this field is empty.

cr438_startdatum contains real startdate. This is a mandatory field.

Both runs result in same order:

Later in the flow, the empty enddate is filled by a string "heden" (dutch for now).

This is the content of the data in both situations:

{
"statusCode": 200,
"headers": {
"Pragma": "no-cache",
"Transfer-Encoding": "chunked",
"Vary": "Accept-Encoding",
"x-ms-request-id": "0cafd47f-896c-4f4b-84b1-ac8cafd256d4",
"Strict-Transport-Security": "max-age=31536000; includeSubDomains",
"X-Content-Type-Options": "nosniff",
"X-Frame-Options": "DENY",
"Cache-Control": "no-store, no-cache",
"Set-Cookie": "",
"Timing-Allow-Origin": "*",
"x-ms-apihub-cached-response": "true",
"Date": "Mon, 06 Sep 2021 09:27:24 GMT",
"Content-Type": "application/json; charset=utf-8; odata.metadata=minimal",
"Expires": "-1",
"Content-Length": "87698"
},
"body": {
"@odata.context": "",
"value": [
{
"cr438_startdatum": "2011-12-01",
"cr438_einddatum": "2020-12-31",
"cr438_einddatum_sort": null,
},
{
"cr438_startdatum": "2019-04-01",
"cr438_einddatum": "2020-04-30",
"cr438_einddatum_sort": null,
},
{....
},
{
"cr438_startdatum": "2018-05-01",
"cr438_einddatum": null,
"cr438_einddatum_sort": "2031-09-06T09:27:24Z",
},
{
"cr438_startdatum": "2015-07-01",
"cr438_einddatum": null,
"cr438_einddatum_sort": "2031-09-06T09:27:24Z",
}
]
}
}

So null will always be on top over the filled cr438_einddatum_sort. No matter if it is descending or ascending. And I was not able to fill cr438_einddatum_sort with the same date as the cr438_einddatum if it was filled. Probably due to the fact that cr438_einddatum is a date only field and Dataverse does not allow date copying in calculated fields if it is date only.

Any solution?

[abm]

Hi @GilbertS 

For datetime field it should cater the time as well. For date my understanding is it shouldn't matter.

Thanks

[GilbertS · Accepted solution]

Hi @abm ,

I do not know why it did not work. I solved it differently. I have made a new calculated field of type number. If enddate is filled it put's a 1. If enddate is not filled it put's a 2. If I now first sort on this field descending, then on enddate descending and finally on startdate descending. This gives me the wanted order. All current assignments on top (ordered by startdate, since enddate is empty) and after that all assignments on realized enddate sorted descending (most recent on top).

Thanks for your help!

[abm]

Hi @GilbertS 

Thanks for the update and glad to hear that you got a working solution. The enddate sort order by asc should give the blanks ones first.