1 2 3 Previous Next 43 Replies Latest reply on Jun 20, 2016 1:51 PM by DamianKelly

    How many orders last 30 days

    andresen

      I have looked in the Advanced Training Series and here in this discussions but can't find an answer. I'm thinking it should not be so hard...

       

      In my custom table I made a calculation field that I want to show; How many orders have been made the last 30 days.

       

      In order table I have a date field that show the date the order is made.

       

      I have tried the Let function and the Case function with no good result.

       

      I'm thinking of getting the last 30 days as a variable and use it some how. I'm stuck! Please help me get loose from this swamp.

        • 1. Re: How many orders last 30 days
          bigtom

          Let (

          [

          d0 = Get ( CurrentDate ) - 30;

          d1 = Get ( CurrentDate )

          ];

          ExecuteSQL ( "SELECT COUNT(\"OrderID\") FROM \"OrderTable\" WHERE \"OrderDate\" BETWEEN ? AND ?"; ""; ""; d0; d1)

          )

          1 of 1 people found this helpful
          • 2. Re: How many orders last 30 days
            bigtom

            Of course replace OrderID, OrderTable, and OrderDate with the names from your file.

            • 3. Re: How many orders last 30 days
              erolst

              And if this formula is used in a calculation field, set that field to unstored.

              • 4. Re: How many orders last 30 days
                bigtom

                erolst wrote:

                 

                And if this formula is used in a calculation field, set that field to unstored.

                Correct. Forgot to mention that.

                • 5. Re: How many orders last 30 days
                  beverly

                  I wouldn't use BETWEEN, just :

                  WHERE \"Order Date\" >= ?

                  ...

                  ; d0 ) // no need to say 'today', it's implied

                   

                  That is unless you have order dates that are future. Then, I'd use:

                  WHERE  \"Order Date\" >= ?

                  AND  \"Order Date\" <= ?

                  ...

                  ; d0 ; d1 )

                   

                  beverly

                  • 6. Re: How many orders last 30 days
                    andresen

                    How do I replace the OrderTable? The OrderID, I pic the field in Order table and the same with OrderDate. Still I get a ?  as a result.

                    • 7. Re: How many orders last 30 days
                      bigtom

                      beverly wrote:

                       

                      I wouldn't use BETWEEN, just :

                      WHERE \"Order Date\" >= ?

                       

                      beverly

                      Yes. Could be cleaner that way.

                      ----------

                      Let (

                      [

                      d0 = Get ( CurrentDate ) - 30

                      ];

                      ExecuteSQL ( "SELECT COUNT(\"OrderID\") FROM \"OrderTable\" WHERE \"OrderDate\" >= ?"; ""; ""; d0)

                      )

                      • 8. Re: How many orders last 30 days
                        bigtom

                        andresen wrote:

                         

                        How do I replace the OrderTable? The OrderID, I pic the field in Order table and the same with OrderDate. Still I get a ? as a result.

                        You should not have OrderTable::OrderID or OrderTable::OrderDate. Just OrderID, OrderTable, and OrderDate.

                         

                        This uses the field names only for the ID and date fields, and the table name only for the table.

                        1 of 1 people found this helpful
                        • 9. Re: How many orders last 30 days
                          DamianKelly

                          There are a few options.....

                           

                          You could add a new relationship from custom(do you mean customers?) to orders. The relationship would be the same as the existing one but with one added predicate. In the custom table create a date calc field that is:

                          get(current date) - 30

                          Relate this new field to the order table::date field with a > condition

                          If you now do a count records calculation in the custom table this will give you the order count

                           

                          Another approach would be to had a calc field to the order table:

                          if(

                          date > get(current date) + 30; 1;

                          0

                          )

                          You can now sum these fields with your existing relationship.

                           

                          Or you could do a little bit of ExecuteSQL mojo. In the custom table make a calc of:

                           

                          ExecuteSQL("

                          Select Count (\"Order\".\"Order_ID\") FROM \"Order\" WHERE \"Order\".\"Custom_ID\" = ? AND Order.Date > CURDATE - 30";

                          "";

                          "";

                          Custom::Custom_ID

                          )

                           

                          I prefer the SQL, then the relationship. I am not sure I like the calc field much at all.

                          • 10. Re: How many orders last 30 days
                            bigtom

                            Let (

                            [

                            d0 = Get ( CurrentDate ) - 30

                            ];

                            ExecuteSQL ( "SELECT COUNT(\"your_orderID_fieldname_only\") FROM \"your_order_tablename_only\" WHERE \"your_orderDate_fieldname_only\" >= ?"; ""; ""; d0)

                            )

                            • 11. Re: How many orders last 30 days
                              DamianKelly

                              If you use SQL's data functions rather than FileMakers you will not need to worry about the different date formats

                              2 of 2 people found this helpful
                              • 12. Re: How many orders last 30 days
                                erolst

                                beverly wrote:

                                 

                                I wouldn't use BETWEEN, just :

                                WHERE \"Order Date\" >= ?

                                beverly

                                 

                                To be nitpicky : that wouldn't necessarily be only orders from with the last 30 days; it would also register future orders – which may or may not be desired. (I was thinking about making the same suggestion.)

                                • 13. Re: How many orders last 30 days
                                  beverly

                                  Spot on!

                                  CURDATE - 30

                                  works well in ExecuteSQL().

                                  I might use >=, not just >, but it's up to the end user.

                                  beverly

                                  • 14. Re: How many orders last 30 days
                                    bigtom

                                    erolst wrote:

                                     

                                    beverly wrote:

                                     

                                    I wouldn't use BETWEEN, just :

                                    WHERE \"Order Date\" >= ?

                                    beverly

                                     

                                    To be nitpicky : that wouldn't necessarily be only orders from with the last 30 days; it would also register future orders – which may or may not be desired. (I was thinking about making the same suggestion.)

                                    I get the impression this might not have future scheduled orders, but you are correct.

                                    1 2 3 Previous Next