Data that has been sliced (filtered) based on one field is returned correctly in the slice but the view based on this slice is empty. Anyone have any ideas what the issue could be?
The field being used to slice the data is a VC lookup to another table.
Solved! Go to Solution.
More details?
Two tables, Circuits & Circuit_Notes. The VC looks up the related rows in the Circuit_Notes table, select the row with the most recent date (MAXROW) and returns the value of field IsActive. If there are no related rows in the Circuit_Notes table, the expression returns TRUE.
IF(
ISBLANK(SELECT(CIRCUIT_NOTES[IsActive],[NOTEID]=
MAXROW(โCircuit_Notesโ,โDATEโ,[CIRCUIT]=[_THISROW].[CIRCUIT]))),โTrueโ,
SELECT(CIRCUIT_NOTES[IsActive],[NOTEID]=
MAXROW(โCircuit_Notesโ,โDATEโ,[CIRCUIT]=[_THISROW].[CIRCUIT]))
)
Data sliced on [IsActive]=TRUE returns the expected data, only the Active circuits. Views based on this slice are empty.
SELECT() returns a list of values, but you only want a singular value from the second SELECT(). Try wrapping it with ANY():
IF(
ISBLANK(
SELECT(
CIRCUIT_NOTES[IsActive],
(
[NOTEID]
= MAXROW(
"Circuit_Notes",
"DATE",
([CIRCUIT] = [_THISROW].[CIRCUIT])
)
)
)
),
"True",
ANY(
SELECT(
CIRCUIT_NOTES[IsActive],
(
[NOTEID]
= MAXROW(
"Circuit_Notes",
"DATE",
([CIRCUIT] = [_THISROW].[CIRCUIT])
)
)
)
)
)
No dice, still blank.
What are you using to format your expression? Surely youโre not doing that manually?
Just using a simple text editor. The website is doing the colorizing.
Also, that expression of yours is hugely expensive!
For each row of CIRCUITS: 1) look at every row of CIRCUIT_NOTES; 2) for each row of CIRCUIT_NOTES, look again at each row of CIRCUIT_NOTES; 3) if the circuit occurs in CIRCUIT_NOTES, repeat (1) and (2).
If N is the number of rows in CIRCUITS, M is the number of rows in CIRCUIT_NOTES, and every circuit is represented in CIRCUIT_NOTES, youโre doing 2M(N^2) row inspections!
Eek!
I suggest creating a virtual column (e.g., LATEST_NOTE) to compute MAXROW(...)
:
MAXROW(
"Circuit_Notes",
"DATE",
([CIRCUIT] = [_THISROW].[CIRCUIT])
)
then rewrite the existing VC app formula to make use of it:
IF(
ISBLANK(
SELECT(
CIRCUIT_NOTES[IsActive],
([NOTEID] = [LATEST_NOTE])
)
),
"True",
ANY(
SELECT(
CIRCUIT_NOTES[IsActive],
([NOTEID] = [LATEST_NOTE])
)
)
)
which brings you down to 3MN. Still more than necessary, but a lot better! But you can reduce further:
IF(
ISBLANK([LATEST_NOTE].[IsActive]),
"True",
[LATEST_NOTE].[IsActive]
)
which brings you down to MN row inspections.
Thank you for pointing out how expensive my expression was and rewriting it for me. Unfortunately, the results are the same: Expected data is returned in the slice but not in the view based on the slice.
I built a test app if youโre interested and available to take a look?
As for the expression, I was curious as to how the expression was being written in regards to the segmentation rather than the color formatting. Where each part of the expression is broken into individual segments.
Iโd be happy to. sc2758g@gmail.com
Invited. Thank you Steve
Can I get edit access? I canโt see the config, only the running app.
Thats odd, I invited you and set your role as Admin:
Iโm in.
I had the editor open until just a second ago. Think that made a difference?
No. I was looking for an Edit option in the drop down; didnโt try just clicking in the image. Used to be an Edit optionโฆ grumble
Iโm guessing mean the blocks enclosing the code fragments? Precede and follow the snippet with a line consisting of three consecutive backticks (```).
Interestingโฆ Thank you for the tip!
So letโs look at this further.
How are you determining the data is returned correctly in the slice?
What type of view is it?
Do any of the columns have Show? set to OFF, or have a Show_If expression that might hide them?
Each slice has a 'View Data" button. Utilizing this feature, I am able to see the data the slice returns.
A table view yields no results.
No columns are hidden or using a Show_If expression.
IsActive is a Text column. Is there a reason it isnโt Yes/No?
No reason other than thatโs what it defaulted to.
Shouldnโt make a difference, but Iโd recommend changing it to Yes/No.
I noticed that it was text rather than yes/no but same as you, I didnโt think it should make a difference and havenโt tried changing it. Since youโre there, change it and see if that works.
Bingo!
Donโt you love & hate it when it so simpleโฆ Rather annoying reallyโฆ
Thanks for your help Steve.
Incredibly annoying! And I have no idea why it made a difference.
Happy to help. Remember to remove my access.
Youโre awesome man, really appreciate it.
User | Count |
---|---|
32 | |
31 | |
30 | |
18 | |
17 |