Answered

Search says "Wrong column type expects text type"

(0) Share

Report

Posted on by candide

I try to search a gallery via a textbox

but there rises an error for the gallery items property

Search(tblZSM,Scr12txtSearchSymptome.Value,"new_lkpSymptom")

column lkpSymptom is a LookUp column in a junction table tblZSM

any ideas?

thx

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Sort by

Verified answer

Ami K 15,689 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
@candide -

Try the below:

Search( AddColumns( tblZSM, "_related_column", lkpSymptom.'the name of the field you want to return from the related table' ), Scr12txtSearchSymptome.Value, "_related_column" )

The Search function expects a string. It is not possible to use the Search function on complex field types (such as Choice, Lookup, People).

Instead we use the AddColumns function to bring a "Virtual" column into our table. An equivalent of this is using a Vlookup in Excel - you're bringing in a new column with data which does not actually exist at source.

We then run a Search against this column to return the result you need.

Notes:

The "_related_column" is an entirely made up name, and it can be anything you want.

Using the AddColumns function this will modify your Gallery schema, and could impact any variables you might be running against the Gallery.

Both the Search and AddColumns functions are not delegable.

Further reading: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-table-shaping

Was this reply helpful? Yes No
candide on at

Like
a
(0)

Report
Copy link

Link copied!
@Amik
works as expected😁 big thanks

Now I additionally need to filter on the combobox Scr12ComboboxFilterKapitel shown in screenshot above
Search( Filter( tblZSM, Len(Scr12ComboboxFilterKapitel.Selected.Kapitelname) = 0 || lkpKapitel.Kapitelname = Scr12ComboboxFilterKapitel.Selected.Kapitelname ), . . .

Here I have some code which worked in another screen but I have no idea how to add it in your solution.

Was this reply helpful? Yes No
Verified answer

Ami K 15,689 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@candide - please remember to accept the solution to the first question.

Regarding the second question, to combine this you can either use:

Search( AddColumns( Filter( tblZSM, Len(Scr12ComboboxFilterKapitel.Selected.Kapitelname) = 0 || lkpKapitel.Kapitelname = Scr12ComboboxFilterKapitel.Selected.Kapitelname ), "_related_column", kpSymptom.'the name of the field you want to return from the related table' ), Scr12txtSearchSymptome.Value, "_related_column" )

Or:

With( { _prefiltered_data: Filter( tblZSM, Len(Scr12ComboboxFilterKapitel.Selected.Kapitelname) = 0 || lkpKapitel.Kapitelname = Scr12ComboboxFilterKapitel.Selected.Kapitelname ) }, Search( AddColumns( _prefiltered_data, "_related_column", kpSymptom.'the name of the field you want to return from the related table' ), Scr12txtSearchSymptome.Value, "_related_column" ) )

Was this reply helpful? Yes No
candide on at

Like
a
(1)

Report
Copy link

Link copied!
@Amik
your 1st proposal is now my solution😁
Thanks a lot. Slight changes did it
Sort( Search( AddColumns( Filter( tblZSM, Len(Scr12ComboboxFilterKapitel.Selected.Kapitelname) = 0 || lkpSymptom.lkpKapitel.Kapitelname = Scr12ComboboxFilterKapitel.Selected.Kapitelname ), "_related_column", lkpSymptom.Bezeichnung ), Scr12txtSearchSymptome.Value, "_related_column" ), lkpSymptom.Bezeichnung )

Was this reply helpful? Yes No