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
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
Top Kudoed Authors
Users online (2,526)