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 |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |