Sort Gallery by One of Many Dates based on Priority of date and Exclusion fields

[seraph787]

I'm trying to do something that in PowerApps that I can do quite simply in Excel, but as I keep trying, it seems it gets more and more complex.

I have 6 different date fields that I need to use to sort my gallery. These dates are stored in Excel with the entire columns formated as a Date "*3/14/1999". The names of these fields are: 'BLS', 'ACLS', 'MDL', 'ProvExp', 'PrivExp', 'taskFU-Date'

• I also need it to ignore some of these based off of whether the T/F value in 'tBLS', 'tMDL', and 'tPriv' field values are "True" or "False".
• I need to have it compare these dates in a very nested format as well.
• I created 3 labels where any True value returns the 'taskFU-Date' - the false values return as noted below:

In the first label,  if it's False, then it will find the newest date between 'BLS' and 'ACLS'

DateValue(If(ThisItem.tBLS="True","",If(ThisItem.ACLS>ThisItem.BLS,ThisItem.ACLS,ThisItem.BLS)))

In the second label, if it's false, then pass on the 'MDL' date.

DateValue(If(ThisItem.tMDL="True","",ThisItem.'MDL'))

3rd Label: If it's false, then find the oldest date between PrivExp and ProvExp.

Text(If(ThisItem.tPriv="True",ThisItem.'taskFU-Date',If(ThisItem.pExp>ThisItem.pProvExp,ThisItem.pProvExp,ThisItem.pExp)),ShortDate)

Now I'd like to create a 4th label that finds the oldest date from all of these but I can't get it to work

• using If() statements (it says it expects # values)
• I try with DateDiff() but it expects date values and I've tried forcing it to render as a date value via
  • Text(variable,ShortDate)
  • DateValue()
  • DateTimeValue()
  • Value()
  • Whatever I do, it's not a compatible format.

Am I overthinking it or am I not using the right tools for this?

My hope is to use the final date produced from this to sort my gallery.

---------------------------- Additional info requested by TheMexican

Okay, here is some stripped down data.

ID

MDL

BLS

ACLS

PrivExp

ProvExp

taskFU-Date

tBLS

tPriv

tMDL

rBLS

rPriv

rMDL

rFinalSortDate

64

6/30/2019

12/1/2018

5/1/2019

4/6/2018

5/1/2018

FALSE

TRUE

FALSE

5/1/2019

5/1/2018

6/30/2019

5/1/2018

70

5/31/2018

7/1/2019

6/21/2018

5/1/2018

FALSE

TRUE

FALSE

7/1/2019

5/1/2018

5/31/2018

5/1/2018

17

12/31/2019

2/1/2016

6/21/2018

5/1/2018

FALSE

TRUE

FALSE

2/1/2016

5/1/2018

12/31/2019

2/1/2016

71

9/30/2019

3/1/2018

10/11/2018

4/19/2018

TRUE

FALSE

FALSE

4/19/2018

10/11/2018

9/30/2019

4/19/2018

54

5/31/2020

9/1/2019

10/11/2018

FALSE

FALSE

FALSE

9/1/2019

10/11/2018

5/31/2020

10/11/2018

40

12/31/2019

11/1/2018

12/7/2018

FALSE

FALSE

FALSE

11/1/2018

12/7/2018

12/31/2019

11/1/2018

14

9/30/2018

4/1/2019

1/19/2019

5/28/2018

4/28/2018

FALSE

TRUE

FALSE

4/1/2019

4/28/2018

9/30/2018

4/28/2018

32

11/30/2017

5/31/2019

11/28/2019

FALSE

FALSE

FALSE

5/31/2019

11/28/2019

11/30/2017

11/30/2017

28

4/30/2019

5/1/2019

1/19/2019

7/24/2018

6/24/2018

FALSE

TRUE

FALSE

5/1/2019

6/24/2018

4/30/2019

6/24/2018

57

4/30/2020

12/1/2018

12/1/2018

1/19/2019

FALSE

FALSE

FALSE

12/1/2018

1/19/2019

4/30/2020

12/1/2018

59

4/30/2020

11/1/2019

3/16/2019

FALSE

FALSE

FALSE

11/1/2019

3/16/2019

4/30/2020

3/16/2019

58

5/31/2018

9/1/2018

5/5/2019

FALSE

FALSE

FALSE

9/1/2018

5/5/2019

5/31/2018

5/31/2018

74

7/31/2018

2/1/2018

5/5/2019

4/19/2018

TRUE

FALSE

FALSE

4/19/2018

5/5/2019

7/31/2018

4/19/2018

72

7/31/2018

7/1/2018

5/5/2019

7/1/2018

TRUE

FALSE

FALSE

7/1/2018

5/5/2019

7/31/2018

7/1/2018

39

3/31/2019

11/1/2019

9/27/2019

FALSE

FALSE

FALSE

11/1/2019

9/27/2019

3/31/2019

3/31/2019

56

9/30/2019

8/1/2018

5/5/2019

FALSE

FALSE

FALSE

8/1/2018

5/5/2019

9/30/2019

8/1/2018

75

5/31/2019

4/1/2018

5/9/2019

4/19/2018

TRUE

FALSE

FALSE

4/19/2018

5/9/2019

5/31/2019

4/19/2018

For the purposes of visualizing this data here in my post, I put the dates that match the last column in red.

The last FOUR columns are the formula columns I was using in excel (but for the purposes of PowerApps, I had to remove them because my app wouldn't read the table if it had formulas in any fields).  The formulas for the last 4 columns were:

• 'rBLS'  =IF([@tBLS]=TRUE,[@[taskFU-Date]],MAX([@BLS],[@ACLS]))
• 'rPriv' =IF([@tPriv]=TRUE,[@[taskFU-Date]],MIN([@PrivExp],[@ProvExp]))
• 'MDL' =IF([@tMDL]=TRUE,[@[taskFU-Date]],[@MDL])
• 'rFinalSortDate' =MIN([@rBLS],[@rPriv],[@rMDL])

I then sorted my table by 'rFinalSortDate'.

Final Result: In PowerApps, I'd like to sort my Gallery by that final sort date (but I have to find a way to make all the calculations that I was doing with Excel Formulas).

[Anonymous]

Hi seraph77, it would be very helpful for all of us to get a sample of how the data is stored in your Excel (It doesn't have to be real data) and what end result you want. Sometimes it is really hard to follow what you want with words.

for example:

Data source has 9At least 5 or 6 rows):

ACLS  MDL   ProvExp          PrivExp              taskFU-Date

09/23/2017   08/23/2017      07/12/2019        11/12/2018

...

....

and then I want this result:

ACLS

09/23/2017

07/12/2018

This way we can all visualize your data and see what kind of result you need.

[seraph787]

Hi TheMexican,

I've updated the original post with the additional data.

[Anonymous]

Hi Seraph787, here are the formulas I came up with:

To calculate rBLS in your Gallery: 

DateValue(If(ThisItem.tBLS="TRUE",ThisItem.'taskFU-Date',DateValue(ThisItem.BLS) > DateValue(ThisItem.ACLS),ThisItem.BLS,ThisItem.ACLS),"en-US")

To calculate rMDL:

DateValue(If(ThisItem.tMDL="FALSE",ThisItem.MDL,ThisItem.'taskFU-Date'),"en-US")

To calculate rPriv

DateValue(If(ThisItem.tPriv="TRUE",ThisItem.'taskFU-Date',DateValue(ThisItem.PrivExp)>DateValue(ThisItem.ProvExp),ThisItem.ProvExp,ThisItem.PrivExp),"en-US")

I ran out of time at work, but I will check out your last label (4th) tomorrow.

[seraph787]

Hi TheMexican,

Thanks for this! 

I am really eager to try this but there's an issue with my account that has essentially locked me out of accessing PowerApps for the past two days.  One of the Microsoft folks is helping me out with it.  Crossing my fingers it gets resolved soon so I can try these!

[seraph787]

Okay I've been able to get back in and try those.

I made a lot of headway with a few tweaks here and there.  When I went to create the 4th label, I got pretty close.. close enough to try sorting by the 4th label.  Except that's when I realized that no matter what I tried, I can't sort by a label. 

So I did some more digging and found a Post by Jeff_Thorpe for a similar issue (I think getting this far prompted me to try some different variations of the forum searches I had done before). Although his specific answer wasn't marked as the accepted solution because of one minor fix needed below, it seems like it's exactly what I need.  So I set out to do the calculations right inside the Gallery Items field.

What I decided was that I need to do four calculations there, the three that you helped me with earlier + plus one more to do the final calculations based on those three.

SortByColumns(
    AddColumns(
        providerTable,
        "calcBLS",Text(If(tBLS="True",'taskFU-Date',If(BLS > ACLS,If(IsBlank(BLS),ACLS,BLS),If(IsBlank(ACLS),BLS,ACLS))),ShortDate),
        "calcMDL",Text(If(tMDL="False",'CA-MD-Lic-Exp','taskFU-Date'),ShortDate),
        "calcPriv",Text(If(tPriv="True",'taskFU-Date',If(pExp<pProvExp, If(IsBlank(pProvExp),pExp,pProvExp),If(IsBlank(pExp),pProvExp,pExp))),ShortDate),
        "calcMain",If(DateValue(Text("calcBLS",ShortDate),"en-US")>DateValue(Text("calcMDL",ShortDate),"en-US"),
	        If(DateValue(Text("calcMDL",ShortDate),"en-US")>DateValue(Text("calcPriv",ShortDate),"en-US"),DateValue(Text("calcPriv",ShortDate),"en-US"),DateValue(Text("calcMDL",ShortDate),"en-US")),
	        If(DateValue(Text("calcBLS",ShortDate),"en-US")>DateValue(Text("calcPriv",ShortDate),"en-US"),DateValue(Text("calcPriv",ShortDate),"en-US"),DateValue(Text("calcBLS",ShortDate),"en-US")))
),
    "calcMain",
    Ascending)

But I am finding out something more... You can't perform the 4th calculation based on the 1st three.  In the code above, you'll see that for the 4th calculation "calcMain", I am referencing the previous columns in quotes.  I've tried this without quotes (calcBLS) with .Text (calcBLS.Text), without the Text(...,ShortDate), etc.  So the above "calcMain" was the only variant I could come up with to get it to stop the errors.  

Once I got to 0 errors, I went ahead and created a label in my gallery to figure out what is actually being passed through.  I should have known... it was treating the "calcBLS" and other "calcMDL", etc.  As text strings, so the label would read: calcBLS.

So am I right in that I can't calculate the 4th one inside the Gallery Items field?  Any other ways to calculate the 4th one and sort by it?

[Jeff_Thorpe · Accepted solution]

Have you tried nesting the AddColumns. The inner AddColumns would create the first three column then the outter AddColumns would create the 4th column which uses the first three columns. Since the 4th column is created in the outter AddColumns the inner columns that were created should be available to use.

SortByColumns(
  AddColumns(
    AddColumns(
        providerTable,
        "calcBLS",Text(If(tBLS="True",'taskFU-Date',If(BLS > ACLS,If(IsBlank(BLS),ACLS,BLS),If(IsBlank(ACLS),BLS,ACLS))),ShortDate),
        "calcMDL",Text(If(tMDL="False",'CA-MD-Lic-Exp','taskFU-Date'),ShortDate),
        "calcPriv",Text(If(tPriv="True",'taskFU-Date',If(pExp<pProvExp, If(IsBlank(pProvExp),pExp,pProvExp),If(IsBlank(pExp),pProvExp,pExp))),ShortDate)
    ),
	"calcMain",If(DateValue(Text(calcBLS,ShortDate),"en-US")>DateValue(Text(calcMDL,ShortDate),"en-US"),
	        If(DateValue(Text(calcMDL,ShortDate),"en-US")>DateValue(Text(calcPriv,ShortDate),"en-US"),DateValue(Text(calcPriv,ShortDate),"en-US"),DateValue(Text(calcMDL,ShortDate),"en-US")),
	        If(DateValue(Text(calcBLS,ShortDate),"en-US")>DateValue(Text(calcPriv,ShortDate),"en-US"),DateValue(Text(calcPriv,ShortDate),"en-US"),DateValue(Text(calcBLS,ShortDate),"en-US")))
  ),
  "calcMain",
  Ascending
)

[seraph787]

It works!  This nesting solution with AddColumns is perfect for this.

Thank you Jeff!

And thank you TheMexican for helping me get the DateValues cleaned out for the first three calculations.