Now that we’ve covered how to build the logic of a basic formula (yes, even though it has multiple nested conditional checks, it’s still a basic formula), we’re going to look at bringing in the additional logical functions OR and AND to test for combinations of logical tests all within the same check.
To begin, we first need to understand the logic associated with both new functions, so we’ll revisit our Truth Table to illustrate how OR and AND work (Figure 8-43).
The idea behind the OR function is that if any of the values are true, the entire statement is true, and if none of them are true, the entire statement is false.
We can use the OR function to test for up to 30 different values together and, as long as at least one of them results in true, the end result will also be true.
The OR function has the following format:
| =OR(test1,test2,test3,...test30) |
It could be something as simple as the following:
| =OR(1+1=2) : result = TRUE since 1+1 does equal 2 |
Or it could be more complex with a series of nested logical functions similar to our example earlier in this chapter (the OR function just puts them all together to determine if any of them results in a true result).
To explore the OR function, we’ll look at a basic business model for paychecks. For someone to receive a paycheck, there are minimum criteria that must be met; the employee should be in the system (an employee on record) and should have worked a number of hours. There’s the possibility that the employee took vacation days or sick leave as well, so we’re going to take a look at these three types of hours (working, sick, and vacation) to see if the employee should be issued a paycheck.
For our new logic problem, our conditions (questions) will be:
Regular hours
Vacation
Sick leave
If we add these conditions to our Truth Table, we get the table shown in Figure 8-44.
The golden rule with an OR function is that no matter what conditions are, as long as one of them yields a true result, the entire formula is considered true.
Let’s look at each row in the Truth Table and expand each question so we can fully understand it.
Row 1: The employee worked regular hours, took vacation, and submitted sick leave (true for all three conditions). Since at least one of the three is true (satisfying our golden rule), the final result is true, so the employee will be issued a paycheck.
Row 2: The employee worked regular hours and took vacation, but did not submit sick leave. Again, since at least one of the three is true, the final result is true, so the employee will be issued a paycheck.
Rows 3–7: At least one of the three is true, so the final result is true, which will allow for a paycheck to be issued to the employee.
Row 8: In all previous rows, at least one condition is true, so the final result is true. However, in the last row, none of the conditions have been met, so the final result is false and the employee will not be issued a paycheck.
Pretty simple so far—it’s basically an “any or none” test. As long as one of the conditions is TRUE, the entire formula yields a TRUE result as well. What if, instead of any conditions being True, we want to test for a combination of conditions? This is where the cousin to the OR function comes in—the AND function.
The AND function is similar to the OR function in that it will look at up to 30 different values for comparison, but it’s difference is that for the final result to be True, every condition must be True in the entire check or the end result will be False.
It’s important to understand this difference, because it will influence your choice to use one or the other and how you will build your logic.
Before we look at our Truth Table to see how the different combinations of values will produce results when using the AND function, we need to re-evaluate the conditions we’re checking. If we were to look at the Truth Table now, before modifying our conditions, the only combination of values that would ever result in an employee receiving a paycheck would be if the employee had actually submitted all three types of hours (must be an interesting job if an employee can work, go on vacation, and take sick leave each and every week), so let’s modify things a little before examining the table.
We know that in order for an employee to be issued a paycheck, he must first be in the system as an actual employee, so we’ll use this as our first condition to check.
| Employee |
Next, we know (based on our test with the usage of the OR function) that in order for the employee to be issued a paycheck, he must have some sort of record of time to track how many hours he is to be paid for. This is in the form of three possible records:
Regular hours
Vacation
Sick leave
Since we’re not quite at the point where we can merge the OR function and the AND function (coming shortly), let’s look at three different versions of the Truth Table that will process each type of hours tracked individually. Figures 8-45, 8-46, and 8-47 show Truth Tables for regular hours, vacation, and sick leave, respectively.
Notice in all three Truth Tables that the only time the end result is True is when both conditions are True as well:
Person is “Employee” and has worked “Regular Hours” = “TRUE” (Issue paycheck)
Person is “Employee” and has taken “Vacation” = “TRUE” (Issue paycheck)
Person is “Employee” and has submitted “Sick Leave” = “TRUE” (Issue paycheck)
In all other cases in each table, at least one of the conditions is False, so the end result is also False (the Golden Rule for the AND function is all conditions must be True for the formula to be True).
Since we’ve now looked at the basics of both the OR and AND functions, we’re going to merge the two to test for multiple conditions together. The approach we need to take is based on what we already know:
For an employee to receive a paycheck, she must be in the system. In other words, she must be an employee.
In addition to being an employee, she must have committed some sort of hours, either through the accumulation of regular hours (actually working) or through the usage or redemption of vacation or sick leave.
Notice the specific wording I’ve used in the above list: “...must be an employee,” “In addition to...,” and “...either through...”
I wrote these two items in a specific manner to illustrate that by using plain English to describe the conditions, we can do half the work for ourselves. Based on these items and how they’re written, we can immediately begin to see what we’ll need to design our Truth Table in order to account for each possible scenario.
In the first item, since I stated the person must be an employee, that suggests the usage of an AND function, since in the AND, the values must all be true, regardless of how many there are.
The second items starts with, “In addition to being an employee.” This makes the three conditions listed in this item the second part of the AND, and also suggests that we have three options to choose from to pair up with the first half of the AND (kind of sounds like an OR, doesn’t it?).
So, we basically just need to list each of the four pieces of our criteria within the Truth Table to get a set of test data based on the results of the True/False values.
In our Truth Table, following the exact same model we’ve been using, create your five columns (four conditions and one result). Your table should look like the one shown in Figure 8-48.
Upon examining this Truth Table, you should see very clearly that no matter what form of hours is submitted, if the submitter isn’t an employee, the result is always False. As long as the person is an employee, the only time the value result is False is when there aren’t any hours of any kind submitted. So, it makes sense by this table that any employee who has committed some form of hours (through working, vacation, or sick leave) will be issued a paycheck.
To simplify, as long as the person is an employee and at least one of the hours columns has a True value, the entire row is True (both golden rules have been satisfied—the rule of at least one True in the OR function results in a True value for the entire OR, which will be added to the AND rule that all values must be True).
To take what we’ve learned by using our Truth Table and convert it into a usable formula for our SharePoint list, we need to work through the logic to make sure we don’t skip any steps, which can happen when designing complicated formulas on the fly.
Since we have only a single IF function to work with (is the person an employee who has submitted any hours), using the previous approach of building out a hierarchical diagram won’t really help us too much in this case.
Instead, we’ll write out the question we’re trying to answer into plain English based on what we discovered in our Truth Table, then dissect the sentence and build out our formula.
Once again, here are our criteria:
For an employee to receive a paycheck, he must be in the system. In other words, he must be an employee.
In addition to being an employee, he must have committed some sort of hours, either through the accumulation of regular hours (actually working), or through the usage or redemption of vacation or sick leave.
Based on our two criteria, the logic that needs to occur is as follows:
| If a person is an employee and has submitted hours by working regular hours, taking vacation, and taking sick leave, or if the person is an employee and has submitted hours by working regular hours and taking vacation, or the person is an employee and has submitted hours by working regular hours and taking sick leave, or the person is an employee and has submitted hours by working regular hours, or the person is an employee and has submitted hours by taking vacation and sick leave, or the person is an employee and has submitted hours by taking vacation, or the person is an employee and has submitted hours by taking sick leave, issue him a paycheck. If not, do not issue him a paycheck. |
A bit long-winded, so let’s simplify it to the following:
| If a person is an employee and has submitted hours, either by working regular hours or by taking vacation or sick leave, issue a paycheck. If not, do not issue a paycheck. |
Since an OR function is based on one or more of its list of conditions that evaluate to true, yielding an end result of true, the above simplified version will do just fine.
Looking back at our list of criteria, the first part is simply asking the question, “Is the person an employee?” We saw this in our expanded textual walkthrough. Since this is nothing more than a basic yes/no (true/false) question, we can further simplify it by making it a single-word question:
| Employee? |
The second part of the criteria is about looking at a list of options. What form of hours was submitted? Based on our simplified sentence, we can clearly see what this list is, so we’ll grab just the list of options:
| Regular hours |
| Vacation |
| Sick leave |
Being a list of options in which at least one of them must be true, we’ll place them in an OR function:
| OR(“Regular Hours”,“Vacation”,“Sick Leave”) |
The entire OR function itself will be a mandatory check, but its contents are all optional. The only caveat is that at least one of them must return true for the OR to be true, so since the OR itself is part of the overall condition to check for (in addition to the employee check), we’ll add it and the first criteria to an AND function:
| AND(“Employee”,OR(“Regular Hours”,“Vacation”,“Sick Leave”)) |
We now have a completed formula that will work just fine, but since we haven’t yet defined just what is to occur based on the two possible results (true and false), we’ll need to place this entire formula inside an IF function to test it and decide what to do if the result is true and what to do if the result is false.
To review the format for each function, the IF functions is as follows:
| IF(logical condition, result if true, result if false) |
The OR function is written in the following form:
| OR(test1, test2, test3,...test30) |
The AND function is the same as the OR in the following form:
| AND(test1, test2, test3,...test30) |
So, as we build out each part of the overall formula, we need to start with the placeholders and replace each one with the actual values or functions to test. We’ll do this step by step as follows:
List the functions with placeholders.
IF(logical condition, result if true, result if false)
OR(test1, test2, test3,...test30)
AND(test1, test2, test3,...test30)
Start replacing placeholders with actual values.
IF(Condition,TRUE,FALSE)
OR(“Regular Hours”,“Vacation”,“Sick Leave”)
AND(“Employee”,test2)
Add the OR to the second test placeholder in the AND.
AND(“Employee”,OR(“Regular Hours”,“Vacation”,“Sick Leave”))
Add the full condition (merged “AND/OR”) to the “IF” condition placeholder.
IF(AND(“Employee”,OR(“Regular Hours”,“Vacation”,“Sick Leave”)),TRUE,FALSE)
Add the result values to the “IF” TRUE/FALSE placeholders.
IF(AND(“Employee”,OR(“Regular Hours”,“Vacation”,“Sick Leave”)),”Issue Paycheck”,”Do Not Issue Paycheck”)
Now that we’ve built our formula, we’ll move it into a list in SharePoint to test it and make sure it gives us the results we saw in our Truth Table.
In SharePoint, create a new custom list called Employee Paychecks.
Create a column called Employee, make it a Choice type, and add in the two choices of Yes and No as Radio Buttons (see Figure 8-49).
Note
I chose the Choice column rather than the Yes/No checkbox option because the Yes/No type doesn’t have a clear display as to what it really is (it doesn’t actually display the text “Yes” or “No” and can be confusing to users). Using a Choice type instead will clearly display just what the column is for and will make it more readable.
Create three additional columns, called Regular Hours, Vacation, and Sick Leave, for the three types of hours. Use Choice type and Radio Buttons (see Figure 8-50).
Since we’re using Choice columns in our list, we’ll need to add in a little bit more to our formula to test for the values that they’ll be returning. We’ll also need to change it to include our column names rather than their text equivalents (consider those as placeholders that were used prior to creating the columns).
Building our formula with the names of our columns in mind as straight text, simply replace the quotes surrounding them with brackets. I always model formulas that are based on column names with brackets added, even if the name is a single word with no spaces. This ensures that when I paste in the formula, I’ll have less chance of errors.
Here is our formula so far:
IF(AND(“Employee”,OR(“Regular Hours”,“Vacation”,“Sick Leave”)),“Issue Paycheck”,“Do Not Issue Paycheck”) When we replace the quotes to reflect column names, we get the following:
IF(AND([Employee],OR([Regular Hours],[Vacation],[Sick Leave])),“Issue Paycheck”,“Do Not Issue Paycheck”)
When we add the parts that reflect our options in the choice columns (condition=”Yes”), our formula is as follows:
=IF(AND([Employee]=“Yes”,OR([Regular Hours]=“Yes”,[Vacation]=“Yes”,[Sick Leave]=“Yes”)),“Issue Paycheck”,“Do Not Issue Paycheck”)
This will allow us to check the return text value of each column to see if it’s Yes or No (we could have used the same true/false shorthand approach as we did in the earlier examples, but I want to illustrate how you can check for any text value you want to use).
Next, we need to add in our formula, so create our final column called Issue Paycheck, make it a Calculated type with a return type of Text, and enter the modified formula (see Figure 8-51).
Now that we have all our columns created and formula added, create a series of items on the list using the same combinations of values from the Truth Table (see Figure 8-52).
Note
I’ve added four extra items to show that it will handle empty or incomplete values as well (since empty does not equal Yes, the result will be False).
Looking at the list, we can see that we did, indeed, get the expected results we saw in our Truth Table and that our formula is working through the logic exactly as we worked it out.
Obviously, this isn’t yet a complete solution for a business to use, since the list is showing nonemployees submitting accumulated hours, but it should serve as a basis for building in the extra functionality needed to get it closer. One of many things you could add in for extra functionality is to change Employee to a Choice column and add different categories of employees such as Volunteer, Full-time, or Part-time to help identify who should be getting paychecks.
Now that we’ve worked through the logic needed for this example, you may be wondering why we didn’t use the same approach as the Hungry/Thirsty/Tired/Sick list. If we had taken the same direction as the first example (detailing the IF) to test for each possible combination of values with multiple nested IF functions, the formula would have been considerably longer and would have worked, but would have been much less efficient, since it would have needed to run through each and every scenario before coming up with a result.
By using the OR and AND functions to test for combinations of values at the same time, we’ve not only lessened the development time needed to create the formula, we’ve also made debugging and testing much easier (always a good thing) and we’ve lowered the processing time needed by the system to get an answer. In a pure programmatic sense, efficiency in the logic process will make the page and list load quicker and lessen the load on the database. This isn’t always true though—sometimes it is better to take the long approach if complexity is needed, but in this case, a simple approach is far better.