I’m having a problem where I have a slice that’s returning multiple records when you click on View Data and returning the correct records as TRUE when I test the underlying formula but only one record is appearing in a Table view using the slice. Any thoughts or is it a bug?
Are the values in the table’s key column all different?
Yes they are. It really looks like a bug to me. For example, this formula works and returns all records;
IF(ISBLANK(ANY(SELECT(Dashboard Filter[Status],[User Email]=USEREMAIL()))),TRUE,CONTAINS(“Quote, Pending, Booked, Lost, On Hold”,[Status]))
but this formula only returns one;
IF(ISBLANK(ANY(SELECT(Dashboard Filter[Status],[User Email]=USEREMAIL()))),TRUE,CONTAINS(ANY(SELECT(Dashboard Filter[Status],[User Email]=USEREMAIL())),[Status]))
where ANY(SELECT(Dashboard Filter[Status],[User Email]=USEREMAIL())) is a text based EnumList field that equals “Quote, Pending, Booked, Lost, On Hold”
Both formulas show the correct number of records when you ‘View Data’ in the slice and both produce the correct results when you test the formula but the second formula only produces one record in a table view based on the slice.
SELECT(Dashboard Filter[Status], ...)
produces a list of Status column values from rows of the Dashboard Filter table that match the selection criteria (
produces the first item in the list produced by the SELECT() expression. The result is not a list unless the first Status column value is itself a list.
Would the Status column value of a single row of the Dashboard Filter slice contain the entire literal text,
Quote, Pending, Booked, Lost, On Hold? Or would it contain just one of the values in that list?
Sorry I should have mentioned, the slice is pulling records from an underlying table called ‘Jobs’. The [Status] field at the end of the expression relates to the ‘Jobs’ table which has many records that satisfy the criteria. There is only one record being sought from the ‘Dashboard Filter’ table, hence the ANY(), which is an EnumList called [Status] which has the value of one or more selected text options, in this case, “Quote, Pending, Booked, Lost, On Hold” that is recorded against the user’s e-mail (so that each user has their own filter). So basically, I’m finding ‘Jobs’ records that have a [Status] contained in the EnumList string in ‘Dashboard Filter’. If I hard code that text string it works in the Table View. The formula also works when I ‘View Data’ in the slice but only shows one record in the Table View. That’s why I think it may be a bug. There’s no filters on the Table View that could change what the slice is producing and as I said, hard coding the string makes the Table View retrieve all records and yet ‘View Data’ in the slice produces the correct list of records from the ‘Jobs’ table in both cases.
It could indeed be a bug, then. I encourage you to engage firstname.lastname@example.org to report it and hopefully get a fix.
Just a quick note to say this bug has now been corrected by AppSheet and the fix is being rolled out progressively to accounts.