How to automate a search

[BigZilla]

Each day I need to;

1. Find all employees who have a birthday on todays date

[Fitch]

Attach this script to a button:

Set Error Capture (ON)

Enter Find mode

Set Field (DOB, Status (CurrentDate))

New Record/Request

Set Field (DateStarted, Status (CurrentDate))

New Record/Request

Set Field (Anniversary, Status (CurrentDate))

Perform Find

If (Status (CurrentFoundCount) = 0)

..Show All Records

..Beep

..Show Message("No cake today")

Else

..Go to layout (List of names)

End If

[Ugo DI LUCA]

I already know about the comments I'll have on this....

Create 2 calculations.

c_MultikeyBirthday = REPLACE(DateToText(DOB, 6,5,"")& "

[BigZilla]

Hi Fitch, or should I say "Your-Pig-Ness,

Your script worked great and I thank you for the help.

Old Hand,

What benifit do I have going with your calculation instead of Fitch's script? I'm learning and the script was the easy way for me to go, I just would like to know "what is the best"?

Thanks Guys,

Bill

[Ugo DI LUCA]

What benifit do I have going with your calculation instead of Fitch's script?

Should take no more than 1 second to display the result either in a portal or in a layout for print...

But as your not searching unstored calcs, Fitch's script surely is quick also.

Would be interresting comparing on a big file though.

[BigZilla]

Thanks Old Hand, for letting me know. I don't think I'll ever have more than 400 to 500 e-mails in a day.

Harry

[Fitch]

Ugo, I think you're missing parentheses after the date, you might want to take another look.

Replace (DateToText (date), start, size, replacement

[Ugo DI LUCA]

Tom,

Sometimes these "old hands" get carried away

Well, this time I tested it. I wanted to add yours for comparison, but I found no way to make it work (how entering the current date would find the same date 20 years before ?)

Well here's the test.

From your suggestion about the 1 digit...No problem as the same method is used for all calcs...

See attached. The start script sets 9 records (random) to the current date 4 digits using Danjacoby's Random script. So do not try it 5 times or you'll get all records founds!!

BigzillaTest.fp5.zip

[LaRetta]

Hi Bill,

You don't need 6 or 7 scripts or a script at all. The following will also account for month and day by separating them. All you need is for them to match - they don't need to be 2-digit. Full process is as follows:

In your main file, create a cDatesKey calc (text, unstored) with:

Month(Status(CurrentDate)) & " " & Day(Status(CurrentDate))

Then, in your Employees file, create a cDatesKey (text, indexed) with:

Month(Anniversary) & " " & Day(Anniversary) & " " & Month(DOB) & " " & Day(DOB) & " " & Month(DateStarted) & " " & Day(DateStarted)

From main, join the two cDatesKeys and select the field(s) you wish to sort by. Place a portal from this new relationship on the layout in Main and display any (or all) Employee fields you wish. Each day when you switch to this layout, your 'daily' found records will automatically be displayed.

Cheers!

LaRetta

[Ugo DI LUCA]

Hi all,

Glad someone's following....

As I said, the calcs were working as both keys were calculated the same way. But I agree it could have been cleaner.

I'd personnally keep the "01/01" structure though as it is really easy to join with any DateRange tool available. I did a test with Bob's DateRange3.fp5 with very little changes to his file, and it worked like a dream.

The calcs could be changed to :

c_Multikey =

Right("0"&Month(DOB),2) & "/" & Right("0"&Day(DOB),2)&" "&

Right("0"&Month(DateStarted),2) & "/" & Right("0"&Day(DateStarted),2)&" "&

Right("0"&Month(WeddingDate),2) & "/" & Right("0"&Day(WeddingDate),2)

c_TodayKey =

Right("0"&Month(Status(CurrentDate)),2) & "/" & Right("0"&Day(Status(CurrentDate)),2)

I agree with LaRetta about the useless need of a script here, as mentionned before. A sortkey could be defined this way and used in a relationship :

c_TypeOfBirthday =

Case(

Patterncount(Right("0"&Month(DOB),2) & "/" & Right("0"&Day(DOB),2), cTodayKey)=1, "Date of Birth",

Patterncount(Right("0"&Month(DateStarted),2) & "/" & Right("0"&Day(DateStarted),2), cTodayKey)=1, "Date Started",

Patterncount(Right("0"&Month(WeddingDate),2) & "/" & Right("0"&Day(WeddingDate),2), cTodayKey)=1, "WeddingDate",

"")

This calculation, even used with a constant relationship would still lead to accurate results, displaying on first rows of the portal all employees with a concording Birthday date.

To get it even more efficient, the fields in the portal could be calculated as :

c_returnEmployee_ID = Case(Is Empty(c_TypeOfBirthday), "", Employee_Name)

Of course, as shown in the small attachment, the c_TypeOfBirthday would also be used with a Subsummary report if Bill has even the need of a printed report.

[Fitch]

Old hands - I try to keep in mind that this kind of stuff is generally intimidating to someone just learning FileMaker. Someone who has not even worked with relationships is going to have trouble wrapping their mind around calculated keys. FYI that's why I went with the script-based solution. It does the job, the questioner learns a little something, and they don't throw their hands up in despair when they see a jungle of calculations.

That said, I think it's great that an alternative method has been presented. I'm sure it's going to spark an idea for somebody, and that's what the forums are all about.

[Ugo DI LUCA]

Tom,

Be indulgent with me....

But I even didn't looked to your script at first, as I was sure it would do it.

But when I went to test it, I just didn't find any way to make it work.

Again, how entering the status(currentdate) would help finding the anniversary date for the DOB of my 35 years old employee's birthday ?

[cjaeger]

It's strange that when I use the calc (text) Month(Status(CurrentDate) & " " & Day(Status(CurrentDate))) that I get a '0' but they work right individually. So I needed to create two calcs (cMonth and cDay) and combine them

look at the brackets....

Month(Status(CurrentDate)) & " " & Day(Status(CurrentDate)) does the job

[LaRetta]

Hi Christian! Thanks so much! FM accepted my formula. I've been a bit spunky and I've been typing all my calcs (I type 85 wpm) instead of double-click selecting

I'll correct the above post. Another lesson learned - don't type em, select em - love it

LaRetta

[LaRetta]

Hi Ugo,

Why do you need a sort key? Only one date (CurrentDate) from three date fields will be in the portal. And you can set the sort via the portal relationship - it would make more sense to sort on FullName or create a calc with field names (DOB, Anniversary or DateStarted) and use that calc in the relationship sort.

I prefer simplicity, myself

LaRetta

[Ugo DI LUCA]

La Retta,

Why do you need a sort key?

Obviously for sorting....the records "matched" by the kind of matches !

Only one date (CurrentDate) from three date fields will be in the portal.

Unless you have some kind of magic tool ...there's no way to do this other than a calculated field. Any record has 3 date fields (DOB, Started, Wedding) so all of them would show in the portal, unless you use a calc that only return the match key, and mask the other ones.

"it would make more sense to sort on FullName."

I don't think so....Could be the second ortkey though.

"or create a calc with field names (DOB, Anniversary or DateStarted)"

That's currently what the calc I proposed is doing....

Effectively, if you want to find the records matching the three criterias give, it's a kind of logic for me to sort on the criterias...

"BTW, you don't need the month and day to be 2-digits, all you need is for them to match ."

That's why I said it WAS WORKING with the first version released, as they matched. But as you did yourself with your modification, I looked to a clean solution.

...And as I said, if you use these kind of calcs with a Date Range Tool, you'd likely have a "01/01/03" rather than a 01 01 03....

[Fitch]

Ugo: Doh! I finally see what you mean. Since our questioner seems to be long-gone at this point, I'll have to just guess that he already had some calculated fields for "Next Birthday," "Next Annniv." etc. Either that or he didn't fully test it before proclaiming that it worked. Sorry about that, everybody. Lack of coffee is a terrible thing.

It's clear now that a scripted Find approach to this would require a bit more work, for example a calculated field for each date's month/day to search on. However, I still think that this might be easier for a beginner to tackle than the calculated key method.

[Ugo DI LUCA]

Tom,

He may be flying with pigs in a FM dream

[LaRetta]

Hi everyone!

It needn't be complex Bill wants to find a current date in three fields! Attached is a very basic demo (step-by-step process listed above). This would also work with a self-join but since Bill has an Employee file, I created a Main & related. You can also adjust the key calcs to include the "/" for dates if you wish. That

AutomateSearch.zip

[Ugo DI LUCA]

Hi all, Hi La Retta,

Hopefully, I'm aware that only the records that matches the multikey would show

But it is likely that a user with such daily needs would need a report/portal or whatever sorted by the criteria...

This surely can be done with an additional calculation, but it still will display the 3 dates.

Now as we're obviously learning a lot from demo file , here's attached another one with 3 methods from simple to "very" complex tree-like portal.

If there's an issue about dates, as I'm running a French version, please report it. Thanks.

BigzillaTestv2.fp5.zip

[PiedPiper]

Hi LaRetta, yours seems really simple and I think I can use it for my Sales Activity stuff.

Ugo, I can't figure yours out. You have 17 fields and I dont know why and like 13 scripts but said we didn't need scripts. and I can't even tell what you are trying to do - to just find (any information) in three fields cus mine will be activity dates . I thought it would be easy. do I need all of that for it to work?

Why do you use Random scripts and so many felds? oh, it also kept saying the date was wroong when I opened it. Thought you like to know. Script2 does a find but I thought we woudln't need a find. Help! Do we? also just curious what is a wedding birthday? You guys and girls are great

Pete

[Ugo DI LUCA]

Hi,

I can't even tell what you are trying to do

I'm just taking the demo one step further to show what can be done with FM for a User friendly interface for this kind of "Daily task".

This kind of setting is very close to what I'm currently using for my Daily Activity Report (Invoices, Proposals, Purchase Orders, Customer Orders, Inventory mooves, Deliveries,...)

For a better view of the found set, I need either :

- A Subsummary report prior to any print.

-----> That's the purpose of the 1st method : Subsummary report on the Activity Module Type with a Script.

- A portal from where I'd select individual records,...showing one Module at a time..

-----> That's the purpose of the 2nd method : Tree-like hierarchical portal and some sort of "Drop-down hierarchy" script, which you can get another demo in the Sample Forum called "Line Item in Folders".

You have 17 fields and I dont know why and like 13 scripts but said we didn't need scripts.

There's no need for any script, except for the demo file, as it has 3 methods and we need some scripts to jump from one to the other. Some scripts were old ones and are now deleted. The fields are also associated to the different demos, and I fixed the Define Fields. Thanks for pointed it.

Remember that my first post on this thread suggested that the easier method was the Multikey method associated to the portal stuff .

LaRetta released the demo file for this method.

do I need all of that for it to work?

Of course not. You could just need the classic Multikey based portal that is exactly the same as LaRetta's.

Why do you use Random scripts and so many felds?

The Random script is just used for the demo. At opening, the Random script randomly set 3 sets of Birthday date equal to the current date, so that you surely have Birthdays to found.

it also kept saying the date was wroong when I opened it. Thought you like to know./quote]

Yes, thanx. This is due to your current system settings (vs French settings).

Does anyone has a method BTW to prevent this ?

But the demo is working after you reset the dates when the message appeared...wasn't it ?

There was even a 4th method in the demo for a date range found set, but I was concerned about the system settings and also found that this one was already sufficient. Anyone interrested could just download Bob's DateRange3.fp5 and make it work.

Finally, Tom is ******* right saying that too many complication may be "alarming" for a newbie, but there is a day where a newbie may think about FM relational design for quick finds.

[BigZilla]

Thank you both for your information and I

[Ugo DI LUCA]

Hi Bill,

Good to know you went relational on that one. Loretta probably did know better than I about Wyoming need for simplicity .

You got the idea, which was the start point of this loooooong discussion. Good.

One little point though, not adressed by either Loretta and myself.

What if the birthday is on Feb 29 and the current end-month of February is 28 th. Who got the chocolates and cakes ? Do you celebrate on the 28th or on March 1st ?

No need for complication : YES

Simplicity always pay : NO

[BigZilla]

Laretta,

I just thought of another wrinkle, no pun intended. Now there's a way to get your question answered!

I would want to send a birthday card etc., not on the date of the birthday, but 7 days prior. How would you do that and would you add it to the portal. And would you show somewhere that Ugo's birthday is on the 12th but was mailed on the 5th?

When I think of the many ways this can be used, I just want to make sure it's right the first time. And what about the Feb 29th thing? That Ugo, always making trouble!

Bill

[Ugo DI LUCA]

Ahah!!

Now, here's the big stuff hided behind the tree.

Of course, when you have a portal, you surely need it for something else !

And I'll bet we cannot say Birthday-7 as you don't send mail on Sundays...

And you want a list of birthdays, a list of the birthdays that would come in a week, and a way to flag directly from the portal....

I'll play with it... Just having fun.

[LaRetta]

Hi Wild Bill!

Not to worry. We can account for these issues and modify your calcs.

1. Because you're using Status(CurrentDate), the records in the portal will display only ONE DAY and then not display again for another year! This could be problematic because what if staff is sick that day? They need a way to go backwards if necessary or view a date range. I

[Ugo DI LUCA]

Hi Bill, Hi La Retta,

This thread is getting longer and longer....

Bill is finally looking for a list of employee which birthday :

- is Today

- will be in a week

Therefore, I'd forget about the current date and would move the entire solution to a week range solution.

Based on that, the right side key would be calculated (Lee Smith's formula) as :

c_employeeMultikey =

Mod(WeekofYear(Date of Birth) - WeekofYear(Status(CurrentDate)) + WeekofYear(Date(12,31,Year(Status(CurrentDate)))), WeekofYear(Date(12,31,Year(Status(CurrentDate))))) &"

[PiedPiper]

Thanks LaRetta! I now can track my salesmen activities. Even I could follow your thougts on it and it made sense to me (shock) I cut pasted it into mine but had to change a few names to match mine-that's all.

Good thing you type so fast. It would havetaken me a week to type that

Pete

[Ugo DI LUCA]

Hi,

Tracking Sales Activity...

Hmm... the good point here is that either your salers and yourself would have a day-off any leap year with these settings. . Nothing never happens on Feb 29 !

It'd be OK in 2004 as the 29th is on Sunday, but you can planify a great week-end for 2008...

Little adjustment needed here, but I let LaRetta fix it.

Now again, this scheme is OK for some "punctual" need, as those Birthday things, while in a business solution, where some reportings and printings are made, you would base your "search" on ranges (day, week, month, quarter, semester, annual,...).

The process is here though...

[PiedPiper]

It works for me. I changed my computer date and entered 2/29 in a record and it displays???

LaRetta said it wasn't a full answer. It's just to display upcoming events. I created a self-join in my Activities and use the portal for quick displays of things. I may be new, but I know enough that I need scripts (yuk) to automate reports, etc. It just saves our staff from having to perform finds all the time and allows them to quickly enter data in the portal - things like customer appointments.

I created buttons for forward back display and it it's pretty nifty

Pete

[BigZilla]

Ok. Ok, I give up. Ugo was right with " probably did know better than I about Wyoming need for simplicity" I'm going to move to Colorado where I understand people are smarter! Then again, my brother lives there and he's at the shallow end of the Mavrick gene pool. I can't even sleep anymore, All i can think about is Ugo, Laretta, Ugo, Laretta, Ugo, Laretta, Ugo, Laretta and my wife keeps telling me, Get a life buddy! And that buddy part is never a good sign. Then I try to explain to her that Ugo says, "Mod(WeekofYear(Date of Birth) - WeekofYear(Status(CurrentDate)) WeekofYear(Date(12,31,Year(Status(CurrentDate))))", but Laretta says, Case(Month(Anniversary) = 2 and Day(Anniversary) = 29, Month(Anniversary) & " " & Day(Anniversary -1), Month(Anniversary) & " " & Day(Anniversary) & " " &". Any my wife says "you can take that kind of talk outside Buddy"! You would think she would know my name after so many years of marriage too!

Anyway, you guys are killin me, your just killin me! I have my database so confused it's hardly talking to me anymore. And when it does it says stuff like "back off jack"! See, it doesn't recognize me anymore either. You guys owe me big now. If my wife calls me Buddy one more time I'm out with the animals and that ain't pretty.

So come on now, I should I do, Really?

Buddy Bill

[Ugo DI LUCA]

Hi Pete,

Sorry for the confusion, and also my apologizes to LaRetta. I was quite sure that I hadn't seen a multikey for the left side, so entering 02/29 wouldn't have worked. While with a 7 days range, the 29 will appear of course. LaRetta, did you edit your post or may I go with the animals also as Bill with his BudLight ?!

Hmm...well now, just need to check it back. Entering 29 would have produced 29,30,31,32,33... but you changed the 29 at right side to 28 !?

So no, it won't show or I need a rest.

It would show though whe entering 28 or going backwards from 29. Anyway, this is very small adjustment wiith one of these Case(Status(CurrentDate) ....

And I still will go with weeks rather than days....

Bill, stay cool. Life is cool and wifes also. because they don(t need a thousand words to make themselves understood. Point !

[Fitch]

Everyone's probably lost interest by now, but I think Ugo's trying to say that LaRetta's calc above does not account for when the current year is a leap year, in which case 2/29 would be a valid date -- so you would want to use 2/29, not 2/28. (I've simplified the formula a little below):

Case(Month(Anniversary) = 2 and Day(Anniversary) = 29, "2 28", Month(Anniversary) & " " & Day(Anniversary)) & "

[Ugo DI LUCA]

Hi Tom,

Here's another one for leap years :

Case(Dayofyear(Date(12, 31, Year(Status(CurrentDate)))> 365, "leap year", "not a leap year")

So:

Case(Month(Anniversary) = 2 and Day(Anniversary) = 29,

Case(Dayofyear(Date(12, 31, Year(Status(CurrentDate)))> 365, "2 28", "2 29"),

Month(Anniversary) & " " & Day(Anniversary) )& "