Solved: Virtual list table not displaying records

1minManager · 05-06-2021 08:01 AM

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 **

Steve

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.

View solution in original post

Steve

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")
              )
            )
          )
        )
      )
    )
  )
)

Steve

What if you change it from List to Ref?

1minManager

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’.

More info

BTW, remind me how to format code like you’ve done above

Steve

Whoops! Try without LIST() around the expression.

Go edit your original post. I updated it with formatting to make it clearer.

1minManager

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

1minManager

RecTasks[RecTasksID] is a Test type and also the key column

1minManager

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…

Steve

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.

1minManager

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…

Steve

Dear God, no! How do you think I knew to suggest this? Hours upon hours of frustration!

Steve

BTW, I generally avoid dereferencing the Related lists entirely exactly because of oddities like this.