A READER ASKS: FileMaker Validation For Year

© 2010 Dwayne Wright - dwaynewright.com

From Dwayne Wright PMP - Certified FileMaker 10, 9 & 8 Developer
EMAIL: info@dwaynewright.com     TWITTER: dwaynewright

APPENDIX 1: A Reader Asks

A READER ASKS
I am fairly new to FM and am trying to make a solution for my Church.

I have used many of your recourses to get started. Thanks so much for them!

I want to set up validation of a date field so that dates entered can only be of a certain year (using a drop down calendar).

The “In Range” works but the date restriction changes from year-to-year and I don’t always want to have to edit that fields properties each year, but get the restriction from a field on the database.

I am trying to use the validate by calculation, but I can’t seem to find a calculation that will work.

-------
DWAYNE RESPONDS
Try using the Year function within your calculation. The year function is found in the Date family of functions and it extracts the year information only from a properly formatted date string in a date field or text field wrapped properly with the GetAsDate function.

For example, the information of 6/1/98 would return 1998 only or 3/17/2010 would simply return 2010. So when you exit the field, the validation would simply check to make sure the year entered equals some other year you specify (either hard coded, based upon another field or via a calculation).

Many times, the year function is used in conjunction with other function as part of a nested function. For example, Get(CurrentDate) returns the current date but what if you just want the current year? Year(Get(CurrentDate) would do the trick.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2010 - Dwayne Wright - dwaynewright.com

A READER ASKS: Misbehaving FileMaker Calculations

From Dwayne Wright - Certified FileMaker 10 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

APPENDIX 1: A Reader Asks

A READER ASKS
Sometimes an employees’ total number of hours worked is calculated wrong. It’s a simple adding calculation, so I’m at a loss as to how it could be getting confused. Do you have any insight? What would we need to know in order to resolve the issue?

-------
DWAYNE RESPONDS
I wish I could hand out a “silver bullet” to slay monsters such as this when the arise. Unfortunately, you have to put on your detective hat and ask a lot of questions.

Generally I start off with ... is the calculation using related information? If the related information isn’t committed, then a calculation may not show up accurately. In the past, I’ve seen some conditions where related data doesn’t refresh a calculation properly (but not so much with the current versions of FileMaker).

In the same neighborhood, if the calculation is using related values, your relationships might be the culprit. Remember that you may have to take the context of the layout and the calculation into consideration when dealing with calculations that have related values within them.

Bad or incomplete data entry can be another contributing factor. In this case, data entry validation in key areas might help keep your foundation data intact.

Mixing data that is in different formats can sometimes hurt you. I’ve seen cases where data is saved in one format (text, number, date, time) and the calculation is in another format. This mix and match situations can cause problems.

Hope this helps!
Dwayne

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.

FILEMAKER: PERT Activity Variance Custom Function

From Dwayne Wright - Certified FileMaker 10 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

CHAPTER 20: Custom Functions

In a previous post (My ThreePoint Custom Function), I created a custom function for the three point estimation used by PMI known as PERT. PERT standing for Program Evaluation and Review Technique developed by the Navy in the 1950s.

You can determine what is called the PERT variance for each activity by using the formula of ...

((Pessimistic - Optimistic) / 6) ^ 2.

Here is how it would breakdown for a pair of activities at the following proposed duration days.

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.

FILEMAKER: PERT Standard Deviation Custom Function

From Dwayne Wright - Certified FileMaker 10 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

CHAPTER 20: Custom Functions

In a previous post (My ThreePoint Custom Function), I created a custom function for the three point estimation used by PMI known as PERT. PERT standing for Program Evaluation and Review Technique developed by the Navy in the 1950s.

You can determine what is called the PERT standard deviation for each activity by using the formula of ...

(Pessimistic - Optimistic) / 6.

So I created a new custom function for this and it is even more simple than the three point estimate.

Here is how it would breakdown for a pair of activities at the following proposed duration days.

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.

FILEMAKER: My ThreePoint Custom Function

From Dwayne Wright - Certified FileMaker 10 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

CHAPTER 20: Custom Functions

So my big 2010 challenge is getting my PMP (project management professional) certification. This is all part of a “OMG the economy is in the ditches and I only have FileMaker skills’ neurosis and leading to my attempts to build a career backup plan. This posting isn’t about that but just some header info (because I’ll probably be writing a lot about project management calculation functions in the future).

Anyhoo ...

THREE POINT ESTIMATES
One of the blessed expected value estimation calculation formulas for the PMP is the three point estimate. What it does is take three estimation points and gives you a most likely result. It can be used to calculate time for schedules or money for budget estimates.

Here you can see three values and their three point result.

Here is a probably a better illustration. Say that a client is asking for an estimate of hours to do a project task. You have done this type of work before and have code you can apply to it. You feel pretty confident that you can do the job in 50 hours and perhaps as low as 40. You want to throw in a higher estimate because you might not have all the deliverable scope because the client isn’t that sure about their definitive needs. So you put in a high estimate of 120 hours into the mix.

You could tell the client that it could take anywhere from 40 to 120 hours. This might be a way to get them to better define their scope to you. It also may be a way to ensure that you never hear from the client again. A three point estimate says that if you give them an estimate of 60, you are pretty well covered.

THE USE OF THREE POINT ESTIMATES
Now every project is unique and no formula is bullet proof but this is one of them that is blessed by the Project Management Institute for PMP certification. Whether or not you are going to be giving the results of the three point estimate to your clients, it might be a worthwhile practice to track it internally. In fact, you could go more granular in this area and have three point estimates for each work package. That way you can refine your estimation skills and build your library of development related organizational process assets!

ABOUT THE CUSTOM FUNCTION
The custom function is ruthlessly simple. We take ...

- the most likely estimate times four
- add to that the optimistic estimate
- add to that the pessimistic estimate
- divide that total by six

Here is the custom function itself in living black and white!

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.