5 Replies Latest reply on Nov 12, 2015 8:54 AM by Handri

    Rental Shop : Relationship Design

    Handri

      Hi everyone,

      My name is Handri, I am here to get some advices, guides, and may be solutions for my projects.

      My level is a beginner for the FMPA14. And I have watched some tutorials on Lynda.com by Chris Ippolite.

      I decided to make a solution for a Rental Shop.

      Which is the flowchart will be

       

      1. CUSTOMER will rent a PRODUCTS  è the shop will issue a SLIP (Rental Start Date)
      • è Customer return the PRODUCTS è the shop will issue a SLIP (Rental End Date) & the shop will issue RECEIPT

       

      Products has a SUPPLIER which the supplier items (PRODUCTS) when its rented the SUPPLIER will get a PAYMENT according the SLIPS amount on the items (PRODUCTS) in percentage.

       

      At the end of the month the shop will have to make a report to a supplier (which can be one of the CUSTOMER) and issue a payment to SUPPLIERS.

       

      Tables & Relationship:

      1. Customer(pkCustomerID)
      2. Products(pkProductsID & fkSupplierID)
      3. Slip(pkSlipID & fkCustomerID)
      4. SlipData(fkSlipID & fkProductsID)
      5. Supplier (pkSupplierID)
      6. Payment (pkPaymentID & fkSupplierID)

       

      The Question is :

      How I manage the relationship to make this works ?

       

      Screenshot (3).png

       

      Please kindly advice

       

      Regards,

       

       

      Handri

        • 1. Re: Rental Shop : Relationship Design
          ChrisJohnston

          How about basing everything on slips, so I would call that invoices. Then you can start from the idea that customer creates an invoice every time he/she rents. This Invoices table would need a relationship to products and suppliers most likely with a join table. Maybe even a join table between Products and Supplier.

           

          Customers----<Invoices>----<Lines[join]>----<Products>----Supplier

          Customers----<Payment

          • 2. Re: Rental Shop : Relationship Design
            Handri

            Hi Chris,     

             

            I would say thanks for reply. I really appreciate it.

            I forgot that I have another table.

             

            In the first place, I also thought that I rather use INVOICE table and the joint table (INVOICE DATA).
            The things is the shop will have to issue a temporary INVOICE (which I called it SLIP) and when the customer return the Items(PRODUCTS), the shop will issue a RECEIPT which the total amount will be different with the SLIP depending on the return day (which has discount rate base on the return day).

             

            So its gonna be like Temporary Invoice (SLIP) & Final Invoice (RECEIPT).

             

             

            Tables & Relationship:

            1. Customer(pkCustomerID)
            2. Products(pkProductsID & fkSupplierID)
            3. Slip(pkSlipID & fkCustomerID)
            4. SlipData(fkSlipID & fkProductsID)
            5. Receipt (ReceiptIDpk & SlipIDfk & ProductsID fk)
            6. Supplier (pkSupplierID)
            7. Payment (pkPaymentID & fkSupplierID)

             

            Is your

             

            Customers----<Invoices>----<Lines[join]>----<Products>----Supplier

            Customers----<Payment

             

            still works on that ?

             

             

            Please advice

            • 3. Re: Rental Shop : Relationship Design
              ChrisJohnston

              I would not worry so much about the temporary nature of how your invoice works before you try a relationship. FileMaker is more than capable of handling the specifics of how a record (in your case invoice) will work or change. For example a script could take care of whether Product is checked in (charged) or out, based on a field. If an Invoice is related to a Customer and a Product, It can be calculated later. In FileMaker relationships are the key. Also think of the idea of starting an Invoice and completing it. The whole time this Invoice remains related to Customers and Products. The least amount of tables the better, as long as they can get the job done. Sounds to me like what we have can work.

              • 4. Re: Rental Shop : Relationship Design
                Handri

                Hi Chris,

                 

                Thank you for your advice. I will try it .

                I will report it back to you.

                 

                 

                Regards,

                 

                Handri

                • 5. Re: Rental Shop : Relationship Design
                  Handri

                  Hi Chris,

                  I decided just to make the invoice

                  and this is the new problem

                   

                  Rental Shop : Multiple Relationship Diagram