4 Replies Latest reply on Oct 13, 2009 9:40 AM by rickscarborough

    Summary field and list dependent on date.

    rickscarborough

      Title

      Summary field and list dependent on date.

      Post

      Hi

       

       

      We are moving our small business reservation system from paper to a Filemaker 10 Pro database on Mac OSX with input by phone receptionist.

       

      We have a "date of reservation" field and a "number of guests" field.

       

      How can we make a summary field for "number of guests" sum only the records for duplicate dates.  We would like the field to display the total number of

      guests for the particular date of the record.

       

      Thanks to suggestions by "Mr_Vodka", we are currently using a script as shortcut to "find" by duplicate date and move to the last record.   The summary field then shows the sum of the results of the find by date.  We would like to figure out a way to do this using calculations and summary fields without changing record sets with a script. 

       

      We are also asking for help to get a layout/report that shows a list of unique dates showing the sum total of number of guests for each date.  We are trying to figure out a script similar to that mentioned with the addition of going to a custom layout showing a list of each unique date with the sum total of number of guests for all records with that date.  We would prefer to do this in one step using a layout.

       

      Thanks for any help

       

      Rick   www.bardchuckwagon.com 

       

        • 1. Re: Summary field and list dependent on date.
          mrvodka
            

          What is the purpose of why you need this? As stated in the last post, you can use a subsummary report grouped by the date. The report will be based on your found set. If you dont want to lose your current found set, you can always pop open a new window and do ti there.

           

          Also, depending on what you need, you can use a calculation with GetSummary, but much like the subsummary report, it will need to be sorted by the break field.

          • 2. Re: Summary field and list dependent on date.
            rickscarborough
              

            Hi

             

            Our phone receptionists are not comfortable using a computer for anything and often are taking a reservation with another call on hold.  Seeing the total number of guests instantly on the form as soon as they enter a "reservation date" without additional steps would be best for a smoother workflow and avoiding over booking.  Using a script adds extra steps and more steps yet in getting back to the previous record set with staff that has to memorize each step.  The script might work OK, but I've seen a form (years ago) that does exactly what we would like. I don't know how and they are now using a MySQL online DB.

             

            I will need to learn what "getsummary", "subsummary", and "breakfield" are before I can try to figure out how to incorporate them into a calculation.  I haven't even been able to make a script or list grouped by date showing just one line for each date yet.   We would print this report at least twice a day and need a couple of weeks of reservation counts by date on each printout.  The reservation counts determines many decisions (crew, food orders, cooking amounts, seating options, etc.) and is used by several people that need to get reservation counts at a glance at least twice a day.

             

            Our current paper list look like this:

             

            06/01/09---250

            06/02/09---534

            06/03/09---456

            etc. for around 10 dates per list 

             

            As you can tell, I have a steep learning curve to even understand your suggestions.

             

            Thanks for the help!

             

            Rick 

            • 3. Re: Summary field and list dependent on date.
              mrvodka
                

              Getting a report like this can be done easily if you follow what i suggest earlier.

               

              06/01/09---250

              06/02/09---534

              06/03/09---456

               

               

              You would create a subsummary report, grouped by the date. You dont need the body part as that would give you details as such

              06/01/09---250

                 100

                 100

                  50

              06/02/09---534

                 520

                  14

              06/03/09---456

                 456

               

               

              Just put the summary field ( which adds up your guest count ) in the subsummary part along with the date.

               

               

               

              As for what you were talking about for your secretaries, this is something different that you had not mentioned before. Ifyou want to check how many guests there are for a particular date that they have entered, then create a new self join relationship keyed on the date field. Now create a new calculation field with formula: Sum ( SelfJoin::guest ). Put that calc on the layout. 

               

              Now whenever the user enters in a date, it will say how many total guest there are for that date since it relates to all other records with the same date via the self join.

               

              • 4. Re: Summary field and list dependent on date.
                rickscarborough
                  

                Thank You!

                 

                This will give me a direction and keep me busy for a while.

                 

                Rick