5 Replies Latest reply on Apr 15, 2014 1:51 PM by DLarsen

    Finding >=2014-Q1

    DLarsen

      I have a TEXT field where all of the entries are in this syntax:

       

      2014-Q1

       

      When I sort the records the results is as expected.

       

      Example:

      2014-Q1

      2014-Q2

      2014-Q3

      2014-Q4

      2015-Q1

      and so on.

       

      What I want to do is FIND all records greater than or equal to a specific quarter.

       

      Example:

      >=2014-Q1

       

      When I perform a find like that I get records prior to, and after, the specified quarter.

       

      Thoughts?

        • 1. Re: Finding >=2014-Q1
          ryantittle

          I'm pretty sure >= isn't going to work for you in a text field. I would recommend having a number field or a calc with values like 2014.2, 2014.3, 2014.4, 2015.1, etc.to use for your find. If the current format is important for on-screen, you could always script the find so you wouldn't have to actually show the other field.

          • 2. Re: Finding >=2014-Q1
            mark_b

            I set up a sample table with a text field and entered similar data.  You are correct, the find doesn't give the results expected.  So I left the data intact and just changed the field type from text to number.  (not a good idea, could cause problems down the road)  The finds worked as expected.  If you are using this field in relationships, you could run into problems unless you carefully match the field types on both sides of the relationship.

            Cheers, Mark

            • 3. Re: Finding >=2014-Q1
              DLarsen

              I will probably end up creating a calculated field that eliminates the dash. I'm guessing that is the source of the problem. I was hoping to do this without the overhead of a calculation.

              • 4. Re: Finding >=2014-Q1
                DrewTenenholz

                DL --

                 

                The FileMaker date engine is one of the overlooked gems inherent in your databases.  Presumably your text field has some sort of underlying true 'date'.  I'd suggest you set up a the search on that, and you can write some auto-enter replace calculations to put in the correct date.

                 

                In other words, if the user is in FIND mode with the cursor in the DATE field and types ">=2014-Q1" I can see an auto-enter calculation that gives back ">=1/1/2014" (or ">=4/1/2014" depending on what is desired) and then proceed with the find as usual.

                 

                I've made an interface that had a pull down list with terms like "This Quarter", "Last Quarter", "This Year", "Last Year", etc. which then replaces the date field with the appropriate text for valid find criteria.  It looks slick and works well, and still allows the user to type a specific date if they need to.

                 

                When it comes to reporting, then I do use calculated fields for "quarter_of_year" (1,2,3,4) or "quarter_and_year" (2014.1, 2014.2, 2014.3, 2014.4) for subsummaries and such but don't need to display them in all cases.

                 

                -- Drew Tenenholz

                • 5. Re: Finding >=2014-Q1
                  DLarsen

                  Thanks Drew but there is no underlying date. The data entered as shown.

                   

                  BTW, I did create a calculated field that removes the dash and when searching on the calculated field it does find the correct records.

                   

                  Example:

                  >=2014Q1

                   

                  I can live with this but I was hoping that there was some way of accomplishing it without a calculation.