Security filters Invalid type "Unknown"

[Jonathan_S]

Can someone let me know why This won’t work in a security Filter?

I know the formula is correct, I have the same formula working in other parts of app, just trying to implement into security filter.

What am I doing wrong?

[LeventK]

@Jonathan_S
The second argumentative expression of your math subtraction is slightly incorrect. Pls. see:

[Suvrutt_Gurjar]

Also you may wish to update the community or review some more mismatches highlighted in the image below between the error expression and the security filter expression.

1. True is written as “True” in one and True in another

2. Error expression shows [_THISROW-1] and the normal [_THISROW]

3. There are a different number of closing parenthesis in the second SUM() expression

[Jonathan_S]

How is this possible, is this a bug or because of my expression?

[Steve]

Suvrutt_Gurjar:

Error expression shows [_THISROW-1] and the normal [_THISROW]

This is just a quirk in Expression Assistant. It’s not a problem here.

[Suvrutt_Gurjar]

Oh okay. Thank you @Steve for that clarification.

[LeventK]

@Jonathan_S
Try with this one:

 AND(
	OR(
		IN(
			USEREMAIL(),
			Admin Emails[Email]
		),
		IN(
			[Display Name].[vendors],
			SELECT(
				Security Filters[Vendors],
				[email] = USEREMAIL()
			)
		)
	),
	(SUM(
		SELECT(
			display Lot Numbers[Amount Added],
			AND(
				[Display Name] = [_THISROW].[Display Name],
				[Display Lot Number] = [_THISROW].[Display Lot Number],
				NOT([Mistake] = True)
			)
		)
	) - 
	SUM(
		SELECT(
			display Lot Numbers[Amount Removed],
			AND(
				[Display Name ] = [_THISROW].[Display Name],
				[Display Lot Number] = [_THISROW].[Display Lot Number],
				NOT([Mistake] = True)
			)
		)
	))>0 
)

[Suvrutt_Gurjar]

Very neat and ideal expression @LeventK

[Bahbus]

LeventK:

Try with this one:

 AND(
	OR(
		IN(
			USEREMAIL(),
			Admin Emails[Email]
		),
		IN(
			[Display Name].[vendors],
			SELECT(
				Security Filters[Vendors],
				[email] = USEREMAIL()
			)
		)
	),
	(SUM(
		SELECT(
			display Lot Numbers[Amount Added],
			AND(
				[Display Name] = [_THISROW].[Display Name],
				[Display Lot Number] = [_THISROW].[Display Lot Number],
				NOT([Mistake])
			)
		)
	) - 
	SUM(
		SELECT(
			display Lot Numbers[Amount Removed],
			AND(
				[Display Name ] = [_THISROW].[Display Name],
				[Display Lot Number] = [_THISROW].[Display Lot Number],
				NOT([Mistake])
			)
		)
	))>0 
)

Removed superfluous comparisons.

[Jonathan_S]

LeventK:

AND(
	OR(
		IN(
			USEREMAIL(),
			Admin Emails[Email]
		),
		IN(
			[Display Name].[vendors],
			SELECT(
				Security Filters[Vendors],
				[email] = USEREMAIL()
			)
		)
	),
	(SUM(
		SELECT(
			display Lot Numbers[Amount Added],
			AND(
				[Display Name] = [_THISROW].[Display Name],
				[Display Lot Number] = [_THISROW].[Display Lot Number],
				NOT([Mistake] = True)
			)
		)
	) - 
	SUM(
		SELECT(
			display Lot Numbers[Amount Removed],
			AND(
				[Display Name ] = [_THISROW].[Display Name],
				[Display Lot Number] = [_THISROW].[Display Lot Number],
				NOT([Mistake] = True)
			)
		)
	))>0 
)

Im getting this error, the expression is valid though,

[LeventK]

With the security filters, you cannot reference the table you are applying the security filter