Expected Text Value Error in Filter Function (SQL Database)

(0) Share

Report

Posted on by Community Power Pla...

Hi! I'm new in Powerapps and trying to write the formula as much as I can.

I would like to do JOIN table as in SQL query, however Powerapps don't work in this way.

There are two tables.
Result I want : Show the Description from Table2 which Table1.Detail1=Table2.Code.

In my Powerapps, the Description will show at a Label "D1Description" in the Gallery(which data source is Table1).

I do Filter in Label "D1Description" based on another Lable "D1Code"(which is Table1.Detail1)
AND Table2.Detail is "D1".

Error Filter - Text value

But I get this ERROR MESSAGE : "Expected Text Value" always, even I searched and tried other function.

SQL Database examples:

Table1
Column Name : Item Detail1 Detail2

Value Row1 : 001 A Aa1

Value Row2 : 001 B Bb1

Table2
Column Name : Detail Code Description

Value Row1 : D1 A Group 1

Value Row2 : D1 B Group 2
Value Row3 : D2 Aa1 Group 1 Alpha

Value Row4 : D2 Bb1 Group 2 Beta

SQL Query
SELECT Description

FROM Table1 X
LEFT JOIN Table2 Y on Y.Code=X.Detail1 and Y.Detail = 'D1'

Appreciate for your advise and guidances.

Thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like (1)

Report

In your screenshot the Filter() is in a Text property. Filter() returns a table of records and that is why you are getting the error message. You can use a Lookup() to retrieve a signle record and display the Description information.

Example:

Lookup('Table2', Code=D1Code.Text And Detail = "D1").Description

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thank you so much Jeff!
I have better picture on Filter and LookUp now!

I searched Join table alternative in internet, I saw some said AddColumns(Filter(...)), may I know is this work the same?
Or as you mentioned, it is same like Filter(without AddColumns function) returns a table with Columns and filtered records?

Besides this, if the record is more than 500, is the formula LookUp(in your answer above) suggested?

Thank you so much!

Was this reply helpful? Yes No
v-yutliu-msft on at

Like (0)

Report

Hi @Anonymous ,

1)AddColumns function is used to adds a column to a table.

It can bu used to reshape the table structure.

I think use this can achieve the function of "join".

Here's a doc about this function for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping

2)If your record is from 500~2000, this will not effect your formula.

Just do not forget to change the non-delegation limit to 2000.

If your record is larger than 2000, then you need to modify your formula to delegable function.

Here's a doc about delegation for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Dear Phoebe,

Thank you very much for your reply!
Sorry that I still don't really get it even though I read the docs.microsoft pages.

1) AddColumn
Based on my example above, create a Gallery(Data Source Table 2) to show multiple columns(including a column from Table 1), does AddColumn(Filter(..)) mean it will create new columns for the Results?

2) Delegation
Everytime I write formula Filter(...) or/and contains "=" then Delegation Warning will happen.
However, the database contain of data more than 2000 rows in SQL Server.
I really confuse on the references in docs.microsoft webpages and confuse on how to modify the formula to delegable.
Is there any precise explaination on "modify the formula to delegable function"?

Pardon me if I asked silly questions.
Thanks alot!

Was this reply helpful? Yes No