cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gochix
Solution Sage
Solution Sage

Display empty records in the gallery

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)
1 ACCEPTED SOLUTION

Accepted Solutions
BCBuizer
Super User
Super User

Hi @Gochix ,

 

Third time's a charm?

 

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

 



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

View solution in original post

6 REPLIES 6
BCBuizer
Super User
Super User

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)
)

 



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

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:

Gochix_0-1659089037736.png

 

BCBuizer
Super User
Super User

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)
)

 



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

Hi @BCBuizer ,

 

This still comes up with an error for some reason.

 

Here is the columns that are stored in collections

Collection A:

Gochix_0-1659092769555.png

Collection B:

Gochix_1-1659092789139.png

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

Gochix_2-1659092810811.png

 

 

BCBuizer
Super User
Super User

Hi @Gochix ,

 

Third time's a charm?

 

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

 



Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

Hi @BCBuizer ,

It is indeed 🙂

 

Thank you for your efforts and time.

Much appreciated.

 

Regards

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (2,506)