Chaining list dereference with regular dereference and vice versa

AppSheet AppSheet Q&A
1

Hey everybody!

I’m not particularly good at describing problems in a concise manner, so I’ll just get straight to the point. In one of my VCs, I need to dereference a list ref column, retrieve a ref field from all of the refs and then select only those refs from the result which have a specific column set. My expression looks like this:

SELECT([Blueprint].[Manufacturers][Building], ISNOTBLANK([Upkeep]))

And it fails with

Error in expression '[Blueprint].[Manufacturers][Building]' : Unable to find column 'Manufacturers][Building'

Is there any way to get this to work?

I also tried setting up multiple VCs, like this:

  1. _Manufacturers: [Blueprint].[Manufacturers]
  2. _Buildings: SELECT([_Manufacturers][Building], ISNOTBLANK([Upkeep]))

But this just fails with “Unable to find column ‘Upkeep’”.

While the docs mention chaining dereferences, they do not mention mixing dereferences and list dereferences and also don’t say anything about chaining list dereferences.

I am a bit puzzled that after a good 6 hours of trying around and googling for solutions, I have not found anything that even remotely mentions anything like what I am trying to achieve and I can’t understand why, since this is such a basically trivial example I was assuming this must have been an issue for somebody in the past.

Apparently, that is not the case, so I was wondering if anybody could help me out

2

You understood correctly, you cannot mix both types and you cannot chain list dereferences. Instead add more columns to store intermediate results and avoid dereference chaining and mixing.

Also check your SELECT() syntax. Unless you feed this function with key values, you should use SELECT(tableName[column], …)

1 Like