1,804 articles and 14,932 comments as of Wednesday, May 18th, 2011

EndUserSharePoint has combined resources with NothingButSharePoint.com. You can now find End User (Mark Miller), Developer (Jeremy Thake) and IT Pro SharePoint content all in one place!

This site is a historical archive and is no longer being updated. Please update your favorites, bookmarks and RSS feeds.

NothingButSharePoint.com
Friday, May 8, 2009

Taming the Elusive “Calculated Column” – Logic – Part 8

The Bigger “Truth” (table)

Now that we’ve looked at how to trace down the logic of an “OR” function and that of an “AND” function, we’re going to merge the two in order to be able to test for multiple conditions together.

The approach we need to take is based on what we already know:

  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”.

Notice the specific wording I’ve used in the above list – “…must be and Employee”, “In addition…”, “…either throughoror…”

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 in our “Truth Table” in order to account for each possible scenario.

In the first item, since I stated that they “must be an Employee”, that would suggest the usage of an “AND” function since in the “AND”, the values must all be “TRUE” regardless of how many there are.

In the second item, it starts out with “In addition to being an Employee” – this would make whatever of the three conditions listed in this item as the second part of the “AND”, and also would suggest that we have three options in which to choose from to pair up with the first half of the “AND” (kind’ve sounds like an “OR” doesn’t it?).

So, we basically just need to list out each of the four pieces of our criteria within the “Truth Table” to give us 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 5 columns (4 conditions and 1 result). 

Calculated Column Part 8

Examining the table, we should see very clearly, that no matter what form of hours was looked at, if they weren’t and employee, the result was always “FALSE”.  Along with this, as long as they were indeed an employee, the only time the value resulted in “FALSE” was when there weren’t any hours of any kind.  So, it would make sense by this table that any employee who has committed some form of hours (through working, vacation, or sick leave) will get issued a paycheck.

Next time we’ll begin modeling our formula.

- Dessie

Dessie LunsfordDessie Lunsford
Points-of-Sharing

View all entries in this series: Dessie Lunsford-Logic Formulas»
Entries in this series:
  1. Taming the Elusive "Calculated Column"
  2. EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Task List
  3. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part II)
  4. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part III)
  5. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part IV)
  6. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Text and Data
  7. EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Contacts List (Part I)
  8. EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Contacts List (Part II-I)
  9. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Contacts List (Part II-II)
  10. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Contacts List (Part II-III)
  11. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part III)
  12. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part IV)
  13. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part V)
  14. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part VI)
  15. Taming the Elusive “Calculated Column” – Lookup and Reference
  16. Taming the Elusive “Calculated Column” - Logic
  17. Taming the Elusive “Calculated Column” - Logic - Part 1
  18. Taming the Elusive “Calculated Column” - Logic - Part 2
  19. Taming the Elusive “Calculated Column” - Logic - Part 3
  20. Taming the Elusive “Calculated Column” - Logic - Part 4
  21. Taming the Elusive “Calculated Column” - Logic - Part 5
  22. Taming the Elusive “Calculated Column” - Logic - Part 7
  23. Taming the Elusive “Calculated Column” - Logic - Part 6
  24. Taming the Elusive “Calculated Column” - Logic - Part 8
  25. Taming the Elusive “Calculated Column” - Logic - Part 9
  26. Taming the Elusive “Calculated Column” - Logic - Part 10
  27. Taming the Elusive “Calculated Column” - Date and Time - Part 1
  28. Taming the Elusive “Calculated Column” - Date and Time - Part 2
  29. Taming the Elusive “Calculated Column” - Date and Time - Part 3
  30. Taming the Elusive “Calculated Column” - Date and Time - Part 4
  31. Taming the Elusive “Calculated Column” - Referencing Multiple Lines of Text Column
  32. Taming the Elusive “Calculated Column” – Date and Time – Part 5
  33. Taming the Elusive “Calculated Column” – Date and Time – Part 6
  34. Taming the Elusive “Calculated Column” – Date and Time – Part 7
  35. Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 1
  36. Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 2
  37. Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 3
 

Please Join the Discussion

3 Responses to “Taming the Elusive “Calculated Column” – Logic – Part 8”

Trackbacks

Check out what others are saying about this post...
  1. [...] Taming the Elusive “Calculated Column” – Logic – Part 8 [...]

  2. [...] Taming the Elusive “Calculated Column” – Logic – Part 8 [...]




Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!