Case Statement Modification Assistance

[4Justme2]

Four of the fields on one of my layouts are used in an auto-calculation function:

Age, number

ParentalStatus, text, indexed

ParticipantStatus, text, indexed

ParticipantStatusIdentification, text, indexed auto calculation Case (ParentalStatus = 1 or Age > 17 ; "Participant" ; "Child" )

The above case statement is used to automatically re-evaluate every time the Age field or ParentalStatus fields change. If the participant is a Parent or they are over the Age of 17 the ParticipantStatus field is automatically changed to “Participant”, otherwise “child”.

This works great. However, I need to make a correction and include Staff as a third category in this radio button. When Staff is picked as the ParticipantStatus I don’t want the ParticipantsStatusIndentification calc to evaluate or modify the ParticipantStatus field.

How can I modify my case statement so that when ParentStatus = 2 (Staff) nothing further happens?

[Vaughan]

All the fields are in the same table. Instead of using an auto enter, change the ParticipantStatusIdentification field to a calculation field and make it stored.

What do you mean by "nothing further happens"? I assume from your description that if parent status is staff then you want the ParticipantStatusIdentification field to display nothing...

Case(

ParentStatus = 2 ; "" ;

ParentalStatus = 1 or Age > 17 ; "Participant" ;

"Child"

)

[4Justme2]

ExampleFile.zip

All the fields are in the same table.

Yes, all one table.

Instead of using an auto enter, change the ParticipantStatusIdentification field to a calculation field and make it stored.

Changed... but I still must be doing something wrong.

What do you mean by "nothing further happens"? I assume from your description that if parent status is staff then you want the ParticipantStatusIdentification field to display nothing...

The ParticipantStatus should simply remain "Staff" (all other values should be re-evaluated every time the record has the focus).

I modified the case statement above because the line directly under Case should read "ParticipantStatus" not ParentStatus:

Case(

ParticipantStatus = 2 ; "Staff" ;

ParentalStatus = 1 or Age > 17 ; "Participant" ;

"Child"

)

But again, I must have something else wrong. I think this requires a more detailed explanation: If this can be done... I would like the enduser to be able to enter age and parental status and have the ParticipantStatus field automatically populate based on the following:

If Age > 17; ParticipantStatus should ="Participant"

Otherwise, ParticipantStatus="Child"

likewise

If ParentalStatus is unchecked and Age >17; "ParticipantStatus should automatically = "Participant"; Otherwise, ParticipantStatus="Child"

Then, once the enduser moves onto the ParticipantStatus field I would like them to be able to manually change the ParticipantStatus field to Staff when applicable.

If Staff, there's no longer a need to re-evaluate based on ParentalStatus or Age... they field value should stay Staff.

It is critical, that if the ParticipantStatus field value = "Child" the field is automatically adjusted when a person moves from Child based Age >17 or once the Child becomes a parent (regardless of age) in which case they should automatically become a "Participant" in the ParticipantStatus field. But again, the re-evaluation should not take place if the ParticipantStatus value is Staff.

I've attached a example of what I've tried...that isn't working fully in hopes you might be able to see what I'm doing wrong. Anything you can tell me would be greatly appreciated. Thanks for your time and assistance... mp

[LaRetta]

Hi 4justme2,

You have the Parental Status checkbox pointed to the Participant Status field. Respecify it to the Staff value list (which is actually your 'select 1' ). Secondly, you have:

Case(

ParticipantStatus = 2 ; "Staff" ;

ParentalStatus = 1 or Age > 17 ; "Participant" ;

"Child"

)

... but ParticipantStatus does not hold a 2 or any number. It holds values of Staff, Child or Participant so in your test file, it can't work.

Try this:

Case(

ParticipantStatus = "Staff" ; "Staff" ;

ParentalStatus = 1 or Age > 17 ; "Participant" ;

"Child"

)

[LaRetta]

"It is critical, that if they are a Child field is automatically adjusted when a person moves from Child based Age or once the Child becomes a parent (regardless of age) in which case they should automatically become a "Participant" in the ParticipantStatus field."

Also, as explained before, if we could implant the child's date of birth, it could all happen without User intervention otherwise the age (compared to today's date) will never be dependable because otherwise we CAN tell whether the child has a child because of your relationship structure).

The other thing you might consider is assigning an arbitrary birth date. If you know the child is 16 but do not know their birth date, make one up. It is better to have them turn to into a participant when they are 17 and two months than not at all.

Since you don't really know what month to put ... it doesn't matter what month you put so you can't put a wrong birth date as long as the year is correct. If you use true dates then it can be automated so they switch to Participant without touching it. You would need to use different field to flag that the record is for staff and then just include that in your calculation.

If you think this might work, let us know and we can provide alternate calc. :^)

Edited April 29, 2012 by LaRetta

[4Justme2]

You have the Parental Status checkbox pointed to the Participant Status field. Respecify it to the Staff value list (which is actually your 'select 1' ).

Good point, thank you. I've made the change to:

Case(

ParticipantStatus = "Staff" ; "Staff" ;

ParentalStatus = 1 or Age > 17 ; "Participant" ;

"Child"

)

However, with that change I'm still not getting the result I need.

The other thing you might consider is assigning an arbitrary birth date. If you know the child is 16 but do not know their birth date, make one up. It is better to have them turn to into a participant when they are 17 and two months than not at al.

I don’t anticipate a problem automating the Age field—sorry if I caused a misunderstanding. Instead of assigning an arbitrary birthdate my plan is to have an Age modification date since we aren’t always provided with a birthdate... but we'll always have an Age even if it's just an estimate. That'll be an easy enough calc for me to figure out.

My problem is with the ParticipantStatus field. For example, if they were 17 yesterday and today they are 18... The ParticipantStatus field value needs to change from Child to Participant (unless they are Staff). Same thing if they become a parent... regardless of age (but again,unless they are already flagged as Staff).

I tried the revised case statement you helped me with above but if I start with a person who is 17 flagged as a Child in the participantStatus field... and I then change their age change to 18 (which will be automated I just haven't gotten to it yet) the ParticipantStatus field doesn't automatically change from Child to Participant.

Also, if I start with a person who is 15 who is not a parent (another words their ParticipantStatus value is Child)... and then when I flag them as a parent the ParticipantStatus field should automatically change to Participant (unless they are already flagged as Staff).. but it doesn't .

Any ideas on how I can get this ParticipantStatus field to change automatically if the value doesn't already equal Staff?

.

[LaRetta]

I don’t anticipate a problem automating the Age field

I do. That is why I mentioned it.

As for your immediate situation, try this (modified file attached) :^)

ExampleFile2.zip

[4Justme2]

"Self"... that's VERY cool! I kept going round and round on this. Thank you so so much for your assistance! :yep:

[4Justme2]

I don’t anticipate a problem automating the Age field—sorry if I caused a misunderstanding. Instead of assigning an arbitrary birthdate my plan is to have an Age modification date since we aren’t always provided with a birthdate... but we'll always have an Age even if it's just an estimate. That'll be an easy enough calc for me to figure out.

I do. That is why I mentioned it.

Now I see what you mean. I did as you suggested, am having the enduser enter a birthdate based on their best guess of the person's age. I just added a check box to indicate if the birthdate is an estimate with conditional formatting to highlight the background of the field if the BD is an estimate--so it stands out. Hopefully they can get the true birthdate at some point down the road. But at least this way we have identified that the birthdate listed is only an estimate. Many thanks for the heads up--which saved me from wasting my time trying to figure out a way to do this when in all liklihood I would have ended up basing my calc on the birthdate eventually... mp

[4Justme2]

I'm wondering if there's a way I can automate the process of having them calculate an estimated birthdate (sort of a birthdate calculator--but in reverse)? They would plug in the estimate of age into some field, and based on the current date the calculator would produce a birthdate for them so they don't have to do it manually. Any ideas on that?

AgeEstimate 29

CurrentDate 5/2/12

EstimateBirtdate = 5/2/83

[comment]

Perhaps this way?

ForceAge.fp7.zip

[4Justme2]

I'm thrilled with this!!! Thank you...Thank you... :yep: