Solved: Show_if expression depending on value in table

deegee3 · 01-15-2022 05:17 PM

I am trying to hide a field depending on a value in another table.

I have 2 tables:
Inventory table - contains various item details, including a Quantity column which I only want to be visible if the QuantityRequired value is set to true for the particular type of Device in the LookupDevice table.
LookupDevice table - contains all types of Device, QuantityRequired and others

If QuantityRequired = TRUE when matched by Device in Inventory and LookupDevice tables then I want the quantity field to be visible on a form, otherwise hide it.

I have tried:
IF(LOOKUP([_THISROW].[Device], “LookupDevice”, “Device”, “QuantityRequired”)=TRUE, TRUE, FALSE)

which seems to evaluate correctly when I test output of the expression, but it makes no different at all to the visibility of the field in the form.

The system displays: IF this condition is true:
…(One randomly chosen value from this list (
…The list of values of column ‘QuantityRequired’
…from rows of table ‘LookupDevice’
…where this condition is true: ((The value of column ‘Device’) is equal to (The value of ‘Device’ from the row referenced by ‘Key’)))) is equal to (“TRUE”)
THEN use the value of:
…“TRUE”
ELSE use the value of:
…“FALSE”

Help!
Thanks,
DG3

Joseph_Seddik

Hello and welcome to the community!

So basically, your Inventory should have a reference column to the LookupDevice table, with the latter key column named let’s say deviceID.

For a particular row in your table, in order to retrieve a value from a referenced row in another table you should use the following expression:

[keyID].[valueToRetrieve]

So in your case you should use: [deviceID].[QuantityRequired], and the expression to use for the Show_IF formula should be: [deviceID].[QuantityRequired] = TRUE

I encourage you to read the short, informative guides below:

View solution in original post

Joseph_Seddik

Hello and welcome to the community!

So basically, your Inventory should have a reference column to the LookupDevice table, with the latter key column named let’s say deviceID.

For a particular row in your table, in order to retrieve a value from a referenced row in another table you should use the following expression:

[keyID].[valueToRetrieve]

So in your case you should use: [deviceID].[QuantityRequired], and the expression to use for the Show_IF formula should be: [deviceID].[QuantityRequired] = TRUE

I encourage you to read the short, informative guides below:

deegee3

Thanks - took a while to get my head around referencing things correctly but it’s working the way I want it now.

Steve

If you’ve not already found a solution, please post a screenshot showing the unwanted column displayed.