13 Replies Latest reply on Jun 12, 2014 7:30 AM by philmodjunk

    Need help on Dates issue

    Sunny1

      Title

      Need help on Dates issue

      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.

        • 1. Re: Need help on Dates issue
          philmodjunk

               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" )

          • 2. Re: Need help on Dates issue
            Sunny1

                 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.

                  

                  

            • 3. Re: Need help on Dates issue
              philmodjunk
                   

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

              • 4. Re: Need help on Dates issue
                Sunny1

                     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?

                      

                • 5. Re: Need help on Dates issue
                  philmodjunk

                       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.

                  • 6. Re: Need help on Dates issue
                    Sunny1

                         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?

                    • 7. Re: Need help on Dates issue
                      philmodjunk

                           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.

                      • 8. Re: Need help on Dates issue
                        Sunny1

                             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.

                        • 9. Re: Need help on Dates issue
                          Sunny1

                               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.

                                

                                

                          • 10. Re: Need help on Dates issue
                            philmodjunk

                                 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.

                            • 11. Re: Need help on Dates issue
                              Sunny1

                                   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. 

                              • 12. Re: Need help on Dates issue
                                Sunny1

                                     Did I enter something incorrectly because I can't get it to work.

                                      

                                • 13. Re: Need help on Dates issue
                                  philmodjunk

                                       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.