12 Replies Latest reply on Mar 2, 2016 12:16 AM by rrrichie

    Looking for a way to report a date range...Please help

    user311

      I've got start date and end date drop down calendar created. I need to report using the source from "Field1" between the selected date range how many orders "Field2" have been created between the dates and put the total count in "Field3". "Field1" is an open text field with the persons name and there is some variance between entries like spaces and upper lower case entries since it was just and open field. I've used the filemaker example to create a script to find a date range but am having trouble pointing that script to the source "Field1" and reporting the count to "Field3". Any help would be appreciated. I've added a pic of the layout.

       

      All the actual fields are just on a different layout for this report. Any help would be greatly appreciated.

        • 1. Re: Looking for a way to report a date range...Please help
          dsvail

          need more info to understand what you are trying to do .... maybe attach your file.

          • 2. Re: Looking for a way to report a date range...Please help
            user311

            A little hard to attach since it's just a layout of a much bigger database. Basically, if you look at the image, I'm just trying to have a selection box to select from about 5 people in the top dropdown and find a date range using the dropdown calendars and report the total number of records created by that user. Currently, everytime a record is created a WO creator (name of the person) is entered in the creator field (top of the attached)

            • 3. Re: Looking for a way to report a date range...Please help
              siplus

              I dare to hope that you used globals for all fields.

               

              See attached.

              • 4. Re: Looking for a way to report a date range...Please help
                dsvail

                maybe show us your script that you are using ....

                making some assumptions... but, here is how I would do it ... make sure that the fields on the layout you are showing are global fields (setup for the purpose of doing this find)

                // gFields designate global fields ...either in your table or a global table

                 

                Script:

                Enter Find Mode

                 

                Set Field [ table::date;  table::gStartDate &"..."& table::gEndDate]

                Set Field [table::WO Creator; table::gCreator]

                 

                Perform Find [ ]

                 

                // what to do here with results ie. FoundCount

                Set Variable [ $$count;  Value: Get (FoundCount)

                • 5. Re: Looking for a way to report a date range...Please help
                  rrrichie

                  Seems like you are asking different things.  One is the date range and the other is getting the found count and putting in a field.  dsvail is the steps you need for that.

                   

                  You can also do it with SQL using the BETWEEN construct.

                   

                  ExecuteSQL ( " SELECT COUNT(*) FROM theTable WHERE theDateField BETWEEN ? AND ? " ; "" ; "" ; theTable::dateFieldFrom ; theTable::dateFieldTo )

                  • 6. Re: Looking for a way to report a date range...Please help
                    dsvail

                    compare scripts between what I showed and Siplus's example ... approach depends on what you are trying to accomplish.  If , just getting a calc.global field result on the count use Siplus's script / logic which is using FileMaker ExecuteSQL function.

                    • 7. Re: Looking for a way to report a date range...Please help
                      siplus

                      This jive not showing attachments in Inbox is a huge time waste for everybody.

                       

                      We have to add messages like "hey, there's an attachment, click here to see it" and so on.

                      • 8. Re: Looking for a way to report a date range...Please help
                        siplus

                        In my experience, valueCount on the result of a SQL is faster than embedding the count into the SQL. That's why I used a Let in my example.

                         

                         

                        Set Variable [$start; Value:Get ( CurrentTimeUTCMilliseconds )]

                          Set Field [reporting::gWorkOrders; Let(  esql = ExecuteSQL("SELECT WorkOrderID FROM workorders WHERE WO_OwnerID = ? AND WO_Date BETWEEN ? AND ?"; "";""; reporting::gPerson;reporting::gDateStart;reporting::gDateEnd);  ValueCount(esql)  )]

                        Set Variable [$$stop1; Value:Get ( CurrentTimeUTCMilliseconds ) - $start]

                        Set Variable [$start; Value:Get ( CurrentTimeUTCMilliseconds )]

                          Set Field [reporting::gWorkOrders; ExecuteSQL("SELECT Count(*) FROM workorders WHERE WO_OwnerID = ? AND WO_Date BETWEEN ? AND ?"; "";""; reporting::gPerson;reporting::gDateStart;reporting::gDateEnd)]

                        Set Variable [$$stop2; Value:Get ( CurrentTimeUTCMilliseconds ) - $start]

                        Refresh Window []

                         

                        Query + ValueCount ($$Stop1) is executed in 15 ms in my example, while Count(*) - $$Stop2 - needs 78 ms.

                         

                        YMMV.

                        • 9. Re: Looking for a way to report a date range...Please help
                          user311

                          siplus

                          Screen Shot 2016-03-01 at 10.17.23 AM.png

                          What you created is great! Exactly the actions I was looking for. I was using the global fields command but being a noob, I'm sure not correctly. I was using a modified "find date range" example in the Filemaker Help docs.

                           

                          The only issue I may have is the current "WO Creator" field that has been populated for years was using open text and I may have to do a "replace" for the names since there is some differences in how they were entered as open text

                          • 10. Re: Looking for a way to report a date range...Please help
                            siplus

                            As you saw, I rely on a popup menu to make sure there's no confusion; moreover, behind the popover what I am collecting is the worker's ID.

                             

                            To clean a mess built up during years, what I do is:

                             

                            - go to the workorders table (I suppose you have names there, instead of ID's, in a field called workOrderOwner - or whatever)

                            - select all records

                            - sort records based upon workOrderOwner

                            - Export records, just the field workOrderOwner, summarized by workOrderOwner, Filemaker file;

                            - Open and inspect the file (view as table) to see how big the problem is;

                            - Add a PK field, numeric, replace with serial numbers;

                            - Add a OK field, numeric, replace on all with 1

                            - MANUALLY correct entries in the PK, making sure that joe smith, Joe Smth and Jo Smithe have the same PK;

                            - MANUALLY set the OK field to 0 on wrong names like Ju Smth;

                            - Create a WorkerID field in the workorders table;

                            - Create the Workers table in the solution;

                            - Import the aux DB just created in it;

                            - build a relationship workorders - workers based upon name;

                            - replace the WorkerID in workorders with the related value from this relationship;

                            - delete the relationship and create the real one based upon ID - ID;

                            - delete the Workers having 0 in the OK field from the Workers table;

                            ...

                            • 11. Re: Looking for a way to report a date range...Please help
                              dsvail

                              check your last Set Field script step ... make sure you are setting the correct field (a date field )

                              • 12. Re: Looking for a way to report a date range...Please help
                                rrrichie

                                That's a handy thing to know :-) Thanx Siplus