Search gallery and match against data in another list

[sudosaurus]

Hello,

In my app I have 3 search boxes to search across results in the gallery "DocData".

The gallery is showing items that have been saved to the DocData list from a form on another screen.

Some of the details on the screen are being pulled from another list - "Buildings".

For example in the Items property of the gallery, I have the following Fx set - and this allows the user to search against the building name - this will bring back results for any building in the DocData list. 

Search(DocData, BuildingName_SearchInput.Text, "spBuilding")

Ideally we also need to search on the 'Storeys' and 'UPRN' columns from the "Buildings" list so that we can search against the items in the 'DocData' list/gallery but retreive the items that e.g. have a URPN value of '123456' or '5' storeys.

Unsure if this is possible.

[Amik]

Hi @sudosaurus ,

As I understand it, you have two SharePoint Lists ("DocData" and "Buildings"). The Items property of the Gallery is DocData, you want to bring in additional fields from the Buildings list.

Assuming there is a unique identifier which relates both lists, you can use the Add Columns function to pull that data into the Items property of your Gallery, and then run your Search function against those columns.

For example:

Search(
    Search(
        Search(
            AddColumns(
                DocData,
                "_Storeys",
                LookUp(
                    'Buildings',
                    'Unique Identifer in Buildings' = 'Unique identifier in DocData',
                    Storeys //your Storeys field
                ),
                "_UPRN",
                LookUp(
                    'Buildings',
                    'Unique Identifer in Buildings' = 'Unique identifier in DocData',
                    UPRN //your Storeys field
                )
            ),
            BuildingName_SearchInput.Text,
            "spBuilding"
        ),
        TextInput_Storeys.Text,
        "_Storeys"
    ),
    TextInput_UPRN.Text,
    "_UPRN"
)

If you actually just want to Filter on Storeys and UPRN number based on exact search criteria, then there is no need to use nested Search. For example:

Search(
    Filter(
        AddColumns(
            DocData,
            "_Storeys",
            LookUp(
                Buildings,
                'Unique Identifer in Buildings' = 'Unique identifier in DocData',
                Storeys //your Storeys field
            ),
            "_UPRN",
            LookUp(
                Buildings,
                'Unique Identifer in Buildings' = 'Unique identifier in DocData',
                UPRN //your Storeys field'
            )
        ),
        Len(TextInput_storeys.Text) = 0 || _Storeys = TextInput_storeys.Text,
        Len(TextInput_uprn.Text) = 0 || _UPRN = TextInput_uprn.Text
    ),
    BuildingName_SearchInput.Text,
   "spBuilding"
)

Note despite the lack of a warning message, the AddColumns function is not delegable.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[Amik]

And another version of the 2nd example using the With function. To me at least, this version is more readable to help understand what is going on:

With(
    {
        _data: AddColumns(
            DocData,
            "_Storeys",
            LookUp(
                Buildings,
                'Unique Identifer in Buildings' = 'Unique identifier in DocData',
                Storeys //your Storeys field
            ),
            "_UPRN",
            LookUp(
                'Sample Issue Data',
                'Unique Identifer in Buildings' = 'Unique identifier in DocData',
                UPRN //your Storeys field'
            )
        )
    },
    Search(
        Filter(
            _data,
            Len(TextInput_storeys.Text) = 0 || _Storeys = TextInput_storeys.Text,
            Len(TextInput_uprn.Text) = 0 || _UPRN = TextInput_uprn.Text
        ),
        BuildingName_SearchInput.Text,
        "spBuilding"
    )
)

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[sudosaurus]

@Amik - just to clarify.

I only need to search across items in the 'DocData' gallery. However, it would be useful to also search by storey - since that value isn't included in the DocData list but I have it showing as a text value on the items for easy viewing - it would be useful to be able to type in "7" into the search Storeys box and return the buildings in the DocData list that are related to buildings in the Buildings list that store that property value.

[Amik]

@sudosaurus - I now remember your app from a different post I shared a solution for, and I understand what you're trying to do.

To achieve it, we're using the Add Columns function to "transform" the data to bring the related columns in, and then running additional functions like Search and Filter on top of the output. 

[sudosaurus]

@Amik 

So, this one - correct?

With(
    {
        _data: AddColumns(
            DocData,
            "_Storeys",
            LookUp(
                Buildings,
                'Unique Identifer in Buildings' = 'Unique identifier in DocData',
                Storeys //your Storeys field
            ),
            "_UPRN",
            LookUp(
                'Sample Issue Data',
                'Unique Identifer in Buildings' = 'Unique identifier in DocData',
                UPRN //your Storeys field'
            )
        )
    },
    Search(
        Filter(
            _data,
            Len(TextInput_storeys.Text) = 0 || _PriorityIssue = TextInput_storeys.Text,
            Len(TextInput_uprn.Text) = 0 || _TeamIssue = TextInput_uprn.Text
        ),
        BuildingName_SearchInput.Text,
        "spBuilding"
    )
)

[Amik]

@sudosaurus you can use any of them.

The first suggestion uses a nested Search. This means the Gallery will return any partial match of the search string entered in either of the Storeys and UPRN Text input boxes.

The second suggestion returns only data for exact matches based on the entered search string in either of the Storeys and UPRN Text input boxes.

The third suggestion is exactly the same as the second, but written in a different (and in my option) a better way.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[sudosaurus]

@Amik 

Right, I have started configuring this:

I think I've done it correctly so far

I'm looking up the Buildings list, then linking the Building column to the spBuilding column in the DocData list

Then querying the Storeys column in the Buildings list.

And then the same again for UPRN correct?

I'm confused as to what _PriotityIssue and _TeamIssue are used for and what Id put in their place??

With(
    {
        _data: AddColumns(
            DocData,
            "_Storeys",
            LookUp(
                Buildings,
                Building = spBuilding,
                Storeys // Storeys field
            ),
            "_UPRN",
            LookUp(
                'Buildings',
                Building = 'Building',
                UPRN // UPRN field'
            )
        )
    },
    Search(
        Filter(
            _data,
            Len(Storeys_SearchInput_1) = 0 || _PriorityIssue = TextInput_storeys.Text,
            Len(UPRN_SearchInput_1) = 0 || _TeamIssue = TextInput_uprn.Text
        ),
        BuildingName_SearchInput.Text,
        "spBuilding"
    )
)

Search(DocData, BuildingName_SearchInput_1, "spBuilding")

What are these highlighted bits for - PriorityIssue and TeamIssue ??

Thanks! 🙂

[Amik]

Hi @sudosaurus - my fault. Those were random names I forgot to replace with the ones suitable for your scenario. I have updated the earlier post with the correct names.

[sudosaurus]

@Amik - nearly there

This last bit:

        BuildingName_SearchInput.Text,
        "spBuilding"

I get no results when searching against this - but I do for the other two.