2 Replies Latest reply on Jun 23, 2016 1:59 PM by StephenWonfor

    Customer record with multiple addresses/locations?

    katatak

      Gettings All,

       

      I'm having difficulty getting some of my customer files to work properly with the invoice files. I am not new to Filemaker, but I am new to this version. I've actually not used it much in the last 5 years, so obviously a lot has changed with the program since then.

       

      Here is my issue... I am using the starter solutions for my customer info and invoices. I'm an interior decorator and bill my clients both for time and items purchased. The problem I'm having is that I have some customers who have more than one home.

       

      For instance, I have a customer who has 3 homes. One is their primary residence, the other two are rental homes. Decorating work needs to be done in all three locations. One house could need $1000 of work, another $3000, and the last one $500. So on the customer details page I set up a new field called Sub Division. This is so I can see at a glance the rough location of the property I'll be working in. But now I'm trying to figure out how to bill that customer (who has the same billing address regardless of the property location) for the correct location. I have the customer record with all of their contact info, but still need to show which property location is getting the work done based on the Sub Division. This way when I create an invoice (which pulls the info from the customer details page), I can choose which location the invoices is for.

       

      I hope this all made sense. Any assistance you could offer would be greatly appreciated. :-)

       

      Thanks!

      Kat

        • 1. Re: Customer record with multiple addresses/locations?
          mikebeargie

          Hi Kat,

           

          Even though filemaker has changed drastically in the past five years, the normalization of database structure really hasn't changed much since version 7 was introduced in 2004.

           

          You have the option of keeping a set of fields for each address in your customers table which I think you are doing now, or a better option may be to normalize your database and store a separate table of addresses that relates to customers.

           

          With a separate table for addresses, you could tie each invoice line item (time or product) to an address, as well as a customer. This would allow you to use those different "contexts" from your invoices.

           

          So your relationships would look like:

           

          Invoices::CustomerID = Customers::ID

          Invoices::BillingAddressID = Addresses::ID

          Addresses::CustomerID = Customers::ID

          InvoiceItems::CustomerID = Customers::ID

          InvoiceItems::AddressID = Addresses::ID

          InvoiceItems::InvoiceID = Invoices::ID

           

          Your invoice layout should already be a list of InvoiceItems, so with the above relational structure you should be able to show:

          -The related invoice details (invoice number, date, etc..)

          -The customer details related to the invoice (name, phone, email, etc..)

          -The billing address associated with the invoice

          -The service address associated with each line on the invoice.

           

          Additionally, if you added a subsummary part to your invoice, you could "group" invoice lines together by AddressID, so that each service address on the invoice had it's own group of charges.

          • 2. Re: Customer record with multiple addresses/locations?
            StephenWonfor

            Kat

             

            You could also consider adding an id_group field.  You would autoset its value to the same as id_customer.  Then, for multiple locations, you would simply reset id_group for the other locations to the value of id_group for the main (billing location).  So your "Customer" table has two types of Customer - one that is a billing location where id_group = id_customer, the service locations would be where id_group ≠ id_customer.  You can join them together using id_group as the key.

            You can only create invoices for the billing location, but can add data from related service locations in the same id_group.

            In your Invoice line items portal on the Invoice you would have an id_customer field that has a dynamic list that only contains id_customers from within the invoice id_group.

            As Mike says you can then sort the invoice details for the actual invoice to reflect each separate location.


            Stephen