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.
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?
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.
Just using a simple text editor. The website is doing the colorizing.
Michael:
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.
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?
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.
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.
Michael:
I built a test app if you’re interested and available to take a look?
I’d be happy to. sc2758g@gmail.com
Michael:
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.
I’m guessing mean the blocks enclosing the code fragments? Precede and follow the snippet with a line consisting of three consecutive backticks (```).
Steve:
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!
Can I get edit access? I can’t see the config, only the running app.
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
IsActive is a Text column. Is there a reason it isn’t Yes/No?
