Set columns - Clear value action doesn't work the same way everywhere

Bugs
#1

It’s a bit complicated. I am creating an app that works similar to classified ads - one user posts an ad, another responds to it. When the second user searches for an ad to respond to, he or she selects certain criteria, such as City, Date, and Category.

“City Selection” and “Date Selection” are user-specific columns in the “Posts” sheet. “Categories” is another sheet (displayed as a checkbox list) with a user-specific “Check value” column.

When a user starts a new search, I need to clear out his or her previous selections. To do so, I made a custom action on the “Search” button, that has three steps inside:

  • Set columns (sheet “Posts”) - Clear values (“City Selection” and “Date Selection”)
  • Set columns (sheet “Categories”) - Clear values (“Check value”)
  • Link to screen with search parameters

In order to be able to set columns, I had to create relations between the Home sheet and the “Posts” and “Categories” sheets. To do so, I created a regular column “True” in the Home sheet (with just one “True” in the first row) and single-value columns “Dummy True” in the other two sheets, that take the value from the “True” column. Then I created two relations in the Home sheet between “True” and “Dummy True”.

Now, the question.

It works for one sheet (“Posts”), but it doesn’t work for the other (“Categories”).
In other words, “City Selection” and “Date Selection” are cleared, but “Check value” is not.
UPD: It actually clears only the first row of the sheet.

Why is that?

The only difference I see is that “City Selection” and “Date Selection” are text columns, and “Check value” is boolean. However, it should not be the reason (and also, converting it to text doesn’t help).

My app’s URL: Glide · Create Apps from Google Sheets

1 Like
#2

I was debating whether to respond, given that I’m pretty new to GLIDE, but your update bolstered my suspicion. I wonder if the the cascading events aren’t getting enough time to complete.

Observationally, I’m guessing that the ACTION is a macro in GLIDE’s engine. I’ve seen it in many other applications that macros get interrupted and don’t complete correctly due to the MACRO’s caboose getting ahead of its engine.

Since you’re trying to clear the entire column, you might try to use the ACTION to clear just one cell at the top of the column (ie CITY:[row]), then clear the rest formulaically within the sheet (be it Google or Glide sheet). IT would likely mean adding a helper column for each of the City Selection and Date Selection columns. The helper column gets an IF/THEN

IF CITY:cell 2 is empty then “”
else "CITY HELP:[row]

That’s not actual syntax, rather just an explanation. Your syntax will vary depending on where you do this (Google/Glide).

1 Like
#3

Thank you for the quick response! Sounds like a valid idea, I will try it out.

#4

Giving it a second thought, I don’t understand, how it should work.
(BTW, my problem is with “Check value”, “City” and “Date” work OK).

If I get it right, you idea was to create an additional “If-Then” column, that would have all row values same as the first one in the “Check value”. So if the first row value is cleared, all other are cleared, too. (Sounds more like a “Single value” column type to me.)

But how should it work with the next search, when a user checks several options - and this column will still have the value of the first row replicated for all other rows? Or did I get it wrong?

Also, there’s no option to do it Google Sheet, because the columns in question are user-specific.

#5

Okay, departing from my previous response…

With a couple of helper columns, you can have the functionality, but there’s one caveat. Here’s a link to an example Sheet.

Screen Shot 2021-02-02 at 12.31.31 PM

I’m assuming you’re using a CHOICE component for users to select the city. That selection will write to the cs_select column. The cs_logic column checks for TRUE/FALSE in B2 and stuffs or clears the value from D to C column. Column B is the output column. That’s the one you show as your user’s choice.

The caveat is that even after clearing, the CHOICE tool will show the the last selection. You can show users the output of the Column B in a separate text component. It’s not ideal, but it may solve your problem long enough to continue your app development.

If you can live with that, then congratulations! You’re on your way to a Degree from the Rube Goldberg School of Spreadsheet Design (I have a Masters and am working on my PhD).

EDIT: You could do this with just two columns, but I like the keeping the RESULT separate from the logic in case I want to change things. Sometimes a recursive formula doesn’t work with just two columns. ALso, the RESULT column is available for a further filtering or logic (like: you can make that pull cities from cs_logic and order them, or append some other text, etc).

1 Like
#6

Thank you! It’s not quite clear though, how the B2 will change to “TRUE” back again when the new search criteria are entered.

However, it still does not explain, why “Clear values” worked on one sheet, and didn’t work on the other. I tried changing the sequence of actions to check your hypothesis, that only the first action is performed - it didn’t help.

#7

It’s not quite clear though, how the B2 will change to “TRUE” back again when the new search criteria are entered.

It would require an external mechanism - either implicit explicit button press from user (“Make new choices”) or driven by some other condition/event in the workflow. It’s not going to happen just from the CHOICE component. Admittedly, The implicit explicit button push is a clunky solution.

Are you doing this in a Google Sheet or a Glide Sheet. Clear Values might work more reliably in a Glide Sheet.

I think I’ve reached the end of my ability to help. Hoping someone else will jump in.

1 Like
#8

It seems like in the Categories sheet you want to clear an entire column, is that correct? It won’t work with the way set column is set up now, it only clears the first match.

#10

Ah, that makes sense, thank you! So it’s not a bug after all, more like a feature request. What could be a possible workaround?

#11

Do you have a set number of categories and how many is it?

#12

Currently, I have 8 top-level categories and 18 second-level categories (like “Outdoor - Biking”). Right now they are all in one list, making it 26 total. And this is not supposed to be a limited number forever, I may add a few more.
But, since it’s just a prototype, I am thinking of leaving the top-level only for simplicity. Not sure yet.

#13

Since we can only set one row in one column at a time, if you have too many and it’s not fixed then it’s hard to scale up. As of now we would have to do multiple set columns to achieve what you want.

#14

Do you mean that I can specify a row in “Set Columns”? How can I do that?
There’s a “Row” field there, but I select a relation column in it so that I have a list of columns from another sheet there.

image
image519×728 35.8 KB

#15

It will act on the current row.
If setting it through a relation, then it only works with a single relation, and you’ll be setting the value in the row on the other end that forms the relation.

1 Like
#16

You would have to set up a lot of relations for that, unfortunately. You can use the single value column to target a specific row.

#17

@Darren_Murphy @ThinhDinh I understand now, thank you for the explanation! Will be looking for a workaround.

It’s looks like there’s no way to create a multiple choice select in Glide now :frowning:

1 Like
Choice Component - multiple selections
#18
#19

Is this solution applicable for a checklist? As far as I understand, you can’t write the checkbox state to an if-else column, neither can you show the state of a checkbox from one column, and write it to another one.

#20

No, not really. It requires firing an action to set the parentid into that row. I don’t think you can do that with a checklist layout when the checkbox is checked. That’s why I used emojis (any image would work too) to indicate if the item is checked or not. It probably won’t fit everyone’s use case but I thought I’d share as an alternative to trying to clear an entire column. I think the only true way to do that would require a script or some sort of looping mechanism in Zapier or Integromat. None of those other options would work is the checkbox is filling a user specific column.

3 Likes