Display empty records in the gallery

[Gochix]

Hi,

Having 2 different Sharepoint Lists as database.

In List A is stored a summary of : Name of Area , Score.

In List B is stored all possible Area names.

What I would like to do display in the gallery is : If any Area in the List A is missing a record for Area from List B then only to display the missing Area names.

Anyone could help to figure this out, please?

Trying to use Filter option but for some reason getting an error that can't compare Text,Table types:

Filter(MissingAreas,Title = '5s-Summary'.Area)

[BCBuizer]

Hi @Gochix,

The below formula adds a column (temp) to 'listB' which stays blank if there's no entry in 'List A'. That can then be filtered out to create the list of missing areas:

Filter(
    AddColumns(
        'List B',
        "temp",
        LookUp(
            'List A',
            AreaID = 'List B'.Title
        ),
    IsBlank(temp)
)

[Gochix]

Hi @BCBuizer ,

There was a mistake in the code I believe.

An extra ")" 

Filter(
    AddColumns(
        'List B',
        "temp",
        LookUp(
            'List A',
            Area = 'List B'.Title
        )),
    IsBlank(temp)
)

 However I've tried it out and getting an error:

[BCBuizer]

Hi @Gochix ,

Indeed I forgot to specify the column to return in the LookUp(). Please try the below:

Filter(
    AddColumns(
        'List B',
        "temp",
        LookUp(
            'List A',
            Area = 'List B'.Title,
            Area
        )),
    IsBlank(temp)
)

[Gochix]

Hi @BCBuizer ,

This still comes up with an error for some reason.

Here is the columns that are stored in collections

Collection A:

Collection B:

Same error shows up. That it's not possible to compare Text with Table. Even if they both are Text columns..

[BCBuizer · Accepted solution]

Hi @Gochix ,

Third time's a charm?

Filter(
    AddColumns(
        'List B',
        "temp",
        LookUp(
            'List A',
            Area = 'List B'[@Title],
            Area
        )),
    IsBlank(temp)
)

[Gochix]

Hi @BCBuizer ,

It is indeed 🙂

Thank you for your efforts and time.

Much appreciated.

Regards