Create individual buttons to return individual filters from a range in a Google Sheet

[andos-avlog]

Hi, I only seem to be able to find half-solutions to my questions because everything seems to be regarding AirTable.

I am trying to create an app which has a buttons for each individual day of the week. The source is a Google Sheet (Timetable).

The buttons are labelled so the user just clicks the day of the week to retrieve their required information instantly.

Day1=Mon; Day2=Tue…etc…

For this example, I am going explain my request using Wednesday as the subject.

This app is a timetable where if the user wanted to know what aircraft movements were operating on a specific day. By clicking the relevant day, ( in this case Wednesday!) The DVL will return a list of all the aircraft in the Google Sheet operating on a Wednesday.

What I am trying to do is this:

If I click on the “We” button (short for Wednesday) first of all the button would change colour to highlight and signify to the user that the day Wednesday has been chosen. (See image below:)

Now, because in aviation, Wednesday is the 3rd day of the week, the next thing I wish to happen is, within the Google Sheet I would like each "Day"button to be associated to their relevant column within the GSheet.

Eg. “We” button to be associated to “We” column (column J) in the GSheet; “Mo” button to be associated to the “Mo” column (column H) and so on. (See image below:)

The idea of this is so that each “Day” will return it’s own individual filter to the DVL.

If the user wished to select a different day within the app, he would tap on a different “Day” button. Let’s say “Mo” (for Monday). Upon clicking this button, the previously selected and highlighted Wednesday button 's colour would no longer be highlighted and Monday(Mo ) button would be highlighted instead.

There is also a “Clr” (Clear) button which I would like to use to deselect and remove the highlighting from all of the “Day” buttons and make the DVL once again invisible.

I have already created the buttons but I do not know where to start because I don’t know whether I need an individual set of blocks for each day for each filter, or could all of this be executed more succinctly.

Here are my blocks so far, I have created the “When button click” block for each individual day of the week,

Here are my blocks:

My Clear Button blocks (TimClrBtn) are shown below:

Any help would be greatly appreciated.

Andos.

[manyone]

this short demo illustrates the functionality you desire: enter day of week (1=monday…7=sunday) and the resulting list is filtered from the original list, showing all rows that have the corresponding day of week is populated.

• at the start, the list shows the full list
  

  

• then enter a one-digit day of week (eg. 3) at top and the displayed list should change to show only those rows that have wednesday (dayofweek=3) in them

the number being displayed on the right (eg. 356, 367, etc) is a computed column, which i called Days, which is a concatenation of all the indicators for the whole week, in each row.
see description below

here is a picture of the google sheet:

here’s the link to the google sheet. make a copy and upload to your google drive.

flights

in order to do make this work, you have to add a new column, Days, which has a formula that concatenates all the days of the week in one row.
=concatenate(H2:N2)

then later during selection, you use the (does this text contain the letter?) block when loading the data viewer list in order to determine if the itme is to be made visible or not. see the whole program below:

note that i’m just using a plain and simple data viewer list to show this demo, but you can design your own custom design to show all the fields you need.

also i’m using a poor man’s way to input data - input text! you can surely use different buttons for different days of the week - just make sure each of them deposits the value of the day of the week to one and the same variable, say day_of_week. (for the clear button, set day_of_week to 0).

in the program above, this day_of_week variable would replace the “text input 2 . text” block.
i hope this helps.

[andos-avlog]

Hi manyone, that’s a really good and logically simple way of looking at a solution. Thank you sir!

@manyone

in the program above, this day_of_week variable would replace the “text input 2 . text” block.

I, however, am not really understanding the last part of your reply. Please forgive me, my mental disability makes a little difficult for me grasp things.

Many thanks,

Andos.

Andos.

[manyone]

in your case you wouldn’t need a input-text field because you are not getting it as a data entry field.
but theocode block needs a day of week - in effect, the block is saying “does this contatenation of weekdays for this row contain wednesday (for example)”? - right now it’s saying “does this concatenation include input-text value?” - you will change it to “does this concatenation include day_of_week?” where does day_of_week come from ? it’s a variable populated with 1 by the MOnday button, the TUesday button sets it to 2, the WEdnesday buttin sets it to 3 (ie. set day_of_week to 3), etc.

[andos-avlog]

Thanks @manyone, I “think” I understand.

Are you saying that I need to replace these variables (below)

with this one?

or do I use the other variables also?

Many thanks,

Andos

[manyone]

first day of week is NOT a list - it’s just a simple, plain variable.
you will not need a separate list per weekday because when you select a weekday (by pressing a button), you will still be pointing your data viewer list to the original list - but we are using a trick that i learned from @Muneer - go ahead and populate the data viewer list with the full google sheet - but we loop over it and when we find a record that is selected (ie. the column Days contains the value of day_of_week), then make the row visible (see code: set visible to true),else make the item invisible (set visible to false).

[andos-avlog]

LOL, I had just this moment figured out that the rows H:N in the GSheet are not required to display in the DVL only the DAYS column is. So everything points to that column.

By the way, what is meant “looping”? I keep seeing the term used but don’t actually know what it is or looks like. .

Andos.

[andos-avlog]

Hi @manyone, are my Monday blocks now correct? see below:

I ask because the “Mo” button is not filtering and displaying out the number “1’s” from the “DAYS” column

Andos.

[tatiang]

Your second “if” block needs to be inside the “for each item” loop.

If that doesn’t fix it, what happens if you add a Refresh Data block at the very end of your list of blocks (outside of the loop)?

[andos-avlog]

Ok @manyone, I shall try this now and let you know what happens…

Andos.

[andos-avlog]

Hi @tatiang, putting the “IF” block inside the "For each item block didn’t work. See both screens below.

Live Test Screen

When I added the 2nd “Call DVL Refresh Data” block nothing happened by doing this either, See images below:

Live Test Screen

I cannot figure out why this won’t work.

Andos.

[tatiang]

This IF block is checking to see if the value of the DAYS column is true or false. Is that what your column values are? What are you trying to check there? Because an IF block without a logic block such as =, <, >, etc. only checks for true or false values.

Edit: I’m guessing that you’re trying to see if DAYS=1…? I think it’s a better idea to just code it as IF DAYS = 1 or =“1” (if it’s a text string).

[manyone]

1. take the 2nd if block (“get value…”) out and the 2nd data-viewer-refresh block but put back all the set button color command inside the when-monday-button-clicked - put after the call-data-viewer-refersh block.
2. change the “1” to 1, in the text-contains block
3. click on monday button to see if it works

if not, check while in design, see propery tab of data viewer list2 to make sure it’s associated with the correct google sheet worksbook and sheet name. show us a copy of your google sheet. does days have the concatenate formula in all rows? share a link to your google sheet, share a link to your project - if/when you’re ready.

[andos-avlog]

Hi @tatiang, I am trying to do this:

My column values are 1 (for Monday); 2 (for Tuesday); 3 (for Wednesday) etc…

When the "TimMoBtn (labelled Mo for Monday) button is clicked, the button will change button’s colour so the user can identify which day they are viewing. The DAYS column is checked to see if it contains the number “1” in that column. If it finds any number “1’s” in the “DAYS” column, it will return those records and list them in the DVL (Data_Viewer_List2) which will then become visible.

If another button say, “TimTuBtn” (labelled Tu for Tuesday) was clicked, then the Mo button would change back to its original colour and the Tu button would change colour to reflect that the number “2” is now being filtered to display Tuesday flights to the user.

The “TimTuBtn” button will be identical to the “TimMoBtn” button except that it will be for filtering and looking for number "2’s**.

@tatiang

Edit: I’m guessing that you’re trying to see if DAYS=1…? I think it’s a better idea to just code it as IF DAYS = 1 or =“1” (if it’s a text string).

I was trying to find a way to do this but couldn’t find a way to approach it using the blocks.

How would I resolve this?

Andos.

[tatiang]

@andos-avlog

I was trying to find a way to do this but couldn’t find a way to approach it using the blocks.

How would I resolve this?

Use the equals (=) block from the Logic drawer:

[andos-avlog]

Thanks @tatiang, I shall try that now. I was lookimg for a way to use those blocks earlier today but coudn’t figure it out.

I shall let you know how i get on.

Many thanks,

Andos.

[andos-avlog]

Hi @tatiang, this did not work. Are my blocks correct? See below:

Andos.

[tatiang]

In order to troubleshoot this, you should be checking to see if one column that has a one in it matches the number block 1 or the text block “1”.

Pick a single column, use its row ID, and test that IF condition. Not in the loop, not with all the other blocks you have. Just a single, simple test to determine if the data source value is 1 or “1”.

[manyone]

is daysOfOpLable associated with the DAYS column. it should. is your daa viewer list2 already associated with your google sheet ( in properties?) , if not, you can code a call-data-viewer-Refresh inside thw when-screen-opens to do the connection.
show us a picture of your spreadsheet and share a link to the project.
my simple demo was already worknig to do what you wanted.

[andos-avlog]

Hi @tatiang, I am not even seeing the DVL (Data_Viewer_List) appear. in LIve Test.

After you earlier asked about whether a “1” or “"1"” was being used I changed the 1’s over to check this. But the result was still the same.

I also tried removing the 2nd “Refresh” block but still no joy.

Andos.