Calendar view using interactive dashboard filter

[Dan_Longley]

Mimicking the demo app " Slice based on user input", I have an interactive dashboard filter that should allow me to select a Client Name using table Filter-Client. That bit works ok. Then it should show on the calendar all dates that the client has (previously) attended.

With this formula

CONTAINS(Register[Attendees],ANY(Filter_Client[Client Name]))

in a slice (looking at Register table) for the calendar view, I’ve managed to get the calendar to recognise that, for example, Anton has attended before, but it shows ALL the dates, even those that he didn’t attend. In other words, it’s a yes/no response.

How do I get the formula to check which dates Anton was an attendee and then return just those dates on the calendar view.

T

Any pointers much appreciated.

[WillowMobileSys]

I believe you want to use the IN() function instead. CONTAINS()is a function meant to operate on text strings and not lists.

I think the function you are looking for is something SIMILAR to:

IN([Client Name], Register[Attendees])

This is assuming that:

• [Client Name] is a column in the row you are operating on
• the [Attendees] column is the name of the attendee - not the ID as in a Ref column.

If the data structure is different than I am assuming then post back and one of us can help get the correct IN() statement written with you.

help.appsheet.com

IN()

Is item in list?

help.appsheet.com

CONTAINS()

Does text contain fragment?

[Dan_Longley]

@John_Baer That’s really useful, thanks.

However, I’m not there yet! I’ve interpreted what you’ve said to come up with:

IN(ANY(Filter_Client[Client Name]),Register[Attendees])

where ANY(…) reads the Full Name of the client I’ve selected (from the FilterClient table) - the needle

The haystack: does the Attendees column on Register table contain my selected client on any of the rows? Unfortunately, if the client exists in any of the lists (under Attendees), then the calendar view shows ALL the dates, not just, say, Sat 11 Apr for Anton. If I choose a client who doesn’t appear on the register, then it rightly shows no dates, so I know the filter is connected correctly to the calendar view.

I thought about embedding the above in FILTER(), but I get: “The expression is valid but its result type ‘List’ is not one of the expected types: Yes/No”

What am I missing here? Help!

[WillowMobileSys]

Ok so the Register[Attendees] portion of the IN() function gives you list of the Attendees values from EVERY row in the Register table. I assume you have Attendees defined as an EnumList? So you will get a List of Lists which effectively one big list of ALL attendees in the Register table.

In your case, If I understand correctly, you are trying to get a list of registrations the Filter client is listed as an Attendee on. So you will need an expression more like this:

SELECT(Register[RegisterID], 
       IN(ANY(Filter_Client[Client Name]
          ), 
          [Attendees]
       )
)

This gives you a list of registrations that include the Filter Client Name.

I hope this helps!

[Dan_Longley]

@John_Baer Thank you, I can see how that should work, but I’m still getting:
“The expression is valid but its result type ‘List’ is not one of the expected types: Yes/No”

“The filter condition ‘=SELECT(Register[RegisterID], IN(ANY(Filter_Client[Client Name] ), [Attendees] ) )’ of table slice ‘Client attendance calendar’ must return true or false.”

You’re right that I use a UX form that allows the user to select from a long EnumList of Client Names (drawn from another spreadsheet table of client details). This results in a row in the Register table which includes a list of all attendees (Client Names) for that day’s class; the Count in the next column adds up the number of attendees.

What’s the mis-match here, any thoughts?

[WillowMobileSys · Accepted solution]

I wasn’t completely sure how you were using the original expression.

For the slice filter criteria, you simply need to wrap the expression I provided with another IN() function. I am assuming that you have access to the RegisterID value is some way such as directly in a column in the “Client Attendance Calendar” slice row or through a Ref column. If you don’t, be sure to add it.

IN([RegisterID],
   SELECT(Register[RegisterID], 
          IN(ANY(Filter_Client[Client Name]
             ), 
             [Attendees]
          )
   )
)

Note that I used RegisterID in two places. AppSheet should be able to figure out from context that the first is in the Slice dataset and the second is from the Register table.

[Dan_Longley]

@John_Baer Yes, it works, thank you so much!! I’m not sure about whether I have a ref column, etc but it appears to be functioning correctly.

I would never have worked out to nest an IN() within another, so will now study it until it makes sense to me

Thanks again for your prompt support

[Neha_Sharma]

Hi,
If you don’t mind, can you please share the link of your APP because I am also trying to create the slice-based upon user input in my calendar but somehow it’s not working for me.
Thanks

[Dan_Longley]

Hi
I’m really sorry, but it’s been 2 years, and it would take me a couple of days to work out how the code works. I did do a copy at some stage, but it broke, so was probably no use. Perhaps address your question to @John_Baer ?

[SkrOYC]

As much as I hate to not to have a good article from here, this is the best explanation I’ve found for Dynamic Dashboard:

Also, the app refered on the OP is this:
Slice based on user input