2 Replies Latest reply on Aug 27, 2014 9:37 AM by sytem.engineering@gmail.com

    How can I sum the fields?



      How can I sum the fields?


           I have two tables that I want to match. One is about reservations and other is about services. One code of reservations can have a lot of codes of services. So, I want to know, how can I sum the fields of related tables? and how do I sum the fields that only  that belong to a single code. For example: Reservation Code 1, Services Code 2,4 and 5, so when I go to the register 1 of reservation I want to see the codes of the services and also their names and prices but I want a sum just of the codes 2,4,5 that belong to the code 1 of reservation.


           Thanks for the help

        • 1. Re: How can I sum the fields?

               If you have this relationship:


               Reservations::ReservationCode = Services::ReservationCode

               then this calculation in a field defined in Reservations:

               Sum ( Services::Price )

               will compute a total price for all Services linked to that reservation. But I suspect that this is more complex than that. I suspect that each time you create a reservations record, you need to select specific services to link to that reservation. This becomes a many to many relationship and you need a related join table to list your selected services for each Reservation:


               Reservations::__pkReservationID = ReservationsLineItems::_fkReservationID
               Services::__pkServicesID = ReservationsLineItems::_fkServiceID

               With this set up, you put a portal to ReservationsLineItems on your Reservations layout. You format the _fkServicesID field with a value list for selecting services. If you enable "allow creation of records via this relationship" for ReservationsLineItems in the Reservations to LineItems relationship, you can add new records to your portal just by selecting a service in the _fkServiceID field.

               A Price field can be defined in ReservationsLineItems that uses an auto-enter field option to copy over the price from the matching Services record. Then Sum ( ReservationsLIneItems::Pric ) will return the total services cost for a given Reservations record.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: How can I sum the fields?

                 Thanks, it was what I needed to know