Set Variable [ $date ; Get(CurrentDate) - 720 & "..." & Get(CurrentDate) - 365 ]
BTW: You could set directly the date field, while in Find Mode.
Wow, this works great. Thanks!
I was trying to do something like page 492 of the FileMaker Pro 11 Missing Manual where the author used a single ">=" type operator. I extended that example, possibly erronouesly, to try and make it a range like I'd do in SQL.
Should I assume that you can't do what I was trying to do with '>=" & Get(CurrentDate) - 720 and "<=" & Get(CurrentDate) - 365' in FMP?
IOW...use Range Operator for these types of things?
Lots to learn here. :)
Regular expressions such as those do not work as find criteria. There are many times when it would be nice if they did, but they do not. This can make certain finds with complex criteria a challenge to do at times.
Thanks as always for your input and reply.
Another thing I'm stuggling with is how to do something that's quite easy in SQL. Namely, say you have two tables. Each table has a Name field.
Say you want a list of all names that are in table 1, but not in table 2 (or vice versa).
Is that easy to do in FMP?
I tried implementing a find command in a script, but can't seem to get the syntax right.
In SQL, it would be something like SELECT * FROM CUSTOMERS WHERE CUSTOMER_NAME NOT IN(SELECT ANOTHER_NAME FROM ANOTHERTABLE)
IF you have this relationship:
Table1::Name = Table2::Name
Then, on a Table1 layout, enter find mode, put * into Table2::name and specify the Omit option.
This finds all Table1 records that do not have a matching Table2 record...
That's cool. I'm wondering how you'd do this if the tables weren't related, but that's not something I need to do right now.
I wish there were a "cookbook" type of book for FileMaker Pro, but perhaps the market isn't big enough?
The only current book I've found is the Missing Manual for version 11.
Fortunately, the support forum here is so awesome!
I appreciate your replies. :)
There's also another book on FileMaker that's more technically oriented. The "Filemaker Training Series"???? (Don't have it in front of me to read the exact title.)
If the tables weren't related, you'd end up relating them one way or another.
Based on your obvious knowledge of this product, I'm guessing you either studied the file maker training series well or ... you wrote it. :) Yes, that was the exact title. It's $99 on the FMP site.
I'm now wondering, based on the upgrade costs, if I should just wait for FMP 12 before jumping in. I still have a few days on my evaluation. However, the evaluation doesn't include the script debugger so I haven't been able to figure out some things that would be easier with it.
I would really like it if they added something (in '12') like creating a Find using an SQL command (leaving all the other functionality intact) or other interactive mode.
Isn't it about time for a new version?
Still, this is a terrific product as is.
There are third party plug ins that support SQL. You may want to do a web search for them to see what they have to offer.
Hmmmm, I did a quick search, but the two products I saw were to connect FMP to _other_ databses (like Oracle, etc.).
What I am hoping they'll implement is SQL for the FMP database itself -- the database I create in FMP. It would be great if there were some type of interactive quick results SQL window. I want to do a quick Left Outer Join on two tables and see the results? No problem. Of course, being able to do a SQL as the source for a Find command would be excellent as well.
Perhaps the internal database isn't SQL under the hood so that may be the issue. Not sure.
I would highly suggest any book by Raymond J. Cologon, PhD.