Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ideas for Quote to Invoice process (2000 all updat

    I am building a database for a self-employed, 1 person operation, electrician.

    The electrician will have to produce a Quote for a Project and it the Quote is accepted by the customer he will then do the work and then produce a final Invoice.

    I am attaching a pdf of the Relationship diagram which will show the tables and how they are related. Data is entered through Forms and Subforms.

    Essentially, the electrician will select a Customer, then in a Projects subform enter information concerning a Project (which will consist of a description of the work to be done, the desired completion date etc.), then in a Materials subform will enter the materials required (based on Category, Item and Item Type, quantity and cost per unit ... and I should add that there are 20 categories, over 200 Items, and over 900 Item types, all of which show in mutually dependent combo boxes), and will also in a Labour subform enter the projected time it will take to complete the Project. From this a Quote form will be produced.

    So far so good. But the glitch is how to get that stuff from a Quote into the final Invoice. I need to preserve the integrity of the original Quote, in case of a dispute later on. However, when it comes time to produce a final Invoice the Materials list may well have to be added to, quantities adjusted plus or minus, and also price adjusted as the price of some items varies regularly depending on what the item is made of (e.g. copper).

    What I would appreciate is suggestions as to a method to move from the Quote to Invoice. And the Quote is essentially as "best guess estimate" as opposed to a "firm quotation."

    An option...
    Make another complete set of tables - for Project, Materials, Labour - and using Append queries roll the data from tblProject, tblMaterials and tblLabour, into the new tables which will be used as the basis for the Invoice which will be produced after the list of Materials is suitably adjusted and the Labour adjusted as necessary.

    I also need to allow for a quick job, which will be time and materials only, and for which the Invoice will be prepared on the spot.

    Any suggestions would be welcome.

    Tom
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ideas for Quote to Invoice process (2000 all updat

    You definitely don't want to overwrite the quote data. I don't think I would create a second set of tables. I'd probably add an identifier field to the materials and labour tables to distinguish between records belonging to a quote and records belonging to an invoice. When a quote is accepted, you could duplicate the materials and labour records for that quote and set the identifier of the duplicated records to invoice.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    I would suggest that in tblProjects you add a status field, which could be either Invoice or Quote (or perhaps a third one ?)
    When you convert a Quote to an Invoice you would need to generate a new project of status Invoice, then duplicate all the items from the quote and attach them to the new project.

    For the quick jobs you could generate the project as an Invoice initially.

    I am basing this on the way the accounting software I use does this (MYOB). It provides a third option for Status (called an Order) which is really an Invoice that is not finalised yet.
    Regards
    John



  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    Hans
    I perhaps should have indicated that the field Invoice in tblMaterials is a Yes/No field, with the default being Yes. (but I also need to add that to tblLabour)

    But, the fly in the ointment is that there could be as many as 40 Materials items selected for a Project. Any of those might need to be adjusted - quantity up or down, price change etc. - from the Quote to the Invoice.

    You suggest that when a Quote is accepted the records could be duplicated, but you also suggest that you wouldn't create second table to do that. So 2 questions...
    1. If the records have to be duplicated where do they go...into a related form?
    2. If the records have to be duplicated what's the problem with rolling them by VBA code (append query SQL) into, say, a tblInvoiceMaterials table?

    Sorry for the additional questions. I'm just trying to wrap my head around this.

    Tom

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    John
    As I indicated in my reply to Hans, the Invoice field in tblMaterials is a Yes/No field, with the default set to Yes. I am wondering if that's the same as the Status field you are suggesting.

    Tom

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    I would add that extra field Invoice (yes/No) to the Labour table.
    Hans suggested to copy the quote records from the Materials table to the Materials tale but with the Invoice field set to Yes indicating an invoice line. The same logic would apply to the Labour table.
    I would have a option box on the form that specified Quote and Invoice that you could choose after selecting the projects record.. If you set it to Quote the records in the Materials table with Invoice field set to No would appear in the Materials SF, same with the Labour SF.
    If you selected Invoice in the option box it would show the Invoice lines in the Materials and Labour subforms.

    You could have separate subforms for Materials (for invoice) and Labour (for invoice) and you could display these based upon the option box, and make the quote SF's invisible.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    As patt indicated, you can use the same form or subform to display materials for a quote or for an invoice or both - it's simply a matter of setting a filter on the Invoice field.

    There wouldn't be a technical problem with using a second materials table (and a second labour table), but having two tables with exactly the same design doesn't appeal to me. Each time you'd modify the design of the materials table (and I assure you that you'll have to at some point), you'd have to apply exactly the same changes to the invoice materials table, and the same goes for the forms and reports based on the two tables, and similarly for the labour and invoice labour tables...

  8. #8
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    Yep, I now get the reasoning behind this approach. Sorry I was having trouble wrapping my head around it before. I had never worked on a process which required adjusting quotes and moving to an invoice; I had just worked on stuff which made up an invoice from orders, and in this case the quote stuff is not the final order.

    Rather than a "highlight, copy and paste" of rows to duplicate them, I am going to try it this way...
    1. The default for the Invoice field in both tblMaterials and tblLabour would be No.
    2. Put a command button on the form. This command button would append the existing rows - for that Project only - from tblMaterials (as showing in subform fsubMaterials) and tblLabour (as showing in fsubLabour) to their respective tables.
    3. At the same time as the rows were appended, the Invoice field, in both tblMaterials and tblLabour, would be set to Yes for those appended rows. I haven't tried it yet, but I assume this can be done in one operation.

    The reason I want to set those new rows to Yes, in the Invoice Yes/No field, is there could be a lot of rows in Materials and it would avoid the user having to go through every row and clicking the Invoice box.

    I really appreciate everyone's input. If you have further thoughts, I would welcome them. In the meantime I will push bravely on.

    Tom

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    You can use an append query that select all records for the current quote, and adds those records to the table but with Invoice set to Yes. You can execute this query using VBA.

    Note: if tblMaterials has a primary key consisting of MaterialID and ProjectID, you won't be able to duplicate the records; if this is the case, you should include the Invoice field in the primary key. This will allow a material to be listed twice for the same project - once with Invoice = No and once with Invoice = Yes.

  10. #10
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    Hans
    There's a clinker in here somewhere.

    Let's assume there are 5 rows in Materials in the Quote. I want to append those rows to tblMaterials and set the Invoice (Yes/No) field to Yes for the 5 appended rows, but leave the original 5 rows set to No.

    An Update query wants to all 10 rows to Yes. So I attempted to restrict the Update just to the records being appended, but then the Update SQL doesn't run. Here are my SQL statements behind a command button.

    [code]Dim SQL As String
    Dim SQL2 As String

    SQL = "INSERT INTO tblMaterials ( ProjectID, Category, Item, ItemType, ProductCode, Quantity, ItemCost, Invoice ) " _
    & "SELECT tblMaterials.ProjectID, tblMaterials.Category, tblMaterials.Item, tblMaterials.ItemType, tblMaterials.ProductCode, tblMaterials.Quantity, tblMaterials.ItemCost, tblMaterials.Invoice " _
    & "FROM tblMaterials " _
    & "WHERE ProjectID = Forms!frmCustomers.Form!fsubProjects!ProjectID " _

    SQL2 = "UPDATE tblMaterials SET tblMaterials.Invoice = Yes " _
    & "WHERE AND tblMaterials.MaterialID = Forms!frmCustomers.Form!fsubProjects!MaterialID;"

    DoCmd.RunSQL SQL
    DoCmd.RunSQL SQL2{/code]

    Any suggestions?

    Tom

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    You can insert records and set Invoice to Yes in one go, so that you don't need an update query:

    SQL = "INSERT INTO tblMaterials ( ProjectID, Category, Item, ItemType, ProductCode, Quantity, ItemCost, Invoice ) " _
    & "SELECT tblMaterials.ProjectID, tblMaterials.Category, tblMaterials.Item, tblMaterials.ItemType, tblMaterials.ProductCode, tblMaterials.Quantity, tblMaterials.ItemCost, True " _
    & "FROM tblMaterials " _
    & "WHERE ProjectID = Forms!frmCustomers.Form!fsubProjects!ProjectID"
    DoCmd.RunSQL SQL

  12. #12
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    Hans
    Thanks very much. That does the trick.

    Doing that, however, makes me see one more little wrinkle to iron out.

    The way I have it set up now is this...
    1. Once the Quote is accepted, a command button is pressed. It has the code you provided to duplicate the rows from the quote, append them to the table tblMaterials, so there are now twice as many rows in the table for the particular Project in question - the original rows with the Invoice (Yes/No) field unchecked, and the newly appended rows with the Invoice field checked for Yes.
    2. On the form, there is an Option Group which allows the user to change the Record Source for fsubMaterials - showing either those for the Quote or those that can be applied to the Invoice.
    3. So the user clicks the appropriate option button and shows those newly appended rows which will form the basis for the Invoice. He can edit those rows, change anything in them he wishes, and also add new rows providing that he makes sure to check the Invoice box.

    So far so good.

    The possibility exists, however, that a row in the Materials that was in the Quote is no longer needed for the Invoice. He can't simply uncheck the Invoice box because that violates the integrity of the original Quote. He could delete the row and that would be fine. Seems to me a better way is for me to find some way to lock those original Quote rows so that they can always be identified as belonging to the original Quote.

    So I am wondering about maybe adding another Yes/No field, called perhaps "Locked." At the time when the rows are appended, additional VBA code could mark the original Quote rows as Locked. It might even be possible to hide that field and have it happen behind the scenes.

    Do you have any thoughts or guidance on this?

    Thanks.

  13. #13
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    Hans
    It also occurs to me that I need to guard against him clicking that command button more than once and appending records more than once. So that "Locked" field might just do it.

    Tom

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    I would not have the Invoice field at all in the SF, no need as you have changed the record source of the SF dependingon the Option box. Use the value of the Option box to set/clear the Invoice field in the After Update event of the SF. If you show the Invoice field for say the Invoice SF then the user may uncheck it and make it a quote item, you dont want to allow this.

    If you dont need an invoice row, just delete it. There is no need to lock the Quote rows, they are already identified by the Invoice field and wont be visible when the SF is set to Invoice..

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ideas for Quote to Invoice process (2000 all u

    Like patt, I don't think you need to use a field Locked in the Materials table.

    Do you set a field in the Projects table when the quote is accepted? For example Accepted? If not, you should add a Yes/No field and set it to True when the quote is accepted.
    You could check this field when the Accept Quote command button is clicked, and exit if the field is True. You could even disable the command button if the field is True.

    I would either hide the Invoice field in the subform, or set the (built-in) Locked property of the control bound to this field to Yes, so that the user cannot change it manually.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •