Filters and Rowlink/Picklist vs. Auto

I have a puzzling problem. I can filter a table in my app, only if the variable filter has DISPLAY set to Auto. When I set DISPLAY to Rowlink & picklist, my filter no longer works except when set to No Selection. Oddly, I can set an initial value of the variable, whether it is displayed as Auto or Rowlink & picklist and for that initial value, it filters fine. If I set the filter to a value from the picklist, including the initial value, it doesn’t work, except, for no selected value. The problem feels like a type conversion problem, but that's just my instinct. It's all documented here with screenshots - https://docs.google.com/document/d/1dhcSv-1Fq1b5GGHvZaX7E7jTFPxkRwWrFLjNEKaqm8Y/edit?usp=sharing

Hey @DennisK,

The google doc you provided is extremely helpful and gives insight on the current design and formula. However to help you with debugging this issue can you provide screenshots and/or settings from the table level. For example in the Shopping_Items table do you have the Where column set up as a Rowlink & picklist similar to my example below or perhaps you have the column set up as Auto. Once I have this information I can assist you further.

Best Regards,

Nancy

2 Likes

Hey @DennisK,

I also reviewed reviewed the filter formula you included in the google doc. I'd recommend changing it to the formula below. Using the IF function you can check if the $SelectedStore variable is empty and if true then display all values from the Shopping_Items table else filter the Shopping_Items list based on the value stored in the $SelectedStore variable.

=if($[SelectedStore]="", Shopping_Items ,FILTER(Shopping_Items, "Shopping_Items[Where] = $[SelectedStore]"))

I hope this helps!

Best Regards,

Nancy

1 Like

Hi Nancy,
Thank you very much for replying. Yes, I am using a picklist very similar to what you illustrated. I added a couple of screen shots at the bottom of my Google Doc in case the embedded images do not come across in this post. I will also try your suggestion regarding the filter, although I would expect it to work the same as the OR logic.
Screen Shot 2020-11-16 at 12.43.46 PM|628x400

Screen Shot 2020-11-16 at 12.43.58 PM

@DennisK

The filter formula I sent works
=if($[SelectedStore]="", Shopping_Items ,FILTER(Shopping_Items, "Shopping_Items[Where] = $[SelectedStore]"))

However I also tested the filter formula you provided and it also works in my sample solution
=Filter(Shopping_Items,"(Shopping_Items[Where]=$[SelectedStore] OR $[SelectedStore]="""")")

In both cases the filter worked when no value was selected as well as selecting other values from the picklist.

It seems our table structure is identical. Perhaps one of our settings are different below is a step by step of the settings in my sample solution.

I have the $SelectedStore variable set as a Rowlink with the following settings. I don't have an initial value set, as I assume the default should be to show all for Shopping_Items until a user selects one from the picklist.

For the column list object I changed the filter to follow your approach.

For the data cells

Best Regards,

Nancy

Hi Nancy,

I think that I figured it out. This works.
=Filter(Shopping_Items,"Shopping_Items[Where]=UPPER($[SelectedStore])")

It looks as though Shopping_Items[Where] is converted to upper case before the compare. Likewise, I think there is a conversion to upper case of a literal before the compare, but not if comparing to a local variable. I will have to explore this more. Note that I have omitted the null selection for simplicity. I don't think that is related to the odd behavior.

When comparing a column to a variable, Honeycode appears to convert the value of a column reference to upper case before a comparison in a Filter. Thus, this doesn't get any hits Shopping_Items[Where]=$[SelectedStore]
but this does
Shopping_Items[Where]=UPPER($[SelectedStore])
The bug applies only when comparing the column to a variable. Literals are automatically converted to upper case. Both of these work.
=Filter(Shopping_Items,"Shopping_Items[Where]=UPPER(""Trader Joe's"")")
=Filter(Shopping_Items,"Shopping_Items[Where]=""Trader Joe's""")

Hey @DennisK,

At the table level if you have the Where field in the Shopping_List table formatted as a Rowlink to the Store table behind the scenes Honeycode is using Primary Keys and Foreign Keys to set the relationships between the two tables.

On the app screens if your using a variable and have it set up as a Rowlink to the Stores table it's referencing IDs behind the scenes not the text info. Of course my assumption is you have the $SelectedStore set up as a Rowlink and not as Auto. If your using Auto then yes you definitely have to account for upper case values.

In my opinion I would use the Rowlink approach for the variable.

Best Regards,

Nancy

1 Like

@DennisK Ok I promise to give it a break after this :slight_smile:

I was looking at your screenshot for the Shopping_Items table and noticed the data in the WHERE column was not highlighted with blue text. When the data is stored using the RowLink the information is in blue text see below:

RowLink

You can also test by manually creating a record directly in the Shopping_Items table and select a store from the dropdown. Verify the text is displaying in blue, if the text for the new record is displaying in blue that means for your other records the rowlink to the store record was not set. Perhaps on the screen you designed to create the shopping_items record the Where column is not setup to use a Rowlink and instead is just storing the plain text.

SelectPicker

1 Like

Hi Nancy, My name is Dennis, BTW.
This is getting interesting. My table was imported as a CSV. That is why nobody is blue. I added a row in Honeycode as you suggested. It indeed was blue. I then deleted the UPPER() conversion from the filter formula. As you would have predicted, the one row that I added by hand, which had a blue value for Where, did indeed work fine and other rows with the same value for Where but not actually blue, did not pass the filter. I'd still consider this a bug because it would seem that in a low code, no code environment, a Black Costco should behave the same as a Blue Costco. Note that $[SelectedStore]="Costco" can evaluate to TRUE, but Shopping_List[Where]=$[SelectedStore] might never TRUE even if they both appear to be Costco. Thank you. Your posts gave me a much better insight to the problem.

1 Like

BTW you might want to review this blog post

One of Honecode's specialist provides instructions to fill in the rowlink after you've imported the data by using the wizard.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.