So I’ve created this formula to pull in a list of Key columns IDs from a subtable
Select([Related RecTasks][RecTasksID],AND(
[Item]<>"Fire Alarm System - Call Point",
[Generic Process]<>"Trigger the fire alarm from a call point. Do a different call point each week"
))
+
LIST(
IF(
ISBLANK([Oldest Callpoint Check])
,
ANY(Select([Related RecTasks][RecTasksID],AND(
[Item]="Fire Alarm System - Call Point",
[Generic Process]="Trigger the fire alarm from a call point. Do a different call point each week"
)))
,
ANY(Select([Related RecTasks][RecTasksID],[Last Check]=
MIN(Select([Related RecTasks][Last Check],AND(
[Item]="Fire Alarm System - Call Point",
[Generic Process]="Trigger the fire alarm from a call point. Do a different call point each week"
)
))))
)
)
On its own the top bit
Select([Related RecTasks][RecTasksID],AND(
[Item]<>“Fire Alarm System - Call Point”,
[Generic Process]<>“Trigger the fire alarm from a call point. Do a different call point each week”
))
is fine and pulls in 2 records displayed as I’d expect
But the second half just gives me
**Which is sort of correct as there is only 1 key column I’m expecting. But why is it not displaying the record? The Inline view for it is standard. If I change the column type from List to Text it pulls in the correct key column ID. So I’m stumped **
Solved! Go to Solution.
That putting LIST() around everything doesn’t works makes complete sense to me: you’re making a list of lists, which definitely isn’t what you want.
That the first two work also makes sense.
Why the third doesn’t work is indeed confusing. I’ll bet it’s a bug with list dereferencing ([Related RecTasks][RecTasksID]
). Try rewriting the SELECT() to query the entire table rather than using the list dereference.
Reformatted for clarity:
Select(
[Related RecTasks][RecTasksID],
AND(
([Item] <> "Fire Alarm System - Call Point"),
([Generic Process] <> "Trigger the fire alarm from a call point. Do a different call point each week")
)
)
+
LIST(
IF(
ISBLANK([Oldest Callpoint Check]),
ANY(
Select(
[Related RecTasks][RecTasksID],
AND(
([Item] = "Fire Alarm System - Call Point"),
([Generic Process] = "Trigger the fire alarm from a call point. Do a different call point each week")
)
)
),
ANY(
Select(
[Related RecTasks][RecTasksID],
(
[Last Check]
= MIN(
Select(
[Related RecTasks][Last Check],
AND(
([Item] = "Fire Alarm System - Call Point"),
([Generic Process] = "Trigger the fire alarm from a call point. Do a different call point each week")
)
)
)
)
)
)
)
)
What if you change it from List to Ref?
Column Name ‘User Related RecTasks’ in Schema ‘Job_Schema’ of Column Type ‘Ref’ has an invalid app formula ‘=LIST( IF( ISBLANK([Oldest Callpoint Check]) , ANY(Select([Related RecTasks][RecTasksID],AND( [Item]=“Fire Alarm System - Call Point”, [Generic Process]=“Trigger the fire alarm from a call point. Do a different call point each week” ))) , ANY(Select([Related RecTasks][RecTasksID],[Last Check]= MIN(Select([Related RecTasks][Last Check],AND( [Item]=“Fire Alarm System - Call Point”, [Generic Process]=“Trigger the fire alarm from a call point. Do a different call point each week” ) )))) ) )’. The type of the app formula ‘List of Ref to table ‘RecTasks’ of Text’ does not match the column type ‘Ref to table ‘RecTasks’ of Text’.
BTW, remind me how to format code like you’ve done above
Whoops! Try without LIST() around the expression.
Go edit your original post. I updated it with formatting to make it clearer.
Its already wrapped in List()
So as I said above, the result of the equation is f0010059 which is the key column ID. So I tried LIST(“f0010059”) and got the same result. I.e. it says User Related RecTasks (1) but you can’t click on anything
RecTasks[RecTasksID] is a Test type and also the key column
Even more confusing…
Works
Says 1 displays 1
LIST("f0010059")
Says 2 displays 2
Select([Related RecTasks][RecTasksID],AND(
[Item]<>"Fire Alarm System - Call Point",
[Generic Process]<>"Trigger the fire alarm from a call point. Do a different call point each week"
))
Merge them together
Says 3 displays 1, just the result of LIST(“f0010059”)
Select([Related RecTasks][RecTasksID],AND(
[Item]<>"Fire Alarm System - Call Point",
[Generic Process]<>"Trigger the fire alarm from a call point. Do a different call point each week"
))
+
LIST("f0010059")
Does not work
List around just select function
LIST(
Select([Related RecTasks][RecTasksID],AND(
[Item]<>"Fire Alarm System - Call Point",
[Generic Process]<>"Trigger the fire alarm from a call point. Do a different call point each week"
)))
+
LIST("f0010059")
List around everything
LIST(
Select([Related RecTasks][RecTasksID],AND(
[Item]<>"Fire Alarm System - Call Point",
[Generic Process]<>"Trigger the fire alarm from a call point. Do a different call point each week"
))
+
LIST("f0010059")
)
I’m more confused now than when I started…
That putting LIST() around everything doesn’t works makes complete sense to me: you’re making a list of lists, which definitely isn’t what you want.
That the first two work also makes sense.
Why the third doesn’t work is indeed confusing. I’ll bet it’s a bug with list dereferencing ([Related RecTasks][RecTasksID]
). Try rewriting the SELECT() to query the entire table rather than using the list dereference.
You were right… This works fine
Select(RecTasks[RecTasksID],AND(
[Job]=[_ThisRow].[JobID],
[Item]<>"Fire Alarm System - Call Point",
[Generic Process]<>"Trigger the fire alarm from a call point. Do a different call point each week"
))
+
LIST("f0010059")
Is it just me that ends up stuck for 4 hours on these obscure bugs…
Dear God, no! How do you think I knew to suggest this? Hours upon hours of frustration!
BTW, I generally avoid dereferencing the Related lists entirely exactly because of oddities like this.
User | Count |
---|---|
25 | |
25 | |
24 | |
21 | |
20 |