The Logic of Flowcharts

If you’re familiar with Visio, or similar technical drawing programs, start by creating our first Question at the very top (this is the entrance point, or start, of our logic), and add in “Placeholders” for both the “TRUE” and “FALSE” results:

A “Placeholder” is a temporary value that will be replaced later.  Since we don’t yet know what our final result will be for either the “TRUE” or “FALSE” values, we just enter the text for the “Logical Value” in as a temporary marker which we’ll replace once we know the actual value to put in its place.

Next, add in the second question (“Thirsty”) to both the “TRUE” and “FALSE” placeholders from question 1 (literally replace the text “Result if TRUE” and “Result if FALSE” with the second question), then add in placeholders for the “TRUE” and “FASLE” results for question 2:

Continue this process by adding in the third question to each of the “TRUE” and “FALSE” placeholders of the second question, and then add the fourth question to each of the “TRUE” and “FALSE” placeholders of the third question.

Once completed, you’ll have 16 “TRUE / FASLE” boxes at the very bottom, which (coincidence?) matches up with the total rows in our “Truth Table”.

The completed hierarchical diagram (logic flowchart), should now appear similar to (Note – I’ve added in the actual values for the “TRUE / FALSE” results of question 4 since it’s the last question to be asked in the logic):

(Click on image to view full-size)

Translating the Logic

Ok, so this is all good and fine…we’ve created a nice table of all possible results and have this neat diagram, but what the heck are we supposed to do with it to help us create the formula for our calculated column in SharePoint?

Believe it or not, the above flowchart is a literal translation of our exact formula.

The manner in which we achieve this translation is as follows.

The logic of our formula (with all four questions included), works 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, then is it true that I am sick?
If so, I will eat and drink and sleep, I’m sick.
If not, I will eat and drink, and sleep.
If it is not true that I am tired, is it true that I am sick?
If so, I will eat and drink, I’m sick.
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, is it true that I am sick?
If so, I will eat and sleep, I’m sick.
If so, I will eat and sleep.
If it is not true that I am tired
Is it true that I am sick?
If so, I will eat, I’m sick.
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, then is it true that I am sick?
If so, I will drink and sleep, I’m sick.
If not, I will drink and sleep.
If it is not true that I am tired, is it true that I am sick?
If so, I will drink, I’m sick.
If not, I will drink.
If it is not true that I am thirsty, is it true that I am tired?
If so, is it true that I am sick?
If so, I will sleep, I’m sick.
If not, I will sleep.
If it is not true that I am tired, is it true that I am sick?
If so, I’m sick.
If not, don’t eat or drink or sleep, I’m not sick.

In our hierarchical flowchart diagram, we need to add in the actual “Formula Text” for each of our questions (the “IF(condition,true,false)” pieces), so for each “TRUE / FALSE” value where we currently have our questions listed, add in a simple “IF” statement in using the same manner in which you’d enter it into SharePoint (or Excel):

Question 1 would be: IF(Hungry,True,False)
Question 2 would be: IF(Thirsty,True,False)
Question 3 would be: IF(Tired,True,False)
Question 4 would be: IF(Sick,True,False)

When completed you should have something similar to:

(Click on image to view full-size)

Now, look at the diagram and observe where each piece is at.  Since we’ve just added in the text for our formulas into each question, we have a bunch of “TRUE” and “FALSE” placeholders throughout the entire diagram.

Each of these placeholders will be housing the child branch directly beneath it, so we need to start moving things around in order to progressively build out our final formula.

The next step is to simply match up each box on the chart to its immediate parent in the correct placeholder (TRUE / FALSE) position.

Starting from the extreme bottom-left of the chart:

Take the result text from the “TRUE” value below “Question 4” and paste it in place of the literal text “TRUE”, in “Question 4” directly above it.

This turns into:

Take the result text from the “FALSE” value below “Question 4” and paste it in place of the literal text “FALSE” in “Question 4” directly above it.

Resulting in:

Continue this pattern moving to the right for each “TRUE” and “FALSE” result of the 16 total “Question 4” parts and moving them to the “Question 4” placeholder directly above. 

When completed, you should have 8 different versions of “Question 4” – each with different values in their “TRUE” and “FALSE” results.

(Click on image to view full-size)

Our next step is to do the exact same process again of copying each result back up one level to the placeholders (“TRUE / FALSE”) for question 3.

Copy the “IF…” statement of each of our 8 “Question 4” boxes and move them to their immediate parent’s “TRUE/FALSE” placeholders.

As you move through each of the 8 question 4’s and move each of their formulas up to their parent “Question 3” true/false placeholders, you should be able to start seeing the final formula coming together:

Perform this exact same process again by moving each of the versions of “Question 3” into its parent “Question 2”, then one last time by moving the 2 versions of “Question 2” back up to the top-level “Question1” placeholders (“TRUE / FALSE”).

When completed, in the top-level question (our entry point to the logic), you will have your final formula of:

=IF(Hungry,IF(Thirsty,IF(Tired,IF(Sick,"Eat and Drink and Sleep, I’m Sick","Eat and Drink and Sleep"),IF(Sick,"Eat and Drink, I’m Sick","Eat and Drink")),IF(Tired,IF(Sick,"Eat and Sleep, I’m Sick","Eat and Sleep"),IF(Sick,"Eat I’m Sick","Eat"))),IF(Thirsty,IF(Tired,IF(Sick,"Drink and Sleep, I’m Sick","Drink and Sleep"),IF(Sick,"Drink, I’m Sick","Drink")),IF(Tired,IF(Sick,"Sleep, I’m Sick","Sleep"),IF(Sick,"I’m Sick","Don’t Eat or Drink or Sleep, I’m not Sick"))))

Neat huh?  You now have a complete formula that includes every step of the logic needed to ask each question in a manner that allows for each possible answer.

The process I’ve detailed does take a bit of time to walk through each of the steps I admit, but the end result is a complete formula without errors…and you get the benefit of not having to figure out where all the closing parentheses go (one of the most troublesome parts of working with complex nested formulas).

To test that our formula indeed is correct, we’ll move to our list in SharePoint where we can see if our expected results from our “Truth Table” match up.

In our final installment tomorrow, we’ll look at the resuls of all our hard work…The Final List.

- Dessie

Dessie Lunsford
Points-of-Sharing