11 Replies Latest reply on Nov 12, 2011 12:25 PM by LaRetta_1

    Simple Date Logic Not Working

      Title

      Simple Date Logic Not Working

      Post

      I'm trying to set a variable $date equal to the expression: ">=" & Get(CurrentDate) - 720  and "<=" & Get(CurrentDate) - 365

      (and then PerformFind on the field)

      However, I get the error that none of the records match this expression.

      If I remove either of the two expressions, then it works.

      I do have data in this range (greather than 2 years to but less than 1 year ago) so I'm guessing I'm doing something wrong (syntatically?), but I don't see it.

      Suggestions?

      Thanks.

      -m

        • 1. Re: Simple Date Logic Not Working
          raybaudi

          Set Variable [ $date ; Get(CurrentDate) - 720 & "..." & Get(CurrentDate) - 365 ]

          BTW: You could set directly the date field, while in Find Mode.

          • 2. Re: Simple Date Logic Not Working

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

            Thanks.

            - m

            • 3. Re: Simple Date Logic Not Working
              philmodjunk

              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.

              • 4. Re: Simple Date Logic Not Working

                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)

                Just wondering...

                -m

                • 5. Re: Simple Date Logic Not Working
                  philmodjunk

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

                  • 6. Re: Simple Date Logic Not Working

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

                    - m

                    • 7. Re: Simple Date Logic Not Working
                      philmodjunk

                      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.

                      • 8. Re: Simple Date Logic Not Working

                        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.

                        Thanks,

                        -m

                        • 9. Re: Simple Date Logic Not Working
                          philmodjunk

                          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.

                          • 10. Re: Simple Date Logic Not Working

                            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.

                            Thanks.

                            -m

                            • 11. Re: Simple Date Logic Not Working
                              LaRetta_1

                              I would highly suggest any book by Raymond J. Cologon, PhD.