3 Replies Latest reply on Mar 22, 2012 2:25 PM by philmodjunk

    Calculating one to multiple relationships



      Calculating one to multiple relationships


      The scenario: 

      In a FM database we have two tables: appointments and sales. The company sells multiple products so an appointment might generate multiple sales.

      Currently the relationship appointment -> sale is one-to-one. Yet that does not work for obvious business reasons. An appointment could be related to multiple sales.

      The current situation: 

      When appointment1234 turns into a sale the first job number is PROD1SALE1234. Then someone manually has to duplicate it and change it to reflect the second sale with a job number of PROD2SALE1234. As you can see the SALE1234 remains consistent and currently is the only mechanism we have to figure out how many sales we have for one appointment.

      The goal: 

      We need an automated way to calculate how many sales we have on each appointment. Since there is only one entry in the appointments table it only reports back one sale from the sales table. I want it to look on all entries in the sales table, count how many SALE1234 (independent of the first PRODx characters) entries is has and report back to me automatically.

      Any ideas on how to accomplish this?

        • 1. Re: Calculating one to multiple relationships

          Sounds like you should have this set of tables and relationships:

          Clients---<appointments---<sales>---Products  (---< means one to many)

          Take a look at the invoicing starter solution where one invoice can list multiple items sold in a portal to a related table.

          You can set up your appointments table to function as an invoice table and sales can serve in place of "LineItems":

          Appointments::__pk_apptID = Sales::_fk_apptID

          Enable "Allow creation of records via this relationship" for Sales in this relationship.

          Put a portal to Sales on your appoinments layout and you can log multiple sales simply by entering data into the rows of the portal.

          With regards to your data "PROD1SALE1234, PROD2SALE1234, ..."

          1234 is the value stored in __pk_apptID and _fk_apptID. This data will be entered for you each time you enter data into an empty portal row.

          "PROD1" would be entered in a second field and could be a value list of your available items for sale--often taken from a Products tatble that would be linked to Sales by ProductID.

          "SALE" would seem to serve no purpose here so I'm not sure why you would need it.

          • 2. Re: Calculating one to multiple relationships

            Would then that work to find out how many sales I have from each appointment?

            If ( Appointments::__pk_apptID = Sales::_fk_apptID ), 1, 0)


            • 3. Re: Calculating one to multiple relationships

              No, but if you defined this field in the appointment table:

              Count ( Sales::_fk_apptId )

              you could get that count.

              Also, a "count" summary field defined in Sales would return the same count.