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.
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?
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
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.
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?
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 )
I will try to do it now. If I get any more questions regarding this matter is it ok to post them here?
That's what this forum is for.
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.
What is the difference between a "customer" and a "contact"? By name, they would seem to be one and the same thing.
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.
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?
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.
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?.
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.