Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Need help on Dates issue

Question asked by Sunny1 on Mar 31, 2014
Latest reply on Jun 12, 2014 by philmodjunk

Like • Show 0 Likes0
Comment • 13

Title

Post

I have Filemaker Pro 12 for MAC. Currently, in the database I am working on, there is a field for range of dates for when students go to camp. It has a beginning and ending within the same field. I will want to make into two different fields Beginning Date and Ending date. When I have a student whose birthdate happens to fall within the time they are at camp, I want an X placed in the Birthday field so that our counselors know to have a celebration for that student.

Do you agree that the Week field must be split into two separate fields? And if so, how to write it to put X in Birthday field.

Thank you in advance.

No one else has this question

Outcomes

13 Replies

philmodjunk Mar 31, 2014 3:58 PM
Mark Correct
Correct Answer
     Yes it should be two fields of type date. There should also be a date field or a calculation that returns a date for the camper's birthday.

     Your calculation for Birthday might be:

     If ( StartDate < Birthday and EndDate > Birthday ; "x" )
Like • Show 0 Likes0
Actions
Sunny1 May 29, 2014 3:45 PM
Mark Correct
Correct Answer
     Hello Again.

     I have another person here at our office helping with improving our Outdoor Education database and we had been using the above calculation to get an X to be placed in a field if a student's birthday falls within the week they are at Outdoor Education and it was working fine. The fields of the Beginning and Ending week of Outdoor Education got changed from TEXT to DATE format and now the calculation does not work. One other problem we were seeing is that when we import the school information from EXCEL, if a student's birthday is 12/01/03 in the spreadsheet, it would come over as 12/1/2003. So if the student is at outdoor ed during the week of 12/09/14 to 12/13/14, it would put an X in the Birthday Party field, which is incorrect. So now, we cannot the format to be the same as mm/dd/yy as in the EXCEL spreadsheet, it does not put an X in the Birthday Party field and it acknowledges incorrect birthdays because of the format of date. We tried changing it back to how it was previously but

     So, I don't know how to fix it or what changed it to be totally not working now. Also, is there a way to write a calculation to reformat the date to be mm/dd/yy?

     Thank you in advance.
Like • Show 0 Likes0
Actions
philmodjunk May 29, 2014 4:45 PM
Mark Correct
Correct Answer

          The fields of the Beginning and Ending week of Outdoor Education got changed from TEXT to DATE format and now the calculation does not work.

     That makes no sense. The fields should be of type Date and I would not expect them to work if they are of type text. If these dates are stored as type date, the display format does not affect how the date evaluates. (Dates are stored as integers counting the number of days from 12/31/000 to the date shown in the date field so they evaluate the same regardless of the display format.)
Like • Show 0 Likes0
Actions
Sunny1 Jun 11, 2014 11:11 AM
Mark Correct
Correct Answer
     Hello Again,

     I have this field set up but I need for the calculation formula to only look at month and day because if inadvertently someone enters current year as birthdate, it puts an X in the Birthdate field. Can you revise the formula to only look at month and day?
Like • Show 0 Likes0
Actions
philmodjunk Jun 11, 2014 11:18 AM
Mark Correct
Correct Answer
For some reason, you posted a question and then closed the thread to block posting comments which would prevent anyone from answering your question. Fortunately, as community leader, I can re-open a closed thread.

It's better to prevent a data entry error than to modify code to handle the error. I'd put a validation rule on the birthdate field that rejects dates where the year is this year.
Like • Show 0 Likes0
Actions
Sunny1 Jun 11, 2014 11:26 AM
Mark Correct
Correct Answer
if the current calculation reads: If (SMCOE_OUT_Week_Number::OE DatesBeg ≤ Birthdate and SMCOE_OUT_Week_Number::OE DatesEnd ≥ Birthdate ; "X"), how do I add a validation to it to not allow current year?
Like • Show 0 Likes0
Actions
philmodjunk Jun 11, 2014 11:33 AM
Mark Correct
Correct Answer
     The validation would be added to the BirthDate year, not this calculation.

     Year ( self ) < Year ( Get ( CurrentDate ) )

     This would be added via the calculation option found on the validation tab in field options for the BirthDate field.
Like • Show 0 Likes0
Actions
Sunny1 Jun 11, 2014 11:54 AM
Mark Correct
Correct Answer
Now I screwed something up. I added that exact phrasing to the Birthdate field under Calculation and it wiped out the birthdate entirely. I am just lost on this thing.
Like • Show 0 Likes0
Actions
Sunny1 Jun 11, 2014 12:53 PM
Mark Correct
Correct Answer
     We are dealing with two fields here: One that holds the student birthdate (birthdate) and one that puts the X in if their birthdate falls in the week they are at Outdoor Ed (Birthday). Which one gets this calculation written into it? I am confused! Right now, Birthday has the calculation of putting the X in that field if the Birthdate falls between OE Beginning Date and OE Ending Date. So, I am guessing from your previous instruction that the new formula goes in the calculated area for Birthdate? If so, that took away all my actual birthdate info. I must have written something wrong.
Like • Show 0 Likes0
Actions
philmodjunk Jun 11, 2014 1:29 PM
Mark Correct
Correct Answer
This is a VALIDATION field option, NOT an AUTO-ENTER option.

Make sure that you select the calculation option on the Validation tab, not the auto-enter tab.
Like • Show 0 Likes0
Actions
Sunny1 Jun 11, 2014 3:14 PM
Mark Correct
Correct Answer
Okay I see where you are talking about. So I put that exact formula in that area, and it is not working. Is the word SELF supposed to be there or is that a reference for the field name?

Currently, I have a calculation formula telling Birthday to place an X in the field if Birthdate is between OutDoor Ed Beginning and Ending Dates. Then I have a validation calculation in Birthdate saying that Birthdate = Year ( Self ) < Year ( Get ( CurrentDate ) ). I don't see where this formula refers to the beginning or ending dates of the outdoor ed week.
Like • Show 0 Likes0
Actions
Sunny1 Jun 11, 2014 6:48 PM
Mark Correct
Correct Answer
Did I enter something incorrectly because I can't get it to work.
Like • Show 0 Likes0
Actions
philmodjunk Jun 12, 2014 7:30 AM
Mark Correct
Correct Answer
     You appear to be confusing which field needs the auto-enter calculation. You have a calculation that returns an "X" if the camper's birthday falls on the current week. You have a date field that stores the camper's birthdate. I am suggesting that you leave the calculation field unchanged, but to find the Birthdate field in Manage | Database | Fields, double click it to open Field Options and enter the suggested validation field option on the Birthdate field.

     You should enter it exactly as written. "Self" is a term you can use in calculations to refer to the current field without having to explicitly name it. If you replace self with the name of your field, the calculation evaluates exactly the same.

     But what may also be a source of confusion is that this validation rule only kicks in when you enter or edit data in the birthdate field by popping up an error message that asks the user to fix the error. If you have existing records with the wrong year already entered into the database, this validation rule will have no effect on them unless you edit those fields in some way.

     But note that you can easily perform a find for all records where the year is this year, or just a few years earlier than today--thus finding all records where the camper's age is impossibly young.
Like • Show 0 Likes0
Actions

Need help on Dates issue

Title

Post

Outcomes

Related Content

Recommended Content