1 2 Previous Next 15 Replies Latest reply on Jun 22, 2014 6:11 PM by philmodjunk

    Need help with my ERD.



      Need help with my ERD.




           I am new to FileMaker. I am trying to design a solution for my business so my invoicing will be more efficient. I am running a small electrical business. Sometimes I do my own jobs where I charge fixed price and sometimes I supply labour for bigger firms where I charge hourly rates for each person I supply. This rate values are sometimes different and sometimes the same for clients. Each rate has got its name which are the same for every client. I.e. Rate A, Rate B, Rate C, Fare, Travel Time, OTA.     Also client can change a rate value over time. I can't figure out where shall I put my Rates.At the minute my ERD looks like this.

           customer ---//Contact ---//Invoice//---InvoiceLineItems//---Product


           Any help much appreciated.


           Thank you


        • 1. Re: Need help with my ERD.

               It may help to think of the Product table as a "Products and Services" table as you can list your different labor and travel charge items as additional records in this table. That would make Rate A, Rate B, Rate C, Fare, Travel Time, OTA different records in your products table and you can select the appropriate rates and specify a quantity (number of man hours) to add that charge to the invoice.

               All product and labor unit charges should be copied by an auto-enter field option to InvoiceLineItems. That way, future changes to the cost of an item in products will only affect the next new invoice to be created and past invoices will still show the amounts that were "current" at the time the invoice was created.

          • 2. Re: Need help with my ERD.

                 Ok, I think I understand that. 

                 I have got 20 different clients that I supply Labour to. Most of them has got different hourly rates. Is there a way to have a drop down field that will only show rates linked to this customer?

            • 3. Re: Need help with my ERD.

                   That would be a conditional value list. But you may find that looking up an hourly rate from a field in the customer table into the lineitems table an easier to manage option.

                   If you do want to use a conditional value list, here are my links on the subject:

                   There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                   The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                   Forum Tutorial: Custom Value List?

                   Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                   Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                   Hierarchical Conditional Value lists: Conditional Value List Question

                   Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                   Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

              • 4. Re: Need help with my ERD.

                     Thanks Phil.


                     I will leave conditional value lists for later. Don't want to put to much on me at that stage. 

                     Just to clarify. If I use a field in customer table as my hourly rate value and produce an invoice. This invoice will not be changed if my hourly rate for this customer increase in future. Is that correct? Is there anything that I need to set up to make sure that this will work properly?

                • 5. Re: Need help with my ERD.

                       It depends on how you get this value from the Customer table into InvoiceLineItems. If the value in Customer is copied to line items, then a future change of this rate in the customer table will not affect the value in previously created records in InvoiceLineItems.

                       An auto-enter calculation for the unit price/rate might look like this in the InvoiceLineItems table:

                       If ( Description = "Labor Charge" ; Customer::LaborRate ; Products::UnitCost )

                  • 6. Re: Need help with my ERD.

                         I will try to do it now. If I get any more questions regarding this matter is it ok to post them here?

                    • 7. Re: Need help with my ERD.

                           That's what this forum is for.

                      • 8. Re: Need help with my ERD.

                             Ok. Went further into my EDR. 

                             I am having difficulties with naming my joint table.


                             I can have many clients (contacts) on on event(contract) and one client can have many events(contracts) over time. What name and information should I use for a joint table. This all need to fit to my previous example.

                        customer ---//Contact ---//Invoice---//InvoiceLineItems//---Product&Services

                             JOBS(Events and Contracts) - new entity


                             Maybe you will find a better name for this new entity. 

                             Any help much apreciated.

                        • 9. Re: Need help with my ERD.

                               What is the difference between a "customer" and a "contact"? By name, they would seem to be one and the same thing.

                          • 10. Re: Need help with my ERD.

                                 Customer is a company that i work for few different contacts.

                                 i.e. John can give me labour jobs - I charge hourly rates for my services.

                                       Mark can give me contract jobs - I charge fixed price for my services.

                                 They both work for the same company. My invoice is still produced for XYZ ltd. Although i got jobs through different contacts in this company.

                                 Hope that clarify that a bit.

                            • 11. Re: Need help with my ERD.

                                   Yet you refer to a contact as a "client" that's what had me confused.

                                   I would think that you'd want to link a customer record to a contract record as it would normally be the customer, not a contact that works for that customer that contracts for your services.

                                   Will every invoice be linked to a contract? Or do you bill for some products/services without one from time to time?

                              • 12. Re: Need help with my ERD.

                                     I do sometimes. But i would prefer to know for which event this invoice was for. Every year we have got same events. I can ring my client few weeks back to ask him if he wants the same amount of people as last year. Strait away I will roughly now my profit. That's why i would prefer to link every invoice to event. With domestic work I will not need that facility but haven't got an idea how to do it differently.

                                • 13. Re: Need help with my ERD.

                                       The point that I am trying to make is that since you do have invoices that are not linked to a specific Event ( contract), you need to set this up in a way where you can link to data in contacts and customers even if there is no Event(contract) for that specific invoice.

                                       But first things first:

                                       Start with these relationships:


                                       Customer::__pkCustomerID = Customer_Event::_fkCustomerID
                                       Event::__pkEventID = Customer_Event::_fkEventID

                                       You can place a portal to Customer_Event on the Customer layout to list and select  Event records for each given Customer record. Fields from Event can be included in the Portal to show additional info about each selected Event record and the _fkEventID field can be set up with a value list for selecting Event records by their ID field.

                                       From there, you'll want to link each record in Customer_Event to the invoice that bills for it--which will require adding an additional Invoice Tutorial: What are Table Occurrences?.



                                  • 14. Re: Need help with my ERD.

                                         Ok, Please have a look at the photo. I hope i get it wright.


                                         How can I sort it out, if I do an invoice not for an EVENT/SHOW but for a House (i.e. I have rewire whole house). Problem i got here is that they will have different attributes.

                                         EVENT will have attributes like: Event name, Organiser, Organiser Adress, Orginiser Cotnact etc,

                                         House will have attributes like: Property Type, Property Address etc.

                                         I will be adding extra tables in future to improve functionality of this database in time.

                                         Thank you for your help Phil.

                                    1 2 Previous Next