Jump to content
Sign in to follow this  
BigZilla

How to automate a search

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Create 2 calculations.

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

Tom,

Sometimes these "old hands" get carried away grin.gif

Well, this time I tested it. I wanted to add yours for comparison, but I found no way to make it work crazy.gif (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

Share this post


Link to post
Share on other sites

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) & " par.gif" & Month(DOB) & " " & Day(DOB) & " par.gif" & 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

Share this post


Link to post
Share on other sites

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)&" par.gif"&

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

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 ?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 crazy.gif

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

LaRetta

Share this post


Link to post
Share on other sites

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 grin.gif

LaRetta

Share this post


Link to post
Share on other sites

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....

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Hi everyone!

It needn't be complex smirk.gif 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

Share this post


Link to post
Share on other sites

Hi all, Hi La Retta,

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

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 smile.gif, 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

Share this post


Link to post
Share on other sites

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? blush.gif

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 cool.gif

Pete

Share this post


Link to post
Share on other sites

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? blush.gif

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.

Share this post


Link to post
Share on other sites

Hi Bill,

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

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

grin.gif

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 ! smile.gif

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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))))) &"

Share this post


Link to post
Share on other sites

Thanks LaRetta! I now can track my salesmen activities. Even I could follow your thougts on it and it made sense to me (shock) smile.gif 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 grin.gif

Pete

Share this post


Link to post
Share on other sites

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. wink.gif. 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...

Share this post


Link to post
Share on other sites

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

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 grin.gif

Pete

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 ?! grin.gif

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.... wink.gif

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

Share this post


Link to post
Share on other sites

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)) & "

Share this post


Link to post
Share on other sites

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) )& "

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By Tumma K
      Hello, All!

      I am an aspiring developer for Filemaker. The company I work with is stuck in the past working off of Filemaker Pro 4.1

      I was given the task of bringing us up to Filemaker Pro/Server 13. So far my conversion prototypes are successful but we recently had a layout issue that can only be fixed in versions 3-6 (as the file is an .fp3) I work off of a macbook while our network is all Windows 7. In order for me to repair the layouts without tampering our active database, I decided the best option is to repair a copy of our solutions off the network. Unfortunately, when I go to download the trial version of Filemaker Pro 6 off of the respected website, the file is corrupt! I've tried multiple times, with different extraction apps and in different directories.

      My question is;

      Does anyone know a place where I could obtain version 6 (or better yet, 4.0) for an OSX computer? I've looked everywhere!
       
      Thank you for your time,
      Tumma K.
    • By MrEddByrnes
      I'm hoping my question can have a happy ending. In the mid-90's, I purchased Filemaker 3. When Filemaker 5.5 Pro was released, I bought the update CD, which requires the user to either have FM 3 installed or to have the installation CD for FM 3. I've used it all these years, most recently with Windows XP Pro, and it has worked just fine. The databases I began with were long ago converted to FM Pro 5.5 databases.
       
      I'm still using FM Pro 5.5 on a laptop with WinXP Pro, but in 2013, I purchased a PC with Windows 8. I haven't been able to install FM 3 on it, therefore can't install FM Pro 5.5. I am retired and rarely use Filemaker, but I have a few Filemaker databases I'd like to add to my Win 8 machine. I don't feel it's worth upgrading FM for the sake of using a couple of databases.
       
      Has anyone else run into this situation and/or have a (possible) solution? Is there perhaps any other software that can read FM 5.5 databases? Thanks in advance for your help.
       
    • By bmill
      I am using a custom filemaker solution for medical office billing written with fp5 running on a mac with snow leopard. In addition, I have a patient management db (which I wrote) that is linked through pt. ID number to the billing program allowing transfer of some demographic information (name, DOB, etc).
       
      Other than being limited by hardware restrictions, the billing program serves our needs for now and upgrading to fp12 will take some time (and money).  In the meantime, I am upgrading my pt. management program to fp13 and would like to move new patient demographic information from the billing program ( fp5 running on snow leopard through Parallels) and the new pt management program ( fp13 running on OS X 10.9) on the same mac.   
       
      Ideally, demographic information would be entered once into fp5 and then a scipt would make the data available for fp13.
       
      Any ideas on how to make this work?
    • By randyinla
      Hi, can anyone tell me why my on-line database might have stopped allowing me to delete records?  All of my access privileges and passwords are correct.
       
      thanks!
    • By randyinla
      Hi, I still use filemaker 5.5 and am very happy with it and don't want to upgrade.  Does anyone still host filemaker 5.5?  Thanks!
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.