Logic – The “Plain” Truth

In order 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 in order to make sure we don’t skip any steps (which can happen when designing complicated formulas “on-the-fly”).

In the previous series on “Logic Functions”, we used a nested “Hierarchical” diagram to accomplish this since all we were working with were a series of nested “IF” statements (“If this, do this, if not do that”).  Although we could take this same approach to detail the logic this time, it wouldn’t really help us too much since there’s only one “IF” in our problem.

Instead, we’ll write out the question we’re trying to solve into plain English based on what we discovered in our “Truth Table”, then dissect the sentence and build out our formula.

The Question

Once again, our criteria:

1. In order for an employee to be able to receive a paycheck, the must be in the system – in other words, they must be an “Employee”.
2. In addition to being an “Employee”, they must also 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 items, the logic that needs to occur is:

“If a person is an employee and they’ve submitted hours by working regular hours, taking vacation, and taking sick leave, or if the person is an employee and they’ve submitted hours by working regular hours and taking vacation, or they person is an employee and they’ve submitted hours by working regular hours and taking sick leave, or the person is an employee and they’ve submitted hours by working regular hours, or the person is an employee and they’ve submitted hours by taking vacation and sick leave, or the person is an employee and they’ve submitted hours by taking vacation, or the person is an employee and they’ve submitted hours by taking sick leave, issue them a paycheck.  If not, do not issue them a paycheck.”

A bit long-winded, so to simplify:

“If a person is an employee and they’ve submitted hours, either by working regular hours, taking vacation or sick leave, issue them a paycheck.  If not, do not issue them 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 and Employee?”  We saw this in our expanded “textual-walkthrough”.  Since this is nothing more than a basic “Yes/No” (“True/False”) question, we could 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” in order for the “OR” to be “True”, so since the “OR” itself is part of the overall condition to check for (it 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 in all actuality 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 in order to test it and decide what to do if “True” and what to do if “False”.

IF(AND(“Employee”, OR(“Regular Hours”,“Vacation”,“Sick Leave”)),TRUE,FALSE)

Based on our criteria, this becomes:

“If a person is an employee and they’ve submitted hours, either by working regular hours, taking vacation or sick leave, issue them a paycheck.  If not, do not issue them a paycheck.”

Adding in the “TRUE” and “FALSE” part of the formula:

IF(AND(“Employee”, OR(“Regular Hours”,“Vacation”,“Sick Leave”)),”Issue Paycheck”,”Do Not Issue Paycheck”)

Now that we have our formula, we’ll move it into our list in SharePoint to test it and make sure it gives us the results we saw in our “Truth Table”.

Next time we’ll create our list and test it.

- Dessie

Dessie Lunsford
Points-of-Sharing