Next Steps: The List

Our list is just a custom list with three additional columns:

1. “Hungry” column – “Choice” type (“True” and “False” for the choices), “Radio Buttons”, no default value.
2. “Thirsty” column – “Choice” type (“True” and “False” for the choices), “Radio Buttons”, no default value.
3. “Result” column – “Calculated” type, all default values with the following formula:
   =IF(Hungry,IF(Thirsty,”Eat and Drink”,”Eat”),”Don’t Eat”)

Adding four items to the list following the pattern laid out in the above “Truth Table”:

We can also see that in its current form, the formula does not take in account for all possible scenarios (note the last two items on the list).

Now, let’s go back in and modify our formula to include our change of checking the “Thirsty” side of things along with “Hungry”.

In our “Results” column formula, modify it to be:

=IF(Hungry,IF(Thirsty,”Eat and Drink”,”Eat”),IF(Thirsty,”Drink”,”Don’t Eat or Drink”))

Once saved, look back at our existing list items.

(Again, note the last two items on the list – they now display accurate results)

Because we’re now taking in account for each possible scenario, our formula will display all of the possible answers based on the choices made.

As mentioned at the beginning of this article, as we add in more checks, our formula will grow rather large and complex, an in some cases, to the point where it’s almost impossible to visually inspect it to find problems.

So if we were to add in an additional question of “Am I Tired?” to the mix, our formula logic expands out to:

Is it true that I am hungry?

If so, then is it true that I am thirsty?

If so, then is it true that I am Tired?

If so, I will eat and drink and sleep.

If not, I will eat and drink.

If it is not true that I am thirsty, is it true that I am tired?

If so, I will eat and sleep.

If not, I will eat.

If it is not true that I am hungry, is it true that I am thirsty?

If so, then is it true that I am tired?

If so, I will drink and sleep.

If not, I will drink.

If it is not true that I am thirsty, is it true that I am tired?

If so, I will sleep.

If not, I will not eat or drink or sleep.

The formula for this becomes:

=IF(Hungry,IF(Thirsty,IF(Tired,”Eat and Drink and Sleep”,”Eat and Drink”),IF(Tired,”Eat and Sleep”,”Eat”)),IF(Thirsty,IF(Tired,”Drink then Sleep”,”Drink”),IF(Tired,”Sleep”,”Don’t Eat or Drink or Sleep”)))

Notice the three beginning “IF” statements:

IF(Hungry, IF(Thirsty,IF(Tired…

This literally translates to: “If I’m hungry, then if I’m thirsty, then if I’m tired…” then progresses on – based on the results of each.

As complicated as it now appears, the formula is still doing the exact same process as it was before, just with an additional check along the way.

If we now look at the “Truth Table” for this, we can see the extra growth in the list of possible answers:

Anyone notice a pattern forming in the table?

In the third “Question” column, the “TRUE / FALSE” values are alternating every-other row, whereas in the second “Question” column they’re alternating every-two rows…and the first “Question “column is alternating every-four rows.

If we were to add in yet another condition (which we will, momentarily), can you guess what the pattern would look like?

If you guessed column one would alternate every-eight rows, you’d be correct!!

This is part of what makes Truth Tables such a valuable tool when prototyping or working-out the logic beforehand…they seem a bit daunting at first, but once you figure out the pattern, you can write them rather quickly then translate it into a basic flowchart to see the path the logic needs to process through. Once you have this, you will also have the basics or “Root” of the formula you need to build to move into SharePoint.

Tomorrow we will look at our formulas and the complex layers we can add. Stay tuned…

- Dessie

Dessie Lunsford
Points-of-Sharing