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

[jeffgeno]

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?

[v-micsh-msft · Accepted solution]

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

[jeffgeno]

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.