Our Final List

In SharePoint create a 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”. 

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).  Using a “Choice” type instead, will clearly display just what the column is for and will make it more readable for the users.

Create three additional columns for the 3 types of hours all in the same fashion as the “Employee” column (“Choice” type, “Yes” “No” choices, “Radio Buttons”).

Column titles: “Regular Hours”, “Vacation”, “Sick Leave”

Since we’re using “Choice” columns in our list, we’ll need to add in a little bit more to our formula in order to test for the values that they’ll be returning.

Formula so far:

=IF(AND([Employee], OR([Regular Hours],[Vacation],[Sick Leave])),"Issue Paycheck","Do Not Issue Paycheck")

Change to:

=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 either “Yes” or “No”.

Create our final column called “Issue Paycheck” make it a “Calculated” type with a return type of “Text” and enter in the modified formula.

=IF(AND([Employee]="Yes",OR([Regular Hours]="Yes",[Vacation]="Yes",[Sick Leave]="Yes")),"Issue Paycheck","Do Not Issue Paycheck")

Once you have all columns created, enter in a series of items on the list using the same combinations of values from the “Truth Table”.

NOTE – I’ve also added in a couple extra items to show that it will handle empty values as well (since empty does not equal “Yes”, the result will be false).

Conclusion

If we had taken the same direction as in the previous series (detailing the “IF”) to test for each possible combination of values with multiple nested “IF” functions, the formula would have been considerable longer, and would have worked, but would have been much less efficient. 

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 need to create the formula, we’ve also made debugging and testing much easier (always a good thing).

Also, for those that may be wondering why we didn’t just use a single “Choice” column to house each of the options for hours, the reason is because SharePoint cannot handle “Multiple Select” values.  Since there isn’t an “Array” function to deal with dynamic data types (those that aren’t consistent in length), a calculated column will not be able to reference a column that (can) return more than a single value.  We could use a single Choice column, but it would defeat the logic we’re trying to accomplish since the calculation wouldn’t be able to see “more than one” choice…it would only see one.

Till next time…

- Dessie

Dessie Lunsford
Points-of-Sharing