Hi, I have created an agenda application and...

expressions
(Juan Bravo Roig) #1

Hi,

I have created an agenda application and I’m having problems with the filtering.

[IMAGE 1]

We have two tables, one which contains all the sessions of the event, and a “personal agenda” with specific sessions chosen by the user.

To register for an event, we have created an action (the blue button)

[IMAGE 2]

We want to show this button only when the remaining seats is higher than 0 and when the user is not registered. (If the user is registered we should show a “unregister” button). The formula that is used to filter does not work, if we are registered for an event, the button keeps appearing… and it’s strange because we are using this formula in another application witch is working fine…

This is the formula: AND(NOT(IN(USEREMAIL(),SELECT(Responses [User],[SessionName]=[TOPIC]))),[Remaining seats]>0)

[IMAGE 3]

On the other hand:

I have created an slice for “my agenda” but is not working either.

This is the row filter condition: IN([topic], SELECT(Responses [SessionName],[USER]=USEREMAIL()))

[IMAGE 4]

When I test this formula on the expression assistant, it works, but the UX view for this Slice is not showing the data.

[IMAGE 5]

I have spend a lot of time trying to resolve this problems but i haven’t found a solution…

Could you please help me?

(Steven Coile) #2

I suspect your problem has to do with the way select() interprets column references, which can easily cause confusion. I get caught by this all the time.

A bare column reference within a select() statement refers to that column within the row under consideration from the table select() is reviewing. In your expression:

AND(NOT(IN(USEREMAIL(),SELECT(Responses [User],[SessionName]=[TOPIC]))),[Remaining seats]>0)

select() is reviewing the Responses table and specifically attempting to compare the values of the [SessionName] and [TOPIC] columns of that table.

I suspect you actually want to compare a column in the Responses table with a column in a different table, apparently Optional Events.

It appears to me you want your select() statement to find Responses rows with a [SessionName] column value that matches the [TOPIC] column value of the currently-displayed Optional Events row.

To do that, you need to tell select() specifically to use the [TOPIC] column of the currently-displayed row.

The Register action is being used in the Optional Events table. Within a select(), the currently-displayed row can be referenced as [_thisrow], and column values from that row can be accessed be de-reference, as in [_thisrow].[TOPIC].

So, try replacing [TOPIC] in your select() statement with [_thisrow].[TOPIC]:

AND(NOT(IN(USEREMAIL(),SELECT(Responses [User],[SessionName]=[_thisrow].[TOPIC]))),[Remaining seats]>0)

“Should [Remaining seats] also get [_thisrow],” you may ask? No, because that row reference isn’t inside a select() statement, so there’s no confusion as to which table it refers: it refers to Optional Events implicitly.