cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seraph787
Level: Powered On

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

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.

 

IDMDLBLSACLSPrivExpProvExptaskFU-DatetBLStPrivtMDLrBLSrPrivrMDLrFinalSortDate
646/30/201912/1/20185/1/20194/6/2018 5/1/2018FALSETRUEFALSE5/1/20195/1/20186/30/20195/1/2018
705/31/20187/1/2019 6/21/2018 5/1/2018FALSETRUEFALSE7/1/20195/1/20185/31/20185/1/2018
1712/31/20192/1/2016 6/21/2018 5/1/2018FALSETRUEFALSE2/1/20165/1/201812/31/20192/1/2016
719/30/20193/1/2018 10/11/2018 4/19/2018TRUEFALSEFALSE4/19/201810/11/20189/30/20194/19/2018
545/31/20209/1/2019 10/11/2018  FALSEFALSEFALSE9/1/201910/11/20185/31/202010/11/2018
4012/31/201911/1/2018 12/7/2018  FALSEFALSEFALSE11/1/201812/7/201812/31/201911/1/2018
149/30/20184/1/2019 1/19/20195/28/20184/28/2018FALSETRUEFALSE4/1/20194/28/20189/30/20184/28/2018
3211/30/20175/31/2019 11/28/2019  FALSEFALSEFALSE5/31/201911/28/201911/30/201711/30/2017
284/30/20195/1/2019 1/19/20197/24/20186/24/2018FALSETRUEFALSE5/1/20196/24/20184/30/20196/24/2018
574/30/202012/1/201812/1/20181/19/2019  FALSEFALSEFALSE12/1/20181/19/20194/30/202012/1/2018
594/30/202011/1/2019 3/16/2019  FALSEFALSEFALSE11/1/20193/16/20194/30/20203/16/2019
585/31/20189/1/2018 5/5/2019  FALSEFALSEFALSE9/1/20185/5/20195/31/20185/31/2018
747/31/20182/1/2018 5/5/2019 4/19/2018TRUEFALSEFALSE4/19/20185/5/20197/31/20184/19/2018
727/31/20187/1/2018 5/5/2019 7/1/2018TRUEFALSEFALSE7/1/20185/5/20197/31/20187/1/2018
393/31/201911/1/2019 9/27/2019  FALSEFALSEFALSE11/1/20199/27/20193/31/20193/31/2019
569/30/20198/1/2018 5/5/2019  FALSEFALSEFALSE8/1/20185/5/20199/30/20198/1/2018
755/31/20194/1/2018 5/9/2019 4/19/2018TRUEFALSEFALSE4/19/20185/9/20195/31/20194/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).

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Formulas

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
)


--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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

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
Level: Powered On

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

Hi TheMexican,

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

seraph787
Level: Powered On

-

 
Anonymous
Not applicable

Formulas

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
Level: Powered On

Re: Formulas

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
Level: Powered On

Re: Formulas

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?

Super User
Super User

Re: Formulas

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
)


--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

seraph787
Level: Powered On

Re: Formulas

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.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,480)