2 Replies Latest reply on Jan 18, 2015 2:31 PM by RogerBirch

    Year's Total (of bookings)



      Year's Total (of bookings)


      Having created a database to record all my hotel reservations, i want a field to show the total of money received todate but only for this year (2015) not previous years.

      I have a field that I record the year, i.e 2015 and (amongst others) a field for showing the booking cost (Invoice) against each client and I know that I need to create a field base on the total of all the "Invoices" for 2015.

      I believe this should be a summary field (or should it be a calculation?) but I do not see how to restrict my new field referring only to a specific year and comimg up with the running total.

      In simple terms, I have taken 3 bookings this year, £100, £150 and £225. I want a field showing me the figure of £475 which will increase each time I take another booking.

      I look forward to any help in creating this. Thank you.

        • 1. Re: Year's Total (of bookings)

          If you have this relationship:

          LayoutTable::ThisYear = Reservations::Year

          then you can either:

          Define a calculation field in LayoutTable as Sum ( Reservations::InvoiceTotal )

          or define a summary field to compute the total of InvoiceTable in Reservations and then place this summary field on your LayoutTable's Layout

          Both will show the same total. The relationship limits the total to just Reservations made in the year specified in ThisYear. ThisYear can be a number field (possibly global) where you specify the year or it can be an unstored calculation field defined as:

          Year ( Get ( CurrentDate ) )

          if you want to "hardwire" it to always match but the current date's year.

          This may require creating a new Tutorial: What are Table Occurrences? of Reservations in order to set up a relationship that does not interfere with existing relationships linking LayoutTable to Reservations.

          The ExecuteSQL() function (not the script step) could also compute this total without modifying your relationship graph.

          • 2. Re: Year's Total (of bookings)

            Thanks PhilModJunk for responding. I feel that your suggestion has further complicated my thinking but will work on it!