Can a security filter rule depend on a virtua...

[Simon_Robinson]

Can a security filter rule depend on a virtual column in the table its referencing or do all the columns need to be in the spreadsheet?

I have 2 tables.

Job is the main table and Photos is the sub-table.

The security filter I use at the moment is

NOT(Photos[JobStatus]=“Complete”.

The issue I’ve realised is that Photos[JobStatus] uses a formula of [Job].[Status].

But if there are multiple photos Row then changing Job[Status] does not update the Photos[JobStatus]

So would a VC work?

[Bellave_Jayaram]

@Simon_Robinson I don’t think you can put VC in a security filter. Try this - make a VC Photos[JobStatusVC] and put the formula [Job].[Status] there. Then, set Photos[JobStatus] which is a real column to [JobStatusVC].

[Simon_Robinson]

Hi @Bellave_Jayaram.

I’m not sure that would work.

So in this example, I’m thinking of a situation where I have 1 Job linked to 4 rows in Photo table.

Initially, all 4 photo rows are visible.

But by changing Job[Status] from “in progress” to “complete” I want the security filter to not display the 4 photo rows.

In the example you gave, Photos[JobStatus] would not change unless I edited all 4 Photo rows to allow the real column to update.

Let me know if I’m somehow wrong here please

[Bellave_Jayaram]

When [Job].[Status] changes, I would expect all Photos for that Job to have the same Status value saved to the real column by virtue of the VC changing. I haven’t tried it but it seems it should work.

[Aleksi_Alkio]

Yes, that’s correct. You can’t use virtual columns with security filter. As Jayaram explained, the [Job].[Status] should work because both of them are real columns. The expression could be like [Job].[Status]=“in progress”