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
Solved! Go to Solution.
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:
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:
Thanks - took a while to get my head around referencing things correctly but it’s working the way I want it now.
If you’ve not already found a solution, please post a screenshot showing the unwanted column displayed.
User | Count |
---|---|
43 | |
30 | |
26 | |
14 | |
14 |