cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeffgeno
Advocate IV
Advocate IV

Creating a new Table from Two SharePoint Lists, ForAll and Filter Don't Work Together

I'm attempting to create a table just for viewing from data in two different SharePoint lists. The first list contains the names of the students and the second is a list of statuses at different times in each day. I'd like to turn the list of statuses into a table for easy viewing of a single day.

 

I've attached pictures of the list of Students, the list of Statuses, and the table that's created. The ForAll loop I'm using seems to work correctly for the Name column of the new table, but only ever seems to search the second list for the statuses relating to the first student in the ForAll loop.

 

Here's the code I'm using to generate the table. I've tried both a LookUp() and a First(Filter()) function and each return the same results. Only the information for the first student in the list is looked up and added to the table.

 

ForAll(
    Filter(Students,Residential=1),
        Collect(DailyLog,{
            Name: '{Name}', 
            WakeUp: First(Filter('Residential Student Log',Title='{Name}', Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Wake Up")).Time,
            Breakfast: First(Filter('Residential Student Log',Title='{Name}', Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Breakfast")).Time, 
            ReturnBkfst: First(Filter('Residential Student Log',Title='{Name}', Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Return from Breakfast")).Time,
            BrushTeeth: First(Filter('Residential Student Log',Title='{Name}', Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Brush Teeth")).Time,
            RoomCheck: First(Filter('Residential Student Log',Title='{Name}', Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Room Check")).Time,
            OffFloor: First(Filter('Residential Student Log',Title='{Name}', Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Off Floor")).Time
            }))

Is there a better way to do this?

1 ACCEPTED SOLUTION

Accepted Solutions
v-micsh-msft
Community Support
Community Support

Hi,

 

Your filter for the status fitlers just within the 

'Residential Student Log'

table, so the result is always the first record.

 

 

Please take a try to change the formula as below:

ForAll(
    RenameColumns(Filter(Students,Residential=1),"Title","Title1"),
        Collect(DailyLog,{
            Name: Title1, 
            WakeUp: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Wake Up")).Time,
            Breakfast: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Breakfast")).Time, 
            ReturnBkfst: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Return from Breakfast")).Time,
            BrushTeeth: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Brush Teeth")).Time,
            RoomCheck: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Room Check")).Time,
            OffFloor: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Off Floor")).Time
            }))

Which should make it work.

Function reference.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-micsh-msft
Community Support
Community Support

Hi,

 

Your filter for the status fitlers just within the 

'Residential Student Log'

table, so the result is always the first record.

 

 

Please take a try to change the formula as below:

ForAll(
    RenameColumns(Filter(Students,Residential=1),"Title","Title1"),
        Collect(DailyLog,{
            Name: Title1, 
            WakeUp: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Wake Up")).Time,
            Breakfast: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Breakfast")).Time, 
            ReturnBkfst: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Return from Breakfast")).Time,
            BrushTeeth: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Brush Teeth")).Time,
            RoomCheck: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Room Check")).Time,
            OffFloor: First(Filter('Residential Student Log',Title=Title1, Date=Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate), Status="Off Floor")).Time
            }))

Which should make it work.

Function reference.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks. I thought it was something like that, but I was confused at how it was correctly populating the first column and not the rest.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (5,484)