CODEWITHSUNDEEP
sharepointpowerapps
Perry J
Perry J

Reputation: 365

PowerApps: getting most recent record having a condition

Is it possible in PowerApps to get at the latest record, from a SharePoint list, having a certain condition? In my case I know how to get the number, using something like this:

CountIf(Applications, Status = "Pending")

But, I would also like to get the most recent one of them. Well I suppose that would imply two conditions where the second is a Date column, but is it possible to sort it and then pick the first? Grateful for any guidance...

Upvotes: 3

Views: 11647

Answers (2)

vapcguy
vapcguy

Reputation: 7547

I had a similar situation where I needed to get the latest date that a person was verified, and it would happen annually. So each year there'd be a new record in the list - but I just needed to display the data from the last one on the form. It sounds like you were trying to do the same.

I had a textbox where the person would put in an Employee ID, and then it would match on that person, fill the person's name in a box, and show the latest "Validation Date". So here's how I set it up:

Validation Date's DefaultDate property

First(
    SortByColumns(
        Filter( // below is listName, madeUpFieldName, realFieldName
            AddColumns('Validations List',"ValDate",'Validation Date'),Title=txtWorkID.Text
        ),
    "ValDate",   // madeUpFieldName
    SortOrder.Descending)
).'Validation Date'   // realFieldName

Employee Name Text property (it was stored in a Text field on the list, and therefore was a textbox on the form)

LookUp('Validations List',txtWorkID.Text="Title").'Employee Name'

The name didn't require the sorting because every record for that WorkID will have that person's name. But if you want any specific value for the latest date's record, you'll have to do it just like the Validation Date, but just change the name at the very end, to indicate the field you want to bring back.

So, to more specifically address your particular issue... it would look something like this, to fill a DefaultDate property of your list's date you want displayed, assuming it was a field called Validation Date:

First(
    SortByColumns(
        Filter(
            AddColumns('Applications',"ValDate",'Validation Date'), Status = "Pending"
        ),
    "ValDate",
    SortOrder.Descending)
).'Validation Date'

And just change 'Validation Date' at the end to whatever field you're trying to retrieve, and just put the code in the Text property for any other Text field.

If you want to fill a Person field, you'd need to do a little more. You'd be filling the DefaultSelectedItems then. To do this, you'd need to get the person's email, which you could probably do something like this:

Set(EmpMail, First(
    SortByColumns(
        Filter(
            AddColumns('Applications',"ValDate",'Validation Date'), Status = "Pending"
        ),
    "ValDate",
    SortOrder.Descending)
).'Employee Name'.mail)

That assumes the field doesn't allow multiple people. And then you'd do this to fill the DefaultSelectedItems:

{'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                   Claims: "i:0#.f|membership|" & EmpMail,
               Department: Office365Users.UserProfileV2(EmpMail).department,
              DisplayName: Office365Users.UserProfileV2(EmpMail).displayName,
                    Email: EmpMail,
                 JobTitle: Office365Users.UserProfileV2(EmpMail).jobTitle,
                  Picture: ""
}

You would need to add Office365Users into your data sources.

Upvotes: 1

carlosfigueira
carlosfigueira

Reputation: 87293

Yes, you can use an expression that sorts the filtered data source by the date in descending order, and take the first element, like in the expression below:

First(
    SortByColumns(
        Filter(Applications, Status = "Pending"),
        "DateColumn",
        Descending))

Upvotes: 5

Related Questions