Hidden panel unless one of many selected

  • 1 September 2020
  • 1 reply
  • 5 views

Badge

I have the following code in place to hide a panel unless Brewing, Bottling or Racking is selected in a list lookup field for department.

 

not(or(equals(Department,"1;#Brewing"),equals(Department,"3;#Bottling"),equals(Department,"4;#Racking")))

 

I'm trying to add a new department called Canning, but when I do the hidden panel will not show when Canning is selected.  If I put Canning first in the list it works and then Racking will not work.

 

not(or(equals(Department,"1;#Brewing"),equals(Department,"3;#Bottling"),equals(Department,"4;#Racking"),equals(Department,"6;Canning")))

 

Is there a limit to the number of or(equals statements I can have here?


1 reply

Userlevel 5
Badge +14

It's not that there is a limit to the 'equals()' function, but the syntax for your 'or()' functions is incorrect. 

 

If we look at the description of that particular Runtime Function, we see that it reads: 

 

Returns TRUE where either logical arguments are true. Returns FALSE when both values are False.
or(boolean1, boolean2)

Notice that example, "(boolean1, boolean2)". This indicates that it only regards the first two arguments that are passed into it. What you currently have is logically structured as shown: 

not( or( equals(Department,"1;#Brewing"), equals(Department,"3;#Bottling"), equals(Department,"4;#Racking") ) )

 

When it would need to be: 

not( or( or( equals(Department,"1;#Brewing"), equals(Department,"3;#Bottling") ), equals(Department,"4;#Racking") ) )

 

Additionally, in your second example where you have added "Canning" you miss the pound-sign in the index prefix writing, "6;Canning" instead of "6;#Canning".

 

Following this pattern, your new addition would need to look like: 

not( or( or( equals(Department,"1;#Brewing"), equals(Department,"3;#Bottling"), ), or( equals(Department,"4;#Racking"), equals(Department,"6;#Canning") ) ) )

 

That *should* produce the results that you're looking for. 

 

 

Bonus Code Disclaimer! Everything below this point is just an expanded solution for the problem, solving it in the way I would solve it myself. It is not necessary to implement ANY of the below! It is simply here for clarity and for anyone who wants to learn different approaches to common logic problems in programming. I find it MUCH easier to read / look at / understand, but that doesn't mean anybody else will! Depending on your level of comfort with javascript, it may or may not be easy to read. That's alright, and it's why I try to explain what it does and how it works. It might just make sense in the future, and that's okay!


Bonus Code: If you are comfortable with javascript, you can also solve this problem in a way that (I personally find) is more readable and requires less work to change in the event of an update to your production line / sharepoint list.

(function(department){ return ["Brewing", "Bottling", "Racking", "Canning"].indexOf(department) === -1; }(parseLookup(Department)));

 

This code uses the 'parseLookup()' runtime function to remove the index number from the Lookup Control's value.

 

So "6;#Canning" simply becomes "Canning". This way, you do not have to worry about the index of the item, as it's not really relevant to anything.

 

Additionally, it relies on the 'indexOf()' method to tell us whether or not our selected value is in the range of values (or: the array of values) we want to show the panel on. The way that IndexOf works is that it checks to see if the value of the thing being passed into it ('department' in this case) is in the array the method is being invoked on (["Brewing", "Bottling", "Racking", "Canning"] in this case). 

 

If the value is found inside of that array, it returns the place or the 'index' of where it is inside of that array, the minimum value being the first spot in the array which is 0. However, if it can't find it, then it returns -1. 

 

In the above code, if the Department Control (and subsequently the 'department' variable) doesn't equal a value in that array, then it returns -1, which we are then comparing against the value of -1 using the '===' comparison operator. If those two values match, then that comparison is true, which makes the Formatting Rule fire, and do the thing we set it up to do. Otherwise that comparison is false, and the Rule undoes anything it has done previously or simply doesn't run at all!

 

Here is an example using a Lookup Control on a test Form, that points to a list of Fruits. I'll hide the panel on every entry except for:

["Apple", "Avocado", "Kumquat", "Satsuma"]

 

My Form:

8839i1259B8138EB87538.png

 

My Rule:

8840i7AAD0D68EEA00D08.png

 

My Formula:

8841iA12D226EA2C38836.png

(note: remember to set your initial Reference to your control as shown with the arrow!)

 

Formula Code: 

(function(fruit){ return ["Apple", "Avocado", "Kumquat", "Satsuma"].indexOf(fruit) === -1; }(parseLookup(Control_FruitsLookup)));

 

Results: 

8842i8CD8946E66341055.png8843i637E969BB84F947D.png8844i54F74FB1425ACA40.png8845i6947F5EA9601DB47.png

etc... 

 

Any who. I hope that this helps you on your journey.

 

 

 

Reply