UX View>Showif Expression Problem

[Nokomis_Fair]

Hi.
I am having issues with creating UX View Show if Expressions (view linked to table “xxxx”), for which, the Show if Expression must reference a different table/worksheet within a different workbook. The Show if Expression tried, refers to a Virtual Column called “Current Status” that is set as a ‘Ref’ type, pointing to a worksheet column called "Voting Status’ that is set as an ‘Emun’ type containing 5 optional values. A “Timestamp” column is set as the key and “Voting Status” column is set as the label.

I plan to use 2 APPS (one for ‘admin’ and one for ‘public’) that utilize different workbooks and uses GoogleSheets for both APP’s. For this issue, both APP’s are linked to the same table in the same ‘admin’ workbook also called “Voting Status” and set up the same way as described above. In the “Voting Status” column of the ‘admin’ APP, a Valid if Expression exists that has 5 optional values set within curly brackets and displays as buttons in the ‘admin’ APP. The ‘admin’ APP sets the “Voting Status” column and the read only “Current Status” virtual column displays the current status equal to the ‘admin’s’ last selected voting status option that was saved to the worksheet. Within the “Current Status” virtual column, the following Expression (see below) is used to sort and filter the “Voting Status” column by the “Timestamp” column to filter and auto display the read only result in the “Current Status” column in the APP. This works perfectly so that the ‘admin’ APP always shows the current status that was last selected by the ‘admin’ for an easy read only reference, as the “Current Status” column updates immediately as a local device computed result within the ‘admin’ APP.

Next, since the ‘public’ APP references the same table/worksheet (perhaps using a Slice may have also worked?), the columns are setup the exact same way as in the ‘admin’ APP, except there was no need to include the same Valid if values in curly brackets because the ‘public’ APP users cannot set the status as this is only set by the ‘admin’. By syncing the APP, the “Current Status” virtual column result in the ‘public’ APP always displays the same result as in the ‘admin’ APP’s “Current Status” virtual column, for which, is the goal.

Now, in trying to create a Show if Expression, I can get a lot of valid true/false expressions, but, none are working as intended to only show, or not, the ‘public’ APP UX view/s based on the “Current Status”. I can’t seem to get the Show if Expression to match the “Current Status” as the [Voting Status] is set, and reset, to a new status in the ‘admin’ APP. When using a simple expression such as “Voting Status”[Current Status], the result is the first posted [Timestamp] value in the list. When trying to use a more complex expression, I can get either the most recent posted [Timestamp] value result, or, a [Voting Status] result that is not relative to the most current [Timestamp] value result.

FYI: here is the formula used successfully in the “Current Status” virtual columns for both APPs to display the current status:
ANY(
TOP(
ORDERBY(
FILTER(“Voting Status”,[Voting Status]=[Voting Status]
),
[Timestamp], TRUE
),
1
)
)

Here is a sample expression that is valid in the UX View Show if Expression, but, the result provided is not what is required. This expression returns the most recent [Timestamp] value:
ISNOTBLANK(TOP(ORDERBY(FILTER(“Voting Status”,[Voting Status]=“Voting in Progress”),[Timestamp],TRUE),1))

I can change the [Voting Status] to [Current Status] in the expression and get the same result. I can also alter the expression and get an unsorted list of [Voting Status] values from oldest to newest (most recent), for which, is not what is required, unless it was reverse sorted, or, relative to the most recent [Timestamp] value.

Your assistance is greatly appreciated. Thank you.

[Steve]

Please concisely restate your question. There’s waaaay too much detail in there.

[Nokomis_Fair]

I apologize for the details. I thought it would help understand the problem.

Bottom line is, I need to create a Show if Expression for views in a ‘public’ APP, for which, the trigger is a row record from a different table and workbook than the View data is sourced from. An ‘admin’ APP is to control 3 different views visibility in the ‘public’ APP. An ‘admin’ APP creates the table row record based on a status of the event as selected from the ‘admin’ APP. The controlling table has a Timestamp and a Voting Status column, as well as, a Current Status virtual column. Essentially, as the event progresses through different stages, the ‘admin’ creates a table record indicating the current status, for which, the corresponding 'public APP UX View will become active while some view/s will not. This is to keep everything simple for the public user and only show them a view based on what is currently going on at the event.

Does this description help?

[Steve]

Okay, lemme see if I understand: in the public app, you want views to be shown or hidden according to the value of a column in a row of a table that is not displayed by the view itself. That’s not a problem. You’ll probably want to use the LOOKUP() function:

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

Consider that and let me know if you can use it. If not, there are other options.

[Nokomis_Fair]

Hi Steve:

Yes. The view/s to be shown or hidden are not determined by the data in the view itself. I want to reference the read only current status (single column value) in another view that is always visible.

I have tried your advice, however, the issue remains unresolved. I am most likely not writing the Expression correctly. I have one view in the ‘public’ APP that is read only and indicates the current status and is always displayed. This view’s current status displayed text value is from a virtual column with a sort and filter expression (as shown previously) of a table in the ‘admin’ side, that records, by row, each status change entered by the ‘admin’. In order to show or hide a view, I need to base this off of what the most recent current status is.

When I write a more complex Expression, it seems to get the most recent [Timestamp] value, but, not the most recent current status text value. When I try to check if the current status text value matches, I get the error that says can’t compared text value, or, List… This leads me to believe that it may be because the Expression that is valid is trying to compare the defined text string value against the [Timestamp] value.

If it matters, in the table, the [Timestamp] column is the key and the [Voting Status] column is the label. The [Current Status] virtual column is a Ref type and the [Voting Status] is Emun type.

I am totally stuck in getting the Show if to work. I sincerely appreciate your patience, understanding and willingness to help. Again, sorry for the lengthy reply, but, I’m doing my best to describe the issue.

[Steve]

Nokomis_Fair:

I have one view in the ‘public’ APP that is read only and indicates the current status and is always displayed. This view’s current status displayed text value is from a virtual column with a sort and filter expression (as shown previously) of a table in the ‘admin’ side, that records, by row, each status change entered by the ‘admin’.

Please post a screenshot of this view’s configuration.

[Nokomis_Fair]

1. Source Table
   

2. ‘admin’ APP Table/Column configuration (‘public’ APP Table/Column set up the same)
   

3. ‘public’ APP View configuration
   

[Steve]

I’m having a lot of difficulty understanding what the problem is an how your app is set up. I think you might have more luck engaging support@appsheet.com and allowing them to look at your app directly.

[Nokomis_Fair]

Ok. Will do. Thank you.

[Nokomis_Fair · Accepted solution]

Aleksi found a solution for me. Thanks for your assistance.

[Aleksi]

For other readers that might be interested… the correct formula was
“Voting in Progress”=
LOOKUP(
MAXROW(“Voting Status”,“Timestamp”),
“Voting Status”,“Timestamp”,“Voting Status”)