Groupby multiple sharepoint list column; latest check in time per person, date,office

[Yvonzim]

Hi, 

Im just starting with powerapps and powerautomate but struggle with filters and codes

Maybe i started the wrong way but i have a button in powerapps that inserts a username, IN\OUT, date, time and officename into a sharepoint list. 

In order to find out who is in de building at that time i want to present a list in Powerapps.

Because they can check in and out several times a day i only want to filter the persons with there latest check IN

(step 2: and no check OUT after check in time)

For now i have a filter that works, but only for one person who checked in as latest.

Filter('Precense list',Time=First(Sort(Distinct('Precense list',Time),Result,Descending)).Result)

I want this filter to show ALL persons that checked in with there latest check in time.

(step 2: and no check OUT after check in time) 

Hope someone can help me to achieve this

[v-bofeng-msft]

Hi @Yvonzim :

Could you tell me:

• Do you want to get the latest check-in time of all users?
• In which field is person stored?What is its data type?

I assume there is a filed named 'YourPersonColumn' and it do not allow multiple selections.

The point is to traverse the list of persons after deduplication.

Plese try this code:

ForAll(
    Distinct(   /*Get the list of persons after deduplication*/
        'Precense list',
        YourPersonColumn
    ),/*Traverse the list of persons after deduplication*/
    First(
        Sort(
            Filter(
                'Precense list',
                YourPersonColumn.Claims = Result.Claims
            ),/*Filter records created by specified users*/
            Time,
            Descending
        )
    )/*Get the latest record of the check-in time created by the specified user*/
)

Best Regards,

Bof

[Yvonzim]

@v-bofeng-msft , 

Thanks for the quick response.

Yes i want the latest check-in time of all users 

Personname is stored in userName (Text) and userID

" I assume there is a filed named 'YourPersonColumn' and it do not allow multiple selections."

Dont understand what you are saying but userName is unique and user can check-in several times a day.

i get the following error.

What does the claims function mean?

[Yvonzim]

hi @v-bofeng-msft , 

I mannaged to do what i wanted in excel (thanks to this video: https://youtu.be/zsTaUNRQhPI).

As a newbe to coding im not able to transelate that into a Powerapps function. Now i have come so far i hope you can help me with the output of the SP List in Powerapps.

I have a SP list with UserName,Date,Time and Office which is update by a flow every time a person hits the Check IN \Check Out button in Powerapps

To define if someone is IN or OUT: I want to the latest Check in and de Latest Check OUT. I want to make the math LatestCheckOUT minus LatestCheckIN. When Latest Check OUT is positief User is OUT otherwise he is still in.

I used the GroupBy function in Excel for 3 columns, but I realy cant make it work in Powerapps. (Only one Groupby)

Hope you or anyone can help met to finalize this great app i have built so far 🙂

[v-bofeng-msft]

Hi @Yvonzim :

Before this, I didn’t know that userName was of Text type.Plsase try this code:

ForAll(
    Distinct(
        AttendancelList,
        userName
    ),
    First(
        Sort(
            Filter(
                AttendancelList,,
                userName = Result
            ),
            Time,
            Descending
        )
    )
)

In addtion,the link you provided seems to be invalid.But not matter,please try the formula I mentioned above.If you there is any problem,please feel free to let me know.

Best Regards,

Bof

[Yvonzim]

Hi @v-bofeng-msft ,

This works!

But what i found out in the meantime is that i want more 🙂

I need it per user, date and office. Is that possible?

a user can check in and out a few times per day. I need the last checkin time and last checkout time (when available).

and after that I want the result from check out minus check in.

Sorry for asking so much but it would be great to get it work.

example of the SP list below (al text columns):

 

[v-bofeng-msft · Accepted solution]

Hi @Yvonzim :

Please try :

ForAll(
    Distinct(
      Concatenate(
         AttendancelList.userName,";;",
         AttendancelList.'Check in\out',";;",
         AttendancelList.Office,";;",
         AttendancelList.Date
        ),Result),
    First(
        Sort(
            Filter(
                AttendancelList,
                userName = Last(FirstN(Split(Result,";;"),1)).Result &&
                'Check in\out'= Last(FirstN(Split(Result,";;"),2)).Result &&
                Office= Last(FirstN(Split(Result,";;"),3)).Result &&
                Date= Last(FirstN(Split(Result,";;"),4)).Result
            ),
            Time,
            Descending
        )
    )
)

In addition, I suggest you create a new thread to ask questions about "the result from check out minus check in".

Judging from the data sheet you provided, the check-in and check-out records do not appear in pairs, and there is a situation where the check-out time is earlier than the check-in.

If my post helps, then please consider Accept it as the solution to help others.Thanks

Best regards,

Bof

[Yvonzim]

Thanks @v-bofeng-msft, 

I know i ask a lot of code in one question,

I will investigate what you provided and how to get further.

Thanks a lot!

(Note:  in Excel i did 2 query's, one for lastCheck In and one for LastCheck out, afterwards i did combine these two in a new table) 

[Yvonzim]

Hi @v-bofeng-msft 

One more thing, 

Im trying to figure out myself but coding is hard for me 🙂

i would like to split this code in two collections, so i have the result with only the latest CHECK " IN" and an other result with only the latest check "OUT"

From there I can probably combine these results?

[v-bofeng-msft]

Hi @Yvonzim :

Do you want to get these three collections:

One for Check-in

ForAll(
    Distinct(
      Concatenate(
         AttendancelList.userName,";;",
         AttendancelList.'Check in\out',";;",
         AttendancelList.Office,";;",
         AttendancelList.Date
        ),Result),
    First(
        Sort(
            Filter(
                AttendancelList,
                userName = Last(FirstN(Split(Result,";;"),1)).Result &&
                'Check in\out'= Last(FirstN(Split(Result,";;"),2)).Result &&
                Office= Last(FirstN(Split(Result,";;"),3)).Result &&
                Date= Last(FirstN(Split(Result,";;"),4)).Result
            ),
            Time,
            Descending
        )
    ),'Check in\out'="IN"
)

One for Check-Out

ForAll(
    Distinct(
      Concatenate(
         AttendancelList.userName,";;",
         AttendancelList.'Check in\out',";;",
         AttendancelList.Office,";;",
         AttendancelList.Date
        ),Result),
    First(
        Sort(
            Filter(
                AttendancelList,
                userName = Last(FirstN(Split(Result,";;"),1)).Result &&
                'Check in\out'= Last(FirstN(Split(Result,";;"),2)).Result &&
                Office= Last(FirstN(Split(Result,";;"),3)).Result &&
                Date= Last(FirstN(Split(Result,";;"),4)).Result
            ),
            Time,
            Descending
        )
    ),'Check in\out'="OUT"
)

One for Check-in & Check-Out

ForAll(
    Distinct(
      Concatenate(
         AttendancelList.userName,";;",
         AttendancelList.'Check in\out',";;",
         AttendancelList.Office,";;",
         AttendancelList.Date
        ),Result),
    First(
        Sort(
            Filter(
                AttendancelList,
                userName = Last(FirstN(Split(Result,";;"),1)).Result &&
                'Check in\out'= Last(FirstN(Split(Result,";;"),2)).Result &&
                Office= Last(FirstN(Split(Result,";;"),3)).Result &&
                Date= Last(FirstN(Split(Result,";;"),4)).Result
            ),
            Time,
            Descending
        )
    )
)

You can also use the collect function if you want to merge collections. But I think my solution is more convenient.

Best Regards,

Bof