4 Replies Latest reply on Nov 16, 2013 9:55 AM by kopeltd

    Need help on setting up relationship

    kopeltd

      Title

      Need help on setting up relationship

      Post

           Hi,

           I am trying to set up a database to run my little business. I am electrician and my services varies from Labour Supplies to managing a contract.

           At the minute i have got a Customer table a Product table and Invoice table.

           my relationship at the minute looks like that:

           Customers--------//Invoice---------//InvoiceProductJoin//--------Products

           thats ok when my Product is Fuseboard for example.

           But I also sell Labour services where my hourly rate is different depends on customer and also some of them got the same rates.

            

           Any ideas on the solution would be much appreciated.

            

           Please keep in mind that i have never created a working relational database so I am newbie to this stuff. 

           I have watched lots of courses thou on Lynda but still a bit struggling.

           Thanks for any help

           Lukasz

        • 1. Re: Need help on setting up relationship
          philmodjunk

               Hope you have taken a look at the invoices starter solution that comes with FileMaker. It can serve as a "source of ideas" for incorporating into your own database.

               You can treat services as a special kind of "product" and list it in the same portal as the products that you sell to your clients. There are several ways that you can manage the rates you charge for your labor. A simple solution that's easy for a "newbie" to set up is to leave the "price" field in Products blank and type in the rate manually when filling in the invoice. A field on your layout can show the rate for each customer if you include a field for that purpose in your customer table. You could even drag and drop that value into the field in the portal.

               A more sophisticated approach is to set up an auto-enter calculation in InvoiceProductJoin that enters the product price from products when it is a product and the hourly rate from customers when it is a labour charge.

               If ( Description = "Labour" ; Customers::LabourRate ; Products::UnitPrice )

          • 2. Re: Need help on setting up relationship
            kopeltd

                 I would prefer second approach. But where exactly do i have to set up this calculation and how? In a relationship or create an extra field?

            • 3. Re: Need help on setting up relationship
              philmodjunk

                   Neither. You should already have a "price" field in InvoiceProductJoin that looks up the price from Products. If not, you need to add it. You don't want to reference the actual price field in Products in calculations in this table because you won't want old invoices to recalculate to new amounts if you change a price in the products table.

                   Find that field in Manage | Database | Fields. Double Click it to open Field Options. Select the Auto enter tab. Then select calculation and enter this expression there. You'll need to modify the expression to work with the actual names and values you are using in your database.

              • 4. Re: Need help on setting up relationship
                kopeltd

                     Thank you