1 of 1 people found this helpful
In Find Mode, enter >=// into the Shoot Date field to find records whose Shoot date is greater than or equal to the current date.
EDIT: Clarified that the find is on the date field, not the proposed Shoot Booked field (which is unnecessary for this operation).
Do you have another date field that is entered when the shoot is completed? If so, the Shoot Booked field can be a calculation (text result), using Cases looking at the Booked and Completed date fields. The calculation would return a yes only when Booked is not empty, but Completed is empty, and a "No" by default.
A search for "Yes" in the Shoot Booked field will find current Bookings, and also Bookings whose date may have passed, but that were not completed, and not re-booked, for some reason.
This is an easy way to find the set I need, thanks. A follow up to this is - I then need to export part of the data for a different person to mail out information packages to these clients. Ideally it would only be for shoots 45-90 days out and would exclude anything once it was marked as information packet sent. I do already have this yes/no field. I am getting busy enough that I am starting to outsource some of my work. I want to develop the database to the point that this sort of thing can be done as an on open script or something similar. Where should I look to find out how to build this?
Ideally it would only be for shoots 45-90 days out and would exclude anything once it was marked as information packet sent. I do already have this yes/no field.
As FilmUser suggests above, you would form a Find Request combining my criteria in the Shoot Date field and No in the Information Packet Sent field.
I am getting busy enough that I am starting to outsource some of my work. I want to develop the database to the point that this sort of thing can be done as an on open script or something similar. Where should I look to find out how to build this?
That's great that you are getting busy enough to outsource your work. In terms of building a database to accommodate this, I guess you mean that you want others to be able to run this script, is that right?
You can create a script that will store these find requests for you and then assign that script to a button.
If multiple people are going to be accessing your database from different locations then we are talking about building a hosted multi-user database which is more involved than I can get into in this space. I recommend that you read the FileMaker Training Series Basics if you haven't already; that will give you a good foundation for building what you're looking for.
No, there are not going be other users on the database. In the past I have just been able to go into the database and pull the information i need when i need it. Now I am needing to have another person do some of the front end work, and Mark lives a 90 miles away. Ideally I want a script that I can use to find, pull, and email him the information. Something I can run once a week maybe or come up with some type of regular schedule. The find set above worked great and I typed everything across that he needed for this first go. but I know there has to be some way to automate this process and make it quicker simpler and immune to typos.
2 of 2 people found this helpful
Ah, in that case then you can build a script that will store your find requests, gather the information from the found set that you want to send to Mark, and then send him an e-mail.
Something like this:
Set Error Capture [ On ]
Perform Find [ Restore ]
If [ Get ( FoundCount ) > 0 ]
Set Variable [ $to ; Value: "firstname.lastname@example.org" ]
Set Variable [ $message ; Value: "Hi Mark,¶" & "Here's a list of shoots:¶¶" ]
Go to Record/Request/Page [ First ]
Set Variable [ $message ; Value: List ( $message ; Shoot Date & " " & Client Name & " " & Client Phone ) ]
Go to Record/Request/Page [ Next ; Exit after last ]
Send Mail [ Send via E-mail Client ; To: $to ; Subject: "Upcoming Photo Shoots" ; Message: $message ]
After you've performed your Find manually you'll see that the Find Records [Restore] script step will already have your find criteria in there.
As you loop through the records you gather appropriate data into a variable. At the end of the loop you use Send Mail with that variable as the body.
Last step is to assign this script to a button on one of your layouts.
I hope this helps.
This sounds to me like a case for a join table. I assume you have many clients and many photoshoots—therefore a many-to-many relationship. If you had a join table to manage bookings you would have a ready means to isolate your found set—all the clients who are listed in the specific booking record.
I'm not sure there'd necessarily be a join table between bookings and clients unless multiple clients appear on a single booking, but I do agree that the table we're talking about here will eventually need to become the Booking table with a separate Client table holding client info.
On the found set, how do I make it look for both no and blank entries? On the info packet sent field.
you can search for empty values with =
Oh, I see... you want to find records with:
Shoot Date >= today AND
(Info Packet = No OR
Info Packet is empty )
There are a couple of ways to do this but probably the easiest would be to replace the Find Records step in your script with this:
Enter Find Mode 
Set Field [ Shoot Date ; // ]
Set Field [ Info Packet ; No ]
Set Field [ Shoot Date ; // ]
Set Field [ Info Packet ; = ]
Perform Find 
I am not very good with the script writer. No matter what I do I end up with at the least [Pause:On] in the Enter Find Mode. how do I get it to be [ ]?
When you hover over the script step you'll see a little gear appear to the right. Click on that and it will give you options for the script step. In this case, uncheck Pause.