9 Replies Latest reply on Apr 29, 2013 5:34 AM by SJCPS

    Find records with birthdays in a range

    JonXS

      Title

      Find records with birthdays in a range

      Post

      I have a database with a table which includes Date of Birth as a field (date formatted - DD/MM/YYYY).

      Is there a way to use the Date Of Birth field as a basis for a script to find records in which a birthday occurs within a date range, say a month (ie. to find everyone who has a birthday occuring next month)?

        • 1. Re: Find records with birthdays in a range
          LaRetta_1
          You can search using wild cards and spans, for example, searching for 6 in a date field (US settings) finds all records for June no matter the day or year.  See FM Help fot examples finding dates.  :-)
          • 2. Re: Find records with birthdays in a range
            SJCPS

                 I been searching the forum for help as a newbi - did this question get answered? if yes what was the solution please - thanks.

            • 3. Re: Find records with birthdays in a range
              LaRetta_1

                   Hi, what was you exact need?  If you want to find all birthdays in an upcoming month (say April) you would search the birthday field for

                   4

                   Or if you prefer to roll your own ... 4/*/*

                   If you provide different example of your need, we can help you with it.  :^)

              • 4. Re: Find records with birthdays in a range
                SJCPS

                     FM Pro 12

                     Hi LaRetta, thanks for helpful and quick reply. I’ve got a DB of school pupils and want all the records that have birthdays in a particular week. Maybe just turning the record red or a simple ‘B’ on the record in list view would be ok.

                     I have seen  this in the KB but not quite clear how to implement it

                     Also seen this

                     However your suggestion seems simple enough to implement and I will have a go. If you know how I can use the KB examples it would be appreciated. Thanks again  

                      

                • 5. Re: Find records with birthdays in a range
                  SJCPS

                       FM12 Pro

                       I am really struggling with setting up a way to sort & find birthdays of all the records. I have received allot of help but still cannot get it to work.

                       I have 2 date fields Birthday from and to I also have a date field called DOB. Is there a simple tutorial I can follow or a video somewhere? I am very new to FM and all the KB are way above me to follow.

                       Thanks

                        

                  • 6. Re: Find records with birthdays in a range
                    LaRetta_1

                         Why two additional date fields?  Are they global for searching a range or are they for User to enter number of days ahead to display birthday?  Do you need them if you just want to highlight upcoming birthdays in a list?  If you need to search or sort, the  easiest thing to do is create a calculation with:  Date ( Month ( DOB ) ; Day ( DOB ) ; 4 )

                         This normalizes all your birthdays.  Why 4?  Because that was the first leap year.  But if you need to just highlight on an attendance list those students with birthdays within the next 7 days, you can use conditional formatting.  Formula might be: 

                         Let (
                         thisDOB = Date ( Month ( DOB ) ; Day ( DOB) ; Year ( Get ( CurrentDate ) ) ) ;
                         thisDOB ≥  Get ( CurrentDate )
                         and
                         thisDOB < Get ( CurrentDate ) + 7 )
                         )

                         BTW, I suggest against using calculations you find on KB.  Most are out-dated, inefficient and some are even wrong.  So if you pin down your requirements,  we will help you achieve them.  :^)

                    • 7. Re: Find records with birthdays in a range
                      SJCPS

                           Hi LaRetta, good of you to reply and help out and stop me from going mad. I am sure the answer is quite straightforward. Here is my setup put very simply. I have used the tutorial data “Contacts” and modified it to suite. I have a two global date fields; one for setting Birthdays From and the other Birthdays To. I simply want to enter a date in the ‘From’ field and a future date in the ‘To’ field. Click a button and my list would sort all the birthday in that range.

                           I have entered Date ( Month ( DOB ) ; Day ( DOB ) ; 4 ) in a calculation but can’t get it to work.

                           Please remind me, should I place the calculation in a date field or a text field? Where can I find an explanation of the syntax?   

                      • 8. Re: Find records with birthdays in a range
                        LaRetta_1

                             Well if you are searching ranges using two global date fields then change the calculation (prior example was good for portals)

                             Date ( Month ( DOB ) ; Day ( DOB) ; Year ( Get ( CurrentDate ) ) )

                             called cThisYearBD and it should be type calculation set to date result and in storage options, select 'do not store calculation results' so it updates as the current date changes.  This will not be a fast search since it is unstored but it should *suffice.  There are four functions here:  Date(), Month(), Day() and Year() and you can read about them - they are all date functions.

                             Now since the birthdays are normalized, let's write the script which would be attached to a button next to the two global dates called Birthdays From and Birthdays To (or you can use custom dialog, not explained in this):

                             Freeze Window
                             Go to Layout [ layout based upon the birthdays ]
                             Enter Find Mode [ uncheck pause ]
                             Set Field [  cThisYearBD ;  Birthdays From & ".." & Birthdays To ]
                             Set Error Capture [ On ]
                             Perform Find [ ]
                             If [ not Get ( FoundCount ) ]
                             ... optional Show All Records  ... sometimes you want to leave them all displaying and sometimes no
                             Go To Layout [ original layout ]
                             Show Custom Dialog [ "No records found." ; OK ]
                             Else
                             ... sort your list ascending on cThisYearDB
                             End If

                              

                        • 9. Re: Find records with birthdays in a range
                          SJCPS

                          LaRetta,

                               I very much appreciate your help and confirm following many more hours I got my DB to work thanks to your help. I didn’t quite understand at first then suddenly it all fell into place and it is all working fine just how I wanted.

                               Sorry for the wait and thank you again.