onefish Posted June 1, 2005 Share Posted June 1, 2005 Attached is a fairly simple way to search for people by age using multikey relationships. I needed a way to do this in an application I am working on as my users will not be bright enough to work out the dates themselves. Originally I simply multiplied the age number by 365.24 and subtracted from the date but was not entirely happy with the results. The tricky part was working around leap years and different system settings. The attached calculations should work regardless of the system setings and occurances of Feb 29. If the multikey is required to fall on Feb 29 for a year that has no leap year it is changed to Feb 28. The calculation is designed to find real people by age so it limits the max range from 1/1/1800 to 1/1/4000 (filemakers max date). I have referenced the test date field so you can see how it performs around leap years by changing the date. For real world use change all references to the test date field with Get( CurrentDate ). It took me a couple of hours to get the calculations to function properly and there may well be an easier way but it is here if anybody wishes to use it. Dates.zip Link to comment Share on other sites More sharing options...
comment Posted June 1, 2005 Share Posted June 1, 2005 there may well be an easier way Yes, there is. And there is no need to limit the range. Dates.fp7.zip Link to comment Share on other sites More sharing options...
onefish Posted June 1, 2005 Author Share Posted June 1, 2005 Comment, I certainly found the long way around, didn't I. I never even considered that FM would automatically go to 1/3 if 29/2 did not exist. I assumed it would produce an invalid date and an error. I will leave the limit dates as these are two of 17 multi key fields and they should not be required to contain data to show records in the portal. I imagine FM will have a new version by 4000! Link to comment Share on other sites More sharing options...
Vaughan Posted June 1, 2005 Share Posted June 1, 2005 For the record, FMP's date engine calendar thingy goes to the year 3000, not 4000. Link to comment Share on other sites More sharing options...
onefish Posted June 1, 2005 Author Share Posted June 1, 2005 Vaughan, The application FM7.3, I am using will allow me to use date years from 1 to 4000 but not 4001. Link to comment Share on other sites More sharing options...
comment Posted June 1, 2005 Share Posted June 1, 2005 Yes, the Date() function is very powerful because of that, and calculations that are seemingly complex can be made very simply, e.g. Date ( Month + 1 ; 0 ; Year ) returns the last day of the Month in that Year. Vaughan, version 7 added another millennium and goes up to Dec 31, 4000. Link to comment Share on other sites More sharing options...
Vaughan Posted June 2, 2005 Share Posted June 2, 2005 I stand corrected! Thanks for the info. Link to comment Share on other sites More sharing options...
Recommended Posts