Error in IF statement

[Lavanya]

I have tables Users and Work Order and I have a dashboard view(User filter view and Work Order table view)

If the user filters the values then the data in Work Order table will show based on filter value.

I want to filter the rows in Work Order table based on the agency that the user selected from the User filter view and if none is selected then it will return all rows in Work Order table. 

This is my expression

IF(

ISNOTBLANK(Users[Agency]),

SELECT(Work Order[Agency],AND([_THISROW].[Agency]=ANY(Users[Agency]),USEREMAIL()=ANY(Users[User Email]))),

TRUE

)

I got an error "IF function is used incorrectly:the second input (if-result) and third input(else-result) should have the same type"

or I tries to filter based on this IF(ISNOTBLANK(Users[Agency]),[Agency]=ANY(Users[Agency]),TRUE)) but none is displayed while the agency column is blank in filter 

@Steve 

[1minManager]

The issue you have is the second part of your IF() is pulling in TEXT with

SELECT(Work Order[Agency],AND(
   [_THISROW].[Agency]=ANY(Users[Agency]),
   USEREMAIL()=ANY(Users[User Email])
))

Whereas your third statement is specifying TRUE, which Appsheet thinks must be a TRUE/FALSE column type.

Assuming you want it to equal TRUE, and presuming that Work Order[Agency] isn't a TRUE/FALSE type column.  Then try simply putting true in speechmarks should fix it e.g.

IF(
ISNOTBLANK(Users[Agency]),
SELECT(Work Order[Agency],AND(
   [_THISROW].[Agency]=ANY(Users[Agency]),
   USEREMAIL()=ANY(Users[User Email])
   )
),
"TRUE"
)

Simon@1minManager.com

[MultiTech · Accepted solution]

The real problem you're likely running into is the following line:

Specifically that second part; you're basically saying, "go grab any random email from the user table... whichever one you get first is good enough"

So unless the Users table only has 1 record inside it.... this is going to cause problems for anyone other than the first user record in the table. 

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

I highly suggest you check out how to implement the Current User System in your app

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Current-User-Slice-How-to-conform-your-app-aroun...

[franc]

Something like this wouldn't work?

SELECT(Work Order[Agency],AND(
   [_THISROW].[Agency]=ANY(Users[Agency]),
   USEREMAIL()=[_THISROW].[User Email])
))