Sorting question - or is it dilema?

[AmyP]

I must be missing something patently obvious here, but I am having issues w/ sorting my client database. I want to track clients' birthdays and have a "birthday" field set up as type date. However, when I sort the records, my oldest client comes up first - not what I want. How do I set up a sort that sorts by calendar month and day, but ignores the year?

TIA,

AmyP

[LaRetta]

Hi Amy,

The easiest way is to create a calculation (date) with:

Date(

Month(BirthDate), Day(BirthDate), Year(Status(CurrentDate))

)

... then just sort by this. In this way, they all have the same year and it will sort correctly (only by month/day). A potential problem ... if they were born on a leap year/month, ie, 2/29/2004, it will say their birthday is 3/1.

But truly, if they were born on 2/29, they are probably USED to their birthday jumping back and forth. I would like to know the answer myself (now that I've brought the question up). This may be one time that FM's internal date handler ... using Date() ... might get it wrong!

LaRetta

[LaRetta]

Hi Amy,

I wanted to give you a (better, I think) option ... This calc produces MMDD format for sorting:

Right("0" & Month(BirthDate), 2) & Right("0" & Day(BirthDate), 2)

... and it correctly produces 0229 for that lost soul with a leap year birthday.

I remain intrigued (just for my own information) on how I could force that Date() calc to adjust for leap years ...

LaRetta

[LaRetta]

I seem to be carrying on a conversation with myself (3 posts in a row); my apologies, but I like to get things right ... and I'm not pleased with either solution I provided.

I just realized ... what if someone's birthday is 2/29/1988 and the current year doesn't HAVE a 2/29! How will you interpret 0229 when it's 0228 and the next day is 0301?

I feel like a dog chasing my own tail on this puppy!

I hope some date magicians step in or I'll be up all night again figuring this one out. I'm back on the first Date() calc (well, maybe not). I'm seeing patterns of 4 and Extend() and Choose() and ... Lord help me.

L

[comment]

Filemaker does NOT get it wrong. There simply is no right way.

There are ~365.25 days in a solar year. Our calendar artificially packs a whole number of days into a year, and resolves the difference by leap years. This is nothing but a naming convention. We decide to call this day April 12 2005, and tommorow's April 13. A day under any other name...

The concept of "exact birthday" is a myth: if you were born on April 12, 2001 4:00pm, your first "exact" birthday (i.e. one rotation around the sun) is on April 12, 2002 at 10:00pm. Your second "exact" birthday is on April 13, 2003 at 4:00am. And that's only if you have stayed in the same time zone.

The "conventional" birthday ignores all that. We refer to the calendar day name, instead of real time. Now, if you want to be consistent within this method, then the birthday of the person who was born on February 29 is ... on February 29.

But we don't want to be consistent - we want the kid to have a birthday every year just like all the other kids, right? So I say to you: if you don't want to be consistent, then do whatever you want and let no one tell you what's right or wrong. Whatever you feel works is fine. Just don't look for the logic in it, because there isn't any.

Filemaker is not concerned with birthdays. Filemaker counts. If you ask Filemaker to return a date based on year, month and day (the Date() function), it will start at the given year, count the number of months from the beginning of the given year, and then count the number of days from the beginning of that month.

Therefore Filemaker will cheerfuly take input like Date ( 14 ; 29 ; 2004 ) and return March 1 2005. Because starting from January 2004, the 14th month is February 2005. And the 29th day, starting from February 1 2005, is March 1 2005.

[LaRetta]

Hi Comment, we meet again on the FM date front!

Powerful stuff, thank you. Alright, let's provide a variable. Let the Personnel Manager insert into gAdjust global whether to celebrate it on the 28th (-1) or the 1st (1). I doubt she'd have more than one leap-year employee; even so, she could ask them (or draw straws). Wouldn't something like this handicap the race and provide the consistency you mention?

We should be able to test whether 2/29 of Current Year IsValid() date. If not, apply gAdjust.

No, this doesn't work (MMDD scenario) no matter what I do (glaringly obvious to you, I'm sure) but I don't see it. I have suffered 3 solid hours of too many (or not enough) separators and I can't keep my eyes open any longer.

If( BirthDate,

Right("0" & Month(BirthDate), 2) &

If(

IsValid(

Date(

Month(2), Day(29), Year(Status(CurrentDate))

)

),

Right("0" & Day(BirthDate), 2),

Right("0" & Day(BirthDate) + gAdjust, 2)

)

)

Pretty ugly, huh. But can you see where I'm going ... since I can't?

Edited: I changed it a bit ... I had adjusted it and forgot to correct it again - even though it's wrong anyway.

LaRetta

[comment]

I believe you're mixing two issues here:

For SORTING, your (text) calc above will do fine. Or you can resort to Ray's trickery and use a number calc =

Month ( Birthdate ) + Day ( Birthdate ) / 100

Both will correctly sort the upcoming birthdays in any year.

Determining the ACTUAL BIRTHDAY of someone who was born on February 29 is another matter. The formula:

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

returns March 1 in a regular year, and February 29 in a leap year.

If you want to change it to February 28 in a regular year, then:

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

- ( Month ( Birthdate ) = 2 and Day ( Birthdate ) = 29 and Month ( Date ( 2 ; 29 ; Year( Get (CurrentDate) ) ) ) = 3 )

Note that this:

isValid ( Date ( 2 ; 29 ; anyYear ) )

is always true. As I explained above, Filemaker sees nothing wrong in specifying a date like this - inside a Date() function. What you are doing here is ask if the date March 1 2005 is valid.

If you want to know if a year was a leap year or not, use:

Month ( Date ( 2 ; 29 ; theYear ) ) = 2

[LaRetta]

Oh dear yes, either of my first two responses would have worked for sorting only. And I didn't consider trying Ray's trickery calc (I like you calling it that and I've adopted it) for sorting!! And that calc resides in bold (14 pt) at the top of my Dates.doc which I reviewed too! Good Lord.

I got off the subject of a simple sort of the birthdays and onto the fact that neither of my responses could produce a consistent result (smile). I let it set me off again. You'd think I would learn ...

Oh. Yes, you DID explain that FM will take any Date() and make it right and I really did listen. And you even provided Month(14) as my reminder ... same as Month(190) working (smile)!! I obviously didn't listen well enough. But NOW it's sunk in good -- Date() will ALWAYS produce valid results because it does the math!

If you want to know if a year was a leap year or not, use: Month ( Date ( 2 ; 29 ; theYear ) ) = 2

Month( Date( 2, 29, BirthDate) ) = 2

This boolean number calc produces 0 on 2/29/2004, 2/29/1996, 4/14/2004 and every other BirthDate I've tried. Your calc makes perfect sense and I know you're not wrong but why won't it produce a 1 on these three dates? Guess I'm still just too tired; have to head for the office. Thank you so much for indulging my date compulsions!!!

LaRetta

[comment]

Try:

Month ( Date ( 2 ; 29 ; Year ( BirthDate ) ) ) = 2

[LaRetta]

Of course.

It's amazing how intelligence rises or falls in direct proportion to sleep (or lack of). Must let those (mind) vacume cleaners (sleep) run periodically or I get (more) stupid. Thanks.

[AmyP]

Okay, I am in WAY over my head. I need the "FM for Dummies" version here - I'm sure there must be one. Where am I putting in this calc? As a values list? As a validation of the date ("birthday")field? A sort script? Halp! I looked in the user's guide (no print version) but nada. Hmmm... BTW, Retta I tried the 2nd calc you posted - and what looked to be the simplest - but it had no effect. I don't need to worry about a leap yr. birthday - very little chance of that occuring.

Again, how do I set up a simple sort that sorts Jan. 1st - Dec. 31st and ignores the year, please?

[LaRetta]

Hi Amy,

Apologies for 'muddying' this thread and confusing the issues for you. I suggest you use Ray's trickery calc.

Create a new calculation (number) of:

Month ( birthdate ) + Day ( birthdate ) / 100

... then sort on this number calculation instead of your birthdate field.

LaRetta

[LaRetta]

Amy, to assist you further - this should walk you through everything you need to do:

1) Highlight and Ctrl-C to copy the calculation I provided in my LAST post (smile).

2) Open your file. From FM Menu, select File > Define Fields. Type in Field Name: cSortBD. Click Calculation radio, then Create.

3) Ctrl-V to paste the calculation into the Specify Calculation box. Then click OK.

4) FM will highlight the field if it isn't exactly like your DOB field name. Say OK to the error message. Go up to your field list and double-click your 'real' Birthdate field. That will replace the highlighted text with your proper field name. Say OK and it might ask you one more time to double-click your field. Then click DONE.

5) Now from Menu, select Records > Sort. Double-click the new cSortDB field in left pane to insert it into Sort Order. It defaults Ascending order. Click DONE.

6) Open Scripts > Script Maker (from Menu). Type Sort Birthdays in Script Name box and then Create.

7) Now insert your script steps (found in the left pane). Under Navigation, double-click Go To Layout [ and specify the layout you wish to display after sorting the dates ].

8) Then find (and double-click) Show All Records from Sort/Find/Print section. In same section then insert (double-click) Sort. Say OK. It will pop up another box . Click the radio REPLACE, then select OK.

Now you can place a button somewhere. Insert > Button > Perform Script and specify your new Sort Birthdays script.

LaRetta

[AmyP]

Yeah! Holy cow, it really worked! Thank you SO much. Now, if I may be so bold, I've got another question: is there some kind of gizmachi (like a plug-in)that will give me an alert, say, 10 days prior to said client's birthday? I need some kind of advance warning so I can get a card/coupon in the mail on time.

Thanks again, Amy

[LaRetta]

Amy said... Now, if I may be so bold ...

Please be bold - I like that in people.

I would suggest posting your question about the plug-in in the plugin section. There are many options (and plug-ins) available. I've only used Secure FM with Menu Magic (which might be overkill for your wishes). You can also search here on Forums for Event Triggers and Plug-ins.

Sometimes, it's easier to just display a portal of the week's upcoming birthdays on your main layout (where your file opens). Then a quick glance daily will keep you informed. You can even set it up so that when you click a selected birthday in the portal, it goes to their detail information (or prints their envelope).

If you'd like to try any of this let us know and we can step you through it.

Oh. And welcome to FM Forums!

LaRetta