Quotes to Jobs to Invoice - Data separation or not?

[AndyCan]

Evening all,

I am developing a database for a small freelance design business of my own and need some help on the Quote->Job->Invoice workflow and whether or not to separate line items into different tables.

Basically, first comes the Quote with various Quote Line Items for each stage or work. Line item fields include:

Category

Description

Estimated Hours

Hourly Rate

Markup

Calculated Cost

etc.

With not all fields being show on the customer quotation for obvious reasons.

After the quotation is approved by the client, next we have to raise a Job with the same tasks as listed in the quote (Category and Description being the same as the quote, but with additional Line Item fields such as:

Time Started

Status

Total Hours

Notes

etc.

Lastly comes the invoice, again with the same Description, costs for each task, etc.

Now the question is: Should I have different line item tables for Quotes, Jobs and Invoices tables or should i just contain all the line items in one table that could be called Tasks and then connect that to the Quotes, Jobs and Invoices tables? How could i accomplish a three way join like that? Would each line item have to have three foreign keys for each parent table, and how would i populate the Jobs foreign key and the Invoices foreign once the quote is done?

I understand that it is generally recommended not to duplicate data in a RDBMS, but i wonder if i might be setting myself up for problems in the future by combining everything. Perhaps would be inflexible as it may be too optimistic to expect everything to stay the same from Quote through to Invoice?

Please let me know what you think and any recommendations for the best way to structure this would be greatly appreciated.

Thanks!!

[Dr. Evil]

I'm having the same type of issue... what is the BEST table set up for what I want to accomplish.

For your problem, here are my thoughts.

You said you don't want to get your self in trouble by combining "everything." But I think you will get in more trouble having the same data in three different places, we could go on and on about why this is a bad idea.

So with that being said, I would have a Projects as your parent table and Assignments as the child table. Assignments would carry all the info. You could label the assignment as Quoting, Approved, Complete.

Then have a Clients table with a child table called Invoices. Create a script that looks for all un-invoiced Assignments for Client, then creates an Invoice, and then marks the billable Assignments with the newly created Invoice ID. Now that the Assignment has an Invoice ID, your solution will know it is marked billed.

[AndyCan]

I found this other post which seems to confirm that it's best to keep quote line items and job/invoice line items separate.

http://www.fmforums.com/forum/showtopic.php?tid/213501/

I guess it's easy to envisage a situation where an invoice could differ from a quote, after all, we can't foresee everything when doing the quote and expenses might need to be added.

[AndyCan]

With what i've learnt from reading other posts like the one above, i'd like to clarify my question a bit.

I have decided to copy Quote line items across to Job line items when a quote is approved by the client and a job is opened. A quote and subsequent job could be something like 'Company Report' and the line items in both would be things like 'Client Consultation', 'Copywriting', 'Layout', First Draft', etc.

My question now is how do i copy the selected line item fields from the quote to the job? My first thought was with lookup fields in the job line items. Here is a screen capture of my relationship graph for that section:

At the moment, nothing is happening in the Job line items table, so i must be doing something wrong. The Quote ID is placed in a new job record when i click on a 'Raise Job' button, so at least i know the Job table is aware of the connection to Quotes. Where a i going wrong? Should this be done with copy/paste scripts or some kind of import script or am i going in the right direction with lookup fields?

Thanks again,

Andy.

[David McQueen]

Just a thought here:

Put line itms for both quotes and invoices in the same table, but flag one as quote and the other as billing item or whatever.

Invoices are really a table to link billing line items. Project is really a table to link quoted line items. Again, consolidate both but flag each. Also cross link them so that a quote is related to a project and vice versa.

The reason for the suggestion: It then makes it incredibly easy to report for each and to report quote versus billed either at the project level or at the individual line item level. The data is all in the same places and calculated fields can be used to make one negative for reporting purposes.

[AndyCan]

David,

Thanks for your input. Let me see if i've understood you correctly...

The line items for Quotes, Jobs and the eventual Invoice all go into the same table.

For each item (tasks that the work is divided into) i would have three entries, but they would be in the single line items table and each would be flagged to note whether it is part of a quote, a job task or an invoice item.

I can understand how this might help with reporting and calculations afterwards, but it seems to me that there would be a lot of repetition in the table and an unwieldy number of fields to accommodate all three circumstances. Let me list the necessary fields for each stage so that we can talk in real world terms. Again, this is for a design firm billing with fixed prices or hourly rates depending on the job.

QUOTE ITEMS

Category, Description, Quote Description, Estimated Hours/Qty, Cost, Markup, Total Cost, Invoice Amount, Status, Locked (Status = Sent)

JOB ITEMS

Category, Description, Staff Member, Start Date, Due Date, Completion Date, Notes, Progress, Status, Estimated Time, Time Spent, Time Remaining, Locked (Status = Finished)

INVOICE ITEMS

Category, Description, Invoice Description, Hours/Qty, Cost Rate, Cost, Markup, Invoice Total excl. tax, Tax Rate, Tax, Total icl. Tax, Status, Locked (Status = Invoice Sent)

Now this is a rough example, but representative of what fields might be in each stage from Quote through to Invoice. It's important to note the project management phase in the middle with the jobs items and their respective time sheets, completion amounts, etc.

I do wonder if it's really necessary to combine all those fields into one table and it wouldn't be simpler just to copy across the few fields that do stay constant throughout the project (and yet retain the ability to edit the contents slightly between stages if required)

For example, when raising a job from a quotation we could just copy across the Category, Description and Estimated Hours for each element of the quote. Then from within the Job table/layout we assign the staff member, update the progress, add notes, etc.

And when the Job is finished we raise the invoice and add just the billable Items from the Job. I say just the billable items, because job items may have been added for project management purposes and which are not itemised in the final invoice. With each billable item we would copy the Category, Description and Time Spent/Qty.

Sorry for the rather long post, but i think a real world example was needed for people to understand my situation.

As always, all comments are greatly appreciated!

Andy.

Edited March 12, 2010 by Guest

[AndyCan]

They say a picture is worth a thousand words, so here's my picture!... Well, illustration really :

I've included a Projects table to tie together the different stages and contain the brief, client name, etc.

Comments are invited on the general logic of this structure and i'm still not sure how to copy the Category, Description, etc between the stages when raising a job or an invoice.

Thanks!

Andy.

P.S. I forgot to add it to the illustration, but the Line Items tables would obviously have their respective foreign key fields linking the items to the parent table.

Edited March 12, 2010 by Guest
Added P.S.

[David McQueen]

I think the thing to remember here is that it is a suggestion ie something to look at and see if it fits your situation. There are always lots of ways to skin the cat. this may or may not fit. You be the one to decide

I would look at the tables you are going to form for line items of various types and see if there is any kind of field concordance ie:

time to time

dollars to dollars

etc.

I would do the same with projects and invoices.

If there is a duality such that it will make reporting better and simpler, then it may be worth pursuing.

The idea is in say a dollars field, it does not matter if it is a billing item or a budgeted item, it is still dollars and would get its context from the flag and then reporting budgeted versus billed dollars becomes simpler.

If it works for you, it has given you another tool. If it does not work, maybe it is useful next year. What you don't want is: :bang:

[AndyCan]

David,

I'd be interested to hear you opinion on this particular situation having seen the illustration i recently uploaded. As you say, there are many ways to skin a cat. I just don't want to have to skin the same poor old cat a number of times because my logic wasn't sound the first time.

Save the kitties!

[David McQueen]

David,

I'd be interested to hear you opinion on this particular situation having seen the illustration i recently uploaded. As you say, there are many ways to skin a cat. I just don't want to have to skin the same poor old cat a number of times because my logic wasn't sound the first time.

Save the kitties!

There looks to be good congruence here.

Right now your diagram gives appearance to:

Quote line items -data moves-> Approved Jobs

Approved Jobs -data moves->Completed jobs ->Invoice line Items table.

My thought would be a quote remains a quote for later comparison purposes. It does not matter if it sits in its own table or not. It is a record of your success or failure at that level. So you have the right idea. A quote does not gradually change into a job.

To me, it looks as if Jobs table items should also be invoice line items. Invoice is just a way of collecting them and showing them in a portal.

Estimated hours in Jobs does not exist in jobs. The related value from the quote should be brought in. If there is a separate table or in a flagged table with a self relation, there is no need to duplicate that data. Plus, if a quote is updated, that value automatically updates.

The long and the short of it appears that at minimum if what I see as a separate invoices line item table is what is there, it is potentially an extra table.

It looks like in the real world the job actually gets done "on paper" or "on screen" twice. Once at the quote level and once in the real world. In both cases the model calls for all aspects of the job including monetary aspects to be compiled.

if so, it looks like it could be done the way I suggested, given there are not a bunch of other fields and processes that happen in the real job.

That would give the opportunity for some easy reporting, if at the same time it does not mess you up in doing the project. There would be some flagging and calculated fields involved. You would have to understand each process prior.

The key to having it be successful from a reporting point is that the same field in quote carries the comparative value for jobs. Then comparison reports fro example become $ as a Quote - $ Actual Cost = Profit.

I really can't get too detailed. But it looks as if the possibility is there.

[Fenton]

I would agree with David, on thinking about the single line items table. It will require some thinking; but it will require less data movement during operation.

I've done it the other way (long ago), with separate line items tables. It's OK, done via Import. But often redundant. Unless you have a situation where there is much messing about with Quotes (and their lines), such as "revisions", "alternate" quotes. That is, separate "quote" records, which are really just variations of the original quote. Doing such things (which this customer did often), would add lots of extraneous line items, only a few of which would actually be used on a Job (not to mention entire quotes which would not be used).*

If you forsee such a situation, I would keep the Quote line items separate; they just add too many records which are never part of a real job.

If there is less messing about, and the line items would match fairly closely between a quote and a job, then a single line items table should be file.

*Of course, a lot of this redundancy could be handled by deleting the extraneous quote records (linked to delete their line items; but only if not on a job already). But clients do not always do such maintenance regularly.

[AndyCan]

Thanks for your help guys. David raised some very good points and it's good to hear Fenton agrees with him. It looks like a single shared line items table is the way to go then.

Does it matter that at times i may have line entries that only relate to one table? For example, i may add tasks to the job which weren't in the quote and won't be in the invoice. Another example is an invoice for a 50% advance payment that again won't be related to quote or job items. The same 50% would then appear as a credit on the final invoice for all the work.

My main reason for separating the items was to have some informal flexibility and to maybe bulk up the project management part in the future, but maybe i can have all that with the single table solution.

Anyway, here's how i understand the combined line items table to work with the other tables...

I must admit, it's not 100% clear to me how it could work, especially the part about how the foreign keys would reference the parent tables, but i'm sure it will become clearer as i keep on working at it.

Thanks for your time guys. Please just clear up my doubt in the second paragraph, but it looks like i'm on the right path now.

Andy.

[Fenton]

The way I see doing this is that, rather than have multiple tables, you have multiple ID fields, in the common line items table. There would be: QuoteID, JobID, TaskID

So, if a line item does not have a value in one of the ID fields, it tells who what it is. If it has only a QuoteID, it's just on a quote. Adding a JobID (which you'd do with a Loop or Replace upon creation of the Job) would tie the existing QuoteID ones to a Job.

If it has a TaskID, then it is a task. Otherwise it's a regular job line item, which would be what the Invoice sees.

Some mechanism will need to disallow modifications to the critical Quote ltem fields once it becomes a Job. I was toying with the idea of sticking them into separate "original quote" fields, to preserve the original quote (of which there is only 1), freeing the fields for later modifications.

So there's some things to work out. But overall, if your quotes are fairly consistent with your jobs (at first anyway), then it should all work out.

I haven't done exactly this, but I've done some of this "multiple IDs, multi-assignments" to line items, using them also for "shipping" & "receiving" (which are shells in a way, much like an "invoice"). And it worked fine.

I first used, and still use, a simplied version of this with Notes fields, which need to be seen by, but filtered to, several different entities.

[David McQueen]

You should be able to add tasks related to Jobs. Again, more than one way to skin the cat:

1. Tasks are defined with a direct link to the specific job line item. No flagging there, just a direct relationship.

2. The jobs line item is flagged with both "Job" and the ProjectID (Make it text). In tasks is a calculated field that is indexed that holds the value "Job". Then from Tasks, one can see all line items pertaining to jobs for that project using a multi predicate relation

c_Job :: Job

And

g_ProjectID::ProjectID

or by using a concatenated calculated key in each table of the formation:

Job & " " & ProjectID

(Sorry, I still like concatenated keys, even better compound concatenated keys .. They can be really useful for display purposes even though I know it is an archaic form)

Back to your main project:

1. Take some time and think it through. I just finished one of these. It took me about 3 days to get my head around it and I had a couple of false starts.

If you can get your head half around it, you will probably find the rest as you are doing it ( I know, not the best of design strategies)

2. Take your time doing it and set up a horrendous wide list view for development purposes so you can see your flags and see what is set and what is not. It is easier if you can see it.

The one I just did is basic bookkeeping, holding invoice line items, invoice payments and general expenses all in the same table. It then made Accounts receivable, Value Added Tax reports and Income and Expense statements a breeze using combinations of calculated fields to create negativity where required. That is why I suggested it as a possible strategy here. It worked well in my case. Reporting from three or four different tables would have been very difficult.

[David McQueen]

The way I see doing this is that, rather than have multiple tables, you have multiple ID fields, in the common line items table. There would be: QuoteID, JobID, TaskID .... Big Snip

Yup, yet another way.

That poor cat has no fur left.

One warning here regarding basic entities and relationships and every day logic

Ask yourself:

1. Is it reasonable and usual that there are many tasks to one job line item?

2. Is there only a minor field congruence?

My thought would be if so to either, then tasks are in their own table. Remember, way back when this started, we were looking for congruence for ease of reporting.

[AndyCan]

Adding a JobID (which you'd do with a Loop or Replace upon creation of the Job) would tie the existing QuoteID ones to a Job

Morning Guys,

I managed to make some good progress on the database over the weekend and eventually went with the idea of putting multiple foreign keys in the shared line items table. I have a small scripting question though which is related to Fenton's comment about adding the JobID to the line items upon creation of a Job.

Here's the Quote Layout with the Job creation button:

And here is the script that i'm having some trouble with. Basically i'm not sure how to execute the GTRR or find set for the line items related to the active quote. To be honest, i'm stabbing in the dark here as i'm not sure how to use this technique. Maybe you could tell me where i'm going wrong. Once i get that part right, i think it's just a case of looping through the results and adding the JobID. Currently, everything works fine up to where i run the GTRR:

By the way, is it possible to copy the script text as opposed to having to do a screen capture?

Have a good Monday and i look forward to hearing from you soon!

Andy.

[Fenton]

That looks like the routine. But there are couple things missing.

1. Go To Related Record [ Quotes Shared items; using layout "Quote Line Items" ] (not )

1.a. It really should check that the Quote HAS line items BEFORE going there, or doing anything further.

If [ not IsEmpty ( Quote Shared Items::Quote ID ) ]

    Go To Related Record [ Quotes Shared items; using layout "Quote Line Items layout ]

2. The Loop is missing a critical step, before the End Loop.

   Go to Record [ Next; Exit after last ] (option; checkbox on lower left)

End Loop

I see that you have regular FileMaker Pro, not FileMaker Pro Advanced. That is going to make it difficult for you to see what's going on during scripts. FileMaker Pro Advanced has a Script Debugger tool, which lets you run scripts one step at a time. You would likely have noticed both the problems above if you ran with it.

Of course if you bought it now, you'd get FileMaker 11, which would require you to relearn the way you do some things, due to the new Inspector (which I have ambivalent feelings about). But, in a way, you might was well learn the way FileMaker will work in the future.