4 Replies Latest reply on Apr 8, 2013 11:39 PM by rbmn67

    Many-to-many new relationship creation dialogue

    rbmn67

      Hi All,

       

      I am not sure which approach will serve the users best, while from a FM point of view, keeping it simple and elegant.

       

      High level, we have serialized products, changing ownership between customers over time, more specifically, boats changing owners.

      The datamodel has tables CUSTOMERS, BOATS and JOIN_CUST_BOAT (incl. purchase date, sell date etc.), which works fine in views, portals etc.

      Relations are set (CUSTOMER::Cust_ID = BOATS::fk_Cust_ID, JOIN_CUST_BOAT::fk_Cust_ID and JOIN_REL_BOAT::fk_Boat_ID).

       

      In order to maintain ownership, I need to setup dialogues for:

      - New boats, not assigned to any customer, assigning to an existing customer as the new owner (sale),

      - Existing boats with existing owners, transfering ownership from one customer to another (both sale and purchase),

      - Existing boats with existing owners, transfering ownership to new customers (both sale and purchase).

      The ownership mutation should start from either a layout based on CUSTOMERS, with current record pointing to a specific customer, or a layout based on BOATS, with current record pointing to a specific Boat.

      Either way, a new dialogue / record needs to be created in JOIN_CUST_BOAT.

      Or, come to think of it, an exisiting JOIN_CUST_BOAT record needs to be changed (end date of ownership for an exisiting customer - boat relationship).

       

      Functional requirement is a pick-list (special "choice" layout?) to be provided to the user, showing several fields of ALL records of the relevant table (CUSTOMERS or BOATS).

      Either a list of all Customers to choose from (from a BOAT perspective), or a list of all Boats to choose from (from a CUSTOMER perspective).

      Creating a view / layout in order for the users to choose either BOAT or RELATION leads to a found recordset, losing the originating CUSTOMER record or BOAT record, resp. Customer_ID or Boat_ID.

       

      Now, for the question: what is the best approach in FM to achieve this?

      Using a global variable in a table to store the originating Relation_ID / Boat_ID, or using a global script variable?

      Is there a possibility of showing all records of a different table in a different window to choose from without changing the original found record set?

      Should index refreshing / rebuilding be adressed within the script(s)?

      Or ......?

       

      Any suggestion -even just a short, high level approach to get me started - will be greatly appreaciated.

       

      Kind regards,

      Ruud Bouman.

        • 1. Re: Many-to-many new relationship creation dialogue
          jetalmage

          Sounds to me like it would be cleaner to have a separate table for the ownership transactions.

           

          James

          • 2. Re: Many-to-many new relationship creation dialogue
            rbmn67

            Hi James,

             

            Your suggestion is actually what made me post this question. I started with the idea of a transaction table, since ownership is started and ended by one: a purchase and a sell. What I run into is that the transaction table would need to create a 'conditional' join between boat and customer: after a purchase, a join is created. Based on the transaction records, a 'purchase' initiates a join, where the join should be deleted if, for that particular boat and customer / join record, a 'sell' transaction is performed.

             

            From a process perspective of ownership, this is the elegant solution, however, I am not sure how to manage the join table in this setup in Filemaker, maintaining the join table based upon a transaction table. The join table is also serving joins for ownership history per customer, resulting in a record set of both past and current boat ownership for a customer, by means of the ownership start and end date in the join table.

             

            Does that make sense, am I correct in assuming your suggestion is to update the join table with every new 'transaction'?

             

            With kind regards,

            Ruud.

             

            Op 5 apr. 2013 om 15:35 heeft jetalmage <noreply@filemaker.com> het volgende geschreven:

             

             

            created by jetalmage in Relationships and Portals - View the full discussion

            Sounds to me like it would be cleaner to have a separate table for the ownership transactions.

             

             

             

            James

             

            Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

            Start a new discussion in Relationships and Portals by email or at FileMaker Technical Network

            Manage your email preferences.

             

            FileMaker Developer Conference 2013 • San Diego, California • August 12-15 • www.filemaker.com/devcon

             

            • 3. Re: Many-to-many new relationship creation dialogue
              LyndsayHowarth

              You don't create then delete anything...

               

              You have a boat (in the boat or product table) in your system which has a current owner. It may have several previous owners.

               

              The transaction table record is generated when that BOAT is sold to (purchased by) a PERSON. This same transaction should record who it was sold by (purchased from) another PERSON. This transaction of course has a date of sale.

               

              (make a self-relationship inside the transaction table by BOAT ID)...

              If the date of sale is equal to the max date of sale dates for that BOAT ID, then that record logically should be the "Current Owner" and could be calculated as such.

               

              HTH

               

              - Lyndsay

              • 4. Re: Many-to-many new relationship creation dialogue
                rbmn67

                Thanks for your reply, certainly helps.