PowerApps Gallery items based on If=>1st search with two columns to search=> Or 2nd search with 1 column to search that's a lookup column

[flebby]

Dataverse Table (Pastor Info, ChurchInfo).  I have a search box (named SearchInput). I want to filter the PastorInfo (PastorGallery - based on items returned from SearchInput or return the PastorInfo table as the list of items for the Gallery) - this portion works. Still, I want to add a part to search the ApptChurchName which is a part of the PastorInfo table and a lookup field).  This code works without the additional search:

If(IsBlank(SearchInput.Text),PastorInfo,

    Search(PastorInfo,SearchInput.Text,"crc5c_pastorfullname","crc5c_firstname","crc5c_lastname")

    )

But I want to add the option as a possible Or statement if none of those work:

        (Search (PastorInfo,SearchInput.Text,"crc5c_HomeChurchID2"))

The crc5c_HomeChurchID2 is the lookup column name in the PastorInfo table

Help would be greatly appreciated 🙂

[Amik]

@flebby - a Dataverse LookUp column returns a record and the Search function requires searchable text. You can leverage the AddColumns function to create a "virtual" column which displays only the record value you want to return as text.

Apply the below to the Items property of your Gallery. Note that the AddColumns function is not delegable.

With(
    {
        _transformed_data: AddColumns(
            PastorInfo,
            "flebby_related_column",
            'Your HomeChurchID2 Field'.'The name of column you want to return from the ChurchInfo table'
        )
    },
  Search(
        _transformed_data,
        SearchInput.Text,
        "crc5c_pastorfullname",
        "crc5c_firstname",
        "crc5c_lastname",
        "flebby_related_column"
    )
)

[flebby · Accepted solution]

The code worked for the search:

With(

    {

        _transformed_data: AddColumns(

            PastorInfo,

            "crc5c_churchname",

            ApptChurchID2.ChurchName

            //'Your HomeChurchID2 Field'.'The name of column you want to return from the ChurchInfo table'

        )

    },

    Search(

        _transformed_data,SearchInput.Text,

        "crc5c_churchname",

        "crc5c_firstname",

        "crc5c_lastname",

        "crc5c_pastorfullname"

    )

)

Now I have an error for the PastorForm, which is connected to the PastorGallery.Selected, and was connected before the updated search code that I placed in the Items for the PastorGallery.

Previously the PastorForm data source was and is the same data source as the gallery

[Amik]

@flebby - This occurs when using the AddColumns or GroupBy function because these functions modify the Items returned into the Gallery control.

This error is expected because the schema used in the DataSource property in the Edit Form control, and the schema returned in the Items property in the Gallery must match. 

To accommodate for this, it is best to use a LookUp function in the Item property of the Edit Form. For Dataverse, I would recommend using the Logical Name of the Unique Identifier column to return the correct record:

LookUp(
    'Your Data',
    'Unique Identifer Column Logical Name' = Gallery1.Selected.'Unique Identifer Column Logical Name'
)

To find the Logical Name of the Unique Identifier/GUID column, navigate to the columns area of your Dataverse table:

On a separate note, try to avoid naming the virtual column you created with the actual name of a field (crc5c_churchname) as this will cause disambiguity.

[flebby]

I added the correct code encase someone has the same issue:

With(

    {

        _transformed_data: AddColumns(

            PastorInfo,

            "returnedChurchname",

            ApptChurchID2.ChurchName

            //'Your HomeChurchID2 Field'.'The name of column you want to return from the ChurchInfo table'

        )

    },

    Search(

        _transformed_data,SearchInput.Text,

        "returnedChurchname",

        "crc5c_firstname",

        "crc5c_lastname",

        "crc5c_pastorfullname"

    )

)

Also,

I code used on the PastorInfo form, Item property:

LookUp(PastorInfo,"crc5c_pastorfullname"=PastorGallery.Selected.PastorFullName)

But this is saying no data found, but when select PastorInfo, the column name and data is showing, and it does match the PastorGallery.Selected.PastorFullName

[Amik]

@flebby - if you can be sure that using PastorFullName will return a unique record, then good, but I would recommend using the unique identifier as you can guarantee uniqueness.

Also, as courtesy to me and to help others, please remember to accept the solution so that other users with the same question can quickly find the answer.

[flebby]

My question is why is it saying this, but the PastorFullName is showing?  When the item is selected on the PastorGallery, it is not updating to the Pastor's record on the form:

[flebby]

When I used the logical name, it did not return any records.  I had to use the PastorFullName and received the records:

LookUp(PastorInfo,PastorGallery.Selected.PastorFullName = PastorFullName)

[Amik]

@flebby the code you posted is not the code I gave you.

1. For some reason you've decided to wrap the field in double quotation marks
2. "crc5c_pastorfullname" does not look the unique identifier field

The unique identifier column is system column and it is automatically generated when you create a table. 

Once again, please navigate to the Columns area of your Table, 

Filter the Data Type column to Unique Identifier:

Select Edit

Take a note of the Logical Name. This is your unique identifier column.

Then use the below. Do not randomly wrap the field with double quotation marks.

LookUp(
    'Your Data',
    'Unique Identifer Column Logical Name' = Gallery1.Selected.'Unique Identifer Column Logical Name'
)

[flebby]

When I did exactly what you directed, I received an error when I did not have the double quotation marks.