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. :-)
I been searching the forum for help as a newbi - did this question get answered? if yes what was the solution please - thanks.
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
Or if you prefer to roll your own ... 4/*/*
If you provide different example of your need, we can help you with it. :^)
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
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.
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:
thisDOB = Date ( Month ( DOB ) ; Day ( DOB) ; Year ( Get ( CurrentDate ) ) ) ;
thisDOB ≥ Get ( CurrentDate )
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. :^)
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?
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):
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 ]
... sort your list ascending on cThisYearDB
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.