4 Replies Latest reply on May 3, 2014 10:45 AM by kll3113

    how to create layout and relationship like this?

    kll3113

      Title

      how to create layout and relationship like this?

      Post

           Hello Everyone, 

           i am new to FileMaker Pro and starting reading FileMaker training basic and FileMaker pro 12: missing manual 

           i am using FileMaker Pro 13 and  want to have a database like below described:

           starting from scratch, i am having 5 layouts: customers, product, PO, delivery note, invoice. attached is my relationship graphs for your ref. 

           i know how to put the relationship between customers, PO & product so that when i am in PO layout, i am able  to add line items into what each customers buy. 

           however, when i come to delivery note layout, there is a problem:

           i want to make the delivery note to include several POs as desired, and each POs have their line items and customers details

           i can only set the delivery note to include only 1 PO with, says 4 line items(showing also the corresponding customers details ), but how can i set the relationship to achieve?

           same case to invoice layout, i want to include several desired delivery note, with several POs and their specific details 

           i've searched for some other forum and they ask me to do the sort of line items to get the summary but this can't help since i want to input the desired POs into the delivery note

           thanks for reading this! and your discussion with this would be grateful for me 

           Ricky Chan

            

            

      Manage_Database_for_%E2%80%9CKLL%E2%80%9D.png

        • 1. Re: how to create layout and relationship like this?
          philmodjunk

               There is no table shown named "PO".

               Am I correct that a record in Orders is a PO (Purchase Order? normally, if you are selling goods, its an invoice if you are purchasing goods, it's a purchase order.)

               What is the purpose served by a record in Delivery Note? (Knowing the intended function can help analyze how to set up relationships and layouts.) Does one record in Delivery Note represent one shipment (Delivery) of ordered goods to the customer?

               If an order = a PO, then it would appear that you need to link Delivery Note to orders, not line items and with the primary and foreign key match ups reversed to make it one Delivery Note to many records in Orders:

               Delivery Note-----<Orders

               Delivery Note::DeliveryNoteID = Orders::DeliveryNoteID

               That enables multiple records in orders to link to a single record in orders.

               

                    same case to invoice layout, i want to include several desired delivery note, with several POs and their specific details

               And this is the same issue. your relationship is set up for many invoices to one delivery note. If you want the opposite relationship, you need to reverse the primary to foreign key matchups:

               Invoices::InvoiceID = DeliveryNote::InvoiceID

          • 2. Re: how to create layout and relationship like this?
            kll3113

                 hello, 

                 thanks for your reply on this. it's about the consignment we've currently doing with the consignment shop. 

                 we have customers place orders (or purchase order from customers to be exact) to the consignment shop. once we receive the orders, we proceed and arrange shipment to consignment shop when finished. we would send consignment shop and customers one delivery note each shipment made. format as attached photo. 

                 after shipment, we would issue invoice to consignment shop twice a week, including different delivery note with the line items showing what had been shipped, to ask for payment from consignment shop. 

                 this is the workflow we're currently doing and your assistance on this would be appreciate

            • 3. Re: how to create layout and relationship like this?
              philmodjunk

                   I take it then that each PO is a record in orders and each delivery note documents a shipment of possibly multiple orders. That was my assumptions in my previous post so my comments about the relationship changes you need to make would then all apply correctly to your situation.

              • 4. Re: how to create layout and relationship like this?
                kll3113

                     thanks for your reply!!! we would work on this and revert to you if we can do this!! so kind of you!