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.
LinkedIn
Twitter
