Inconsistent results between LINKTOFILTEREDVIEW() and slice filters

[interact-yugal]

I’m running into unexpected behaviour with LINKTOFILTEREDVIEW().

Here’s my expression -

IF(
CONTEXT(“View”) = “0 - Inventory Movements Card”,
LINKTOFILTEREDVIEW(
“0 - Inventory Movements Card”,
OR(
AND(
OR(ISBLANK([Buy Rate]), [Buy Rate] = 0),
IN([Movement Type], { “Opening Balance”, “Receipt”, “Purchases Return”})
),
AND(
OR(ISBLANK([Sell Rate]), [Sell Rate] = 0),
IN([Movement Type], { “Delivery”, “Sales Return”})
)
)
),
LINKTOFILTEREDVIEW(
“0 - Inventory Movements Table”,
OR(
AND(
OR(ISBLANK([Buy Rate]), [Buy Rate] = 0),
IN([Movement Type], { “Opening Balance”, “Receipt”, “Purchases Return”})
),
AND(
OR(ISBLANK([Sell Rate]), [Sell Rate] = 0),
IN([Movement Type], { “Delivery”, “Sales Return”})
)
)
)
)

Issue -

• [Sell Rate] = 0 is working correctly.
• ISBLANK([Sell Rate]) is not being applied inside LINKTOFILTEREDVIEW().

To work around this, I created a slice with the same filter condition -
OR(
AND(
OR(ISBLANK([Buy Rate]), [Buy Rate] = 0),
IN([Movement Type], { “Opening Balance”, “Receipt”, “Purchases Return”})
),
AND(
OR(ISBLANK([Sell Rate]), [Sell Rate] = 0),
IN([Movement Type], { “Delivery”, “Sales Return”})
)
)

The slice handles OR(ISBLANK([Sell Rate]), [Sell Rate] = 0) perfectly — but LINKTOFILTEREDVIEW() does not.

Is this a known limitation/bug of LINKTOFILTEREDVIEW()?

[Steve]

@interact-yugal

Is this a known limitation/bug of LINKTOFILTEREDVIEW()?

It is not a limitation of LINKTOFILTEREDVIEW(). You’re doing something wrong.

[interact-yugal]

@Steve

It is not a limitation of LINKTOFILTEREDVIEW(). You’re doing something wrong.

Thanks for the quick response, Steve.

I may be misunderstanding how LINKTOFILTEREDVIEW() evaluates the filter condition — could you point out what I should be adjusting in my expression?

OR(ISBLANK([Sell Rate]), [Sell Rate] = 0) 

• [Sell Rate] = 0 works fine.

• But ISBLANK([Sell Rate]) doesn’t return the expected rows when used inside LINKTOFILTEREDVIEW().

• The same condition works correctly when applied in a slice.

[Suvrutt_Gurjar]

I quickly tested on a test app with just the expression of

LINKTOFILTEREDVIEW(“Products”, OR([Sell_Rate]=0, ISBLANK([Sell_Rate])))

and it is working. As Steve said, you may want to look at other parts of the expression.

Also please test the LINKTOFILTEREDVIEW() expression just with OR([Sell_Rate]=0, ISBLANK([Sell_Rate]) to ensure it works or otherwise. Thereafter you could add more conditions to know where it is failing.

As the screenshot shows, the expression filters out the records with sell rate of 0 or blank sell rate, as expected.

[interact-yugal]

Apologies for the confusion in my earlier post!

After re-checking, I realized that ISBLANK() is actually working fine inside LINKTOFILTEREDVIEW().

Slice filter (works as expected)

OR(
  AND(
    OR(ISBLANK([Buy Rate]), [Buy Rate] = 0),
    IN([Movement Type], { "Opening Balance", "Receipt", "Purchases Return"})
  ),
  AND(
    OR(ISBLANK([Sell Rate]), [Sell Rate] = 0),
    IN([Movement Type], { "Delivery", "Sales Return"})
  )
)

Rows are correctly returned when [Buy Rate] = 0 or [Sell Rate] = 0 (including blanks).

LINKTOFILTEREDVIEW() (unexpected behavior)

IF(
  CONTEXT("View") = "0 - Default Inventory Movements Card",
  LINKTOFILTEREDVIEW(
    "0 - Default Inventory Movements Card",
    OR(
      AND(
        OR(ISBLANK([Buy Rate]), [Buy Rate] = 0),
        IN([Movement Type], { "Opening Balance", "Receipt", "Purchases Return"})
      ),
      AND(
        OR(ISBLANK([Sell Rate]), [Sell Rate] = 0),
        IN([Movement Type], { "Delivery", "Sales Return"})
      )
    )
  ),
  LINKTOFILTEREDVIEW(
    "0 - Default Inventory Movements Table",
    OR(
      AND(
        OR(ISBLANK([Buy Rate]), [Buy Rate] = 0),
        IN([Movement Type], { "Opening Balance", "Receipt", "Purchases Return"})
      ),
      AND(
        OR(ISBLANK([Sell Rate]), [Sell Rate] = 0),
        IN([Movement Type], { "Delivery", "Sales Return"})
      )
    )
  )
)

What happens

• ISBLANK([Column]) → works fine in both slice and LINKTOFILTEREDVIEW().

• [Column] = 0 → works in the slice, but does not return rows in LINKTOFILTEREDVIEW().

Just wanted to clarify this so the thread stays accurate.

[Suvrutt_Gurjar]

As requested you may want to check with the following expression first, something like

LINKTOFILTEREDVIEW( “0 - Default Inventory Movements Card”,

                      OR
                      (

                      OR(ISBLANK([Buy Rate]), [Buy Rate] = 0),
                      OR(ISBLANK([Sell Rate]), [Sell Rate] = 0)
                       )
                   )

You may even test even smaller expressions with just [Buy Rate] or [Sell Rate] at a time.

[Steve]

@interact-yugal

After re-checking, I realized that ISBLANK() is actually working fine inside LINKTOFILTEREDVIEW().

I’m done here.

[interact-yugal]

Thanks for sharing your insights on this filtering inconsistency issue.

I found that explicitly converting the [Buy Rate] and [Sell Rate] columns to numbers before comparing to zero solves the problem for me.

NUMBER([Buy Rate]) = 0

NUMBER([Sell Rate]) = 0

Here’s the expression I used -

IF(
CONTEXT(“View”) = “0 - Default Inventory Movements Card”,
LINKTOFILTEREDVIEW(
“0 - Default Inventory Movements Card”,
OR(
AND(
OR(ISBLANK([Buy Rate]), NUMBER([Buy Rate]) = 0),
IN([Movement Type], { “Opening Balance”, “Receipt”, “Purchases Return”})
),
AND(
OR(ISBLANK([Sell Rate]), NUMBER([Sell Rate]) = 0),
IN([Movement Type], { “Delivery”, “Sales Return”})
)
)
),
LINKTOFILTEREDVIEW(
“0 - Default Inventory Movements Table”,
OR(
AND(
OR(ISBLANK([Buy Rate]), NUMBER([Buy Rate]) = 0),
IN([Movement Type], { “Opening Balance”, “Receipt”, “Purchases Return”})
),
AND(
OR(ISBLANK([Sell Rate]), NUMBER([Sell Rate]) = 0),
IN([Movement Type], { “Delivery”, “Sales Return”})
)

)
)

Hope this helps others facing similar issues!

[Suvrutt_Gurjar]

Great, thank you for the update.
Since you were comparing the column [Sell rate] with 0, it was a natural assumption that the column is of number type.

Anyway, nice to know that you got it solved.

[interact-yugal]

Both [Buy Rate] and [Sell Rate] columns are set to the Price data type. While I expected Price (with a Number base type) to behave like a numeric value, LINKTOFILTEREDVIEW() didn’t seem to consistently recognize it that way — unlike slices, which handled the filter correctly.

To resolve this, I had to explicitly cast the values as numbers using -

NUMBER([Buy Rate]) = 0 NUMBER([Sell Rate]) = 0

This ensured the filtered view returned the correct rows.

[Suvrutt_Gurjar]

Thank you for this important update. It makes sense. I agree that I would also expect price column to work with comparison to 0 value.

So I tested with [Sell_Rate] being designated as Price type column and the LINKTOFILTEREDVIEW() still works for both blank and 0 price as the following GIF shows.

Anyway, at times we may need to live with workarounds the way you did. As long as it is working for you with a modification that is not a huge impact on any other parameter such as say sync time , I think it is okay to have a practical solution like yours that works for a requirement.

So good job on finding that practical workaround.

[interact-yugal]

There may be an interaction with how Google Sheets represents the data points, even though they are stored as numbers in native Tables of Google Sheets.

@Suvrutt_Gurjar, I really appreciate the time you took to look into this.

[Suvrutt_Gurjar]

You are welcome. If you are using Google Sheets as a backend, please take a look at Steve’s recommendation in formatting the Google sheet in the post below, just in case you have not come across this post earlier.

Spreadsheet formatting tips

The format of a cell in a spreadsheet used as a data source for an app can affect how AppSheet uses the cell’s content. It is therefore important to take care to choose cell formatting carefully. Automatic format is okay for most data, but can cause problems in some circumstances. The safest cell format you can use for everything is plain text. The downside to formatting the spreadsheet as plain text is the spreadsheet becomes less legible to its human users. Here are my suggestions: Forma…

[interact-yugal]

I tested various Google Sheets formatting options based on Spreadsheet formatting tips to debug inconsistent filtering with LINKTOFILTEREDVIEW() on Price columns -

1. Default number format
   Path - Format > Number > Number
   Output example - 2.80

2. Custom currency format
   Path - Format > Number > Custom currency > ₹
   Output example: ₹2.80

3. Number format via edit column type
   Path - Right-click column > Edit column type > Number > Number
   Output example: 2.8

Result - Despite proper numeric formats, only explicit use of NUMBER([Buy Rate]) = 0 or NUMBER([Sell Rate]) = 0 worked reliably in LINKTOFILTEREDVIEW().

This confirms that Google Sheets formatting alone isn’t sufficient; explicit numeric conversion in AppSheet expressions is required.

[Suvrutt_Gurjar]

Great. Nice testing and analysis.

You may also want to test more why in your specific case you need to wrap price column with number whereas my testing showed that price column comparison with 0 works in LINKTOFILTEREDVIEW() expression.

[Steve]

What are the AppSheet column types of the Buy Rate and Sell Rate columns?

[interact-yugal]

It’s Price with 2 decimal places

[Steve]

I know you have a workaround. I wonder, though, if [Buy Rate] = 0.0 would work as well. Buy Rate is a Price, which is a special type of Decimal. 0.0 is a Decimal value. 0 is a Number (integer) value.

[interact-yugal]

I tested your theory about the data type mismatch between Price (Decimal) and integer comparison.

I tried both -

• [Buy Rate] = 0.0

• [Buy Rate] = 0.00

Unfortunately, both failed to work in LINKTOFILTEREDVIEW(), just like the original [Buy Rate] = 0.

This confirms that the issue isn’t simply about integer vs. decimal type matching, but rather a deeper inconsistency in how LINKTOFILTEREDVIEW() specifically processes Price columns compared to slices.

The NUMBER() parsing workaround remains the only reliable solution -

OR(ISBLANK([Buy Rate]), NUMBER([Buy Rate]) = 0)

Further, I discovered an interesting behavior—when users actually enter 0.0 as the value in the Price fields, the NUMBER() parsing workaround still works perfectly and its storing the 0.0 as 0 in the backend ie Google Sheets.

It seems there’s a genuine behavioral difference between slice filters and LINKTOFILTEREDVIEW() when handling Price data types, regardless of decimal precision in the comparison value.