5 Replies Latest reply on Sep 22, 2010 9:09 AM by philmodjunk

    Split down quotation/invoice to options at component level



      Split down quotation/invoice to options at component level



      We are a small installation company working in the audio/video industry and we are currently trying out Filemaker Pro (and hopefully eventually Server). I will explain a little about the (overcomplicated!) system we use at the minute so hopefully you can see what we want to do with Filemaker.

      When we carry out a quotation we produce a 'shopping list' of options for the client to pick what they want installed. We work out all the figures on a large spreadsheet which contains individual component codes for every part of an installation, the next part of the process is where we have a series of blocks (the options) about 25 rows deep into which we input component codes and quantities, which then produces subtotals for each option. There is a sumamry of all the options at the bottom (about 30 in total) and then grand total, VAT, etc. We then print out the summary and manually input all those figures into a word document which has a list of all the options, a notes section with information in, and a price for each option. This document is the 'official' bit with the customers address on which gets sent to them.

      If the quotation is accepted then we produce a more detailed version called a specification which main difference is a more in depth ‘notes’ section and slightly different wording. Again if there are any changes we go back to the spreadsheet and do all that and then come back to word and change all the text and figures. At specification stage, we have the job so stock is confirmed as allocated to that job and any serial numbered stock is put aside and we write all the numbers on our office copy of the spec.

      After installation we give it a few weeks and then invoice which is another copy of the spreadsheet (updated if necessary) and a different word document which this time contains the names of the options and figures only (so fairly simple layout). But it does contain all the serial numbers and model numbers of any big pieces of equipment.

      Any labels for envelopes are copied over to a word or photoshop template manually. We buy in massive bulk to keep prices down and although loss of stock is not a problem at all, we find it very hard to keep track of where stock is purchased, where it is at our offices (ie. in stock, allocated, on dem, returned as faulty, etc), and ultimately where it has been sold to. It becomes a huge problem when something comes back as faulty because we have to send it back to the supplier with purchase invoice and sales invoice numebrs and dates. It's all time searchign through the paper that could easily be gotten rid of.

      This is a very simplified version of what we do but I'm sure you can see just from this that there is lots of paper lying around and lots of little things that take time which could easily be forgotten about with a bit of automation.

      I've only had Filemaker on trial for a couple of days but I've managed to setup a simple contact database with contacts automatically associated by organisation. I've also created a seperate section which shows the information of the organisation and their file number (each organisation has a unique file reference). I've also adapted the preset notes sections to give individual notes but also organisation notes, so search for anyone with associated with the same organisation and they all get the same notes about that place but also their individual notes. What I would like is when you create a new contact, you type in the organisation file reference and it fills in all the other information (name, location, type, address, etc) automatically - basically like a lookup formula in a spreadsheet. That's a minor thing but some advice would be appreciated.

      I've also input some details of serial numbered stock along with relevant purchase dates and price etc. but not got much further with that yet.

      So in summary, we need some way of creating a quote with several (sometimes 25+) options, each consisting of a title, notes/text, a breakdown of individual components and time and then a subtotal, which we can then print in full for us, but then print only the titles, notes and subtotals for the customer copy. We then need this information automatically transferred to a specification if we get the job. And then on to invoicing which would only take the option title and subtotal information. Throughout this process we also need all the stock to be status ‘allocated’ at quotation level for a period of 3 months. If the quotation is not accepted within 3 months we need it to go back as status ‘stock’, if a specification is produced it needs to stay as ‘allocated’ until invoicing at which point it will be ‘sold’. All that allocated stock’s relevant serial/model numbers would need to automatically appear on the invoice in the relevant option.

      Another thing I forgot to mention earlier is our pricing setup. Just take for example a piece of stock that costs us £130. We might sell that stock out at a "false trade" of £145 which then goes through our markup and comes out at £220 say. So if our "true trade" is £130 for the 5 in stock and we suddenly run out and the price goes up to £150, we have made a certain amount of provision for the stock we are buying in at the new inflated price. It also gives us scope to lower our "false trade" down to "true trade" and still make a decent profit, should we want to lower our prices a bit in order to either get a job. It's a bit of an odd concept I think (I didn't come up with it) but that's how it has to be done so there does need to be provision in the system for the "false trade" and markup = retail on all quotes, invoicing, etc. But then on profit/loss sheets (which I hope to create through filemaker as well?) it needs to obviously use the "true trade" comapred with the retail. We also do work with varying VAT rates (although everyone has done the past couple of years!) so this needs to be easily changeable as well so we can zero rate and also go up to 20% next year.

      Like I said before, we are a small business so we do not employ a specific computer engineer or anything. I do most of the computer side of things but as we are expanded I'm needing more and more help. I am pretty good with spreadsheets and I did use Access a fair bit at one point but that was about 5 years ago so I'm very rusty and also Filemaker, althoguh similiar in concept is obviously very different to use so any help would be appreciated. There's no doubt more stuff you need to know, and stuff I've forgotten but I will do my best to add to this as and when.

      I guess the first question is: Is the above possible? and if so, can anyone help me on my way?

      Many thanks to all who can help!

      Sam K

      PS. Stupid question but how I change from dollars to pounds?! Stupid thing keeps spitting about numbers in dollars!

        • 1. Re: Split down quotation/invoice to options at component level

          First, you need to set up tables and relationships that support what you are trying to do. You'll need at least 4 tables, probably more with the correct relationships linking them:

          Contacts----<Invoices-----<LineItems>------ProductsServices     (  ---< means one to many )

          You put your customer information in contacts, quotes and invoices go in invoices and your "shopping list" of items quoted and invoiced are in LineItems. The pricing information including "false" and "true" trade costs would be stored in ProductsServices and would be copied (Looked up values) into LineItems.

          Here's a very simple demo file Created by another Forum member, Comment, that you can download and look at: 


          You can see a more elaborate example if you select Invoices from the FileMaker starter solutions. I don't recommend how it handles inventory tracking but both demos demonstrate how to set up your relationships for this, a portal to LineItems on an Invoice layout and how selecting an item in the portal looks up prices from the Products table. You want the prices to be looked up so that future price changes don't modify existing invoices and so that you can modify prices on a case by case basis if needed.

          • 2. Re: Split down quotation/invoice to options at component level

            Oh yeah, you asked about $ versus pound symbols. If you click a number field while in layout mode, you can go to the data format section of the data tab of the inspector and select currency formatting where you can specify the number of decimal places and the currency symbol to be used.

            • 3. Re: Split down quotation/invoice to options at component level


              Thanks for your advice, I've downloaded that example and I kinda see what you mean.

              So quotes/specs/invoices would all be part of one table, just with different layouts accessible? And I presume we could have one customer layout, and one office layout for each so when we print, we get a full copy but the customer doesn't?

              From what I see, the line items contains specific items from the product list that are going to be invoiced for, like this:

              Invoice 1 has two items on it from the LineItems,
              Item 1 consists of 1x A
              Item 2 consists of 5x P

              Invoice 2 has three items on it from the LineItems,
              Item 1 consists of 3x A
              Item 2 consists of 10x P
              Item 3 consists of 4x K

              The bit I don't get is we want to pick out several components from the list of 26, not just one. We want some of A, some of B, some of C, etc to all make up a subtotal for line item 1, not just one component, like this:

              Invoice 1 has two items on it from the LineItems,
              Item 1 consists of 1x A + 2x C + 5x M
              Item 2 consists of 5x A + 7x P + 3x Z

              But then on Invoice 2 it has three items on it from the LineItems,
              Item 1 consists of 1x A + 2x C + 5x M (just like 1)
              Item 2 consists of 5x A + 10x P + 7x Z
              Item 3 consists of 3x B + 4x K + 5x V

              As you can see sometimes the first option/item on an invoice contains the same components as another, but in varying quantities, so each option on each invoice is completely different. From what I see, we need to create an entry in LineItems for each option and then manually input the calculated subtotal for each option. Is there a way we can do this in Filemaker automatically?

              What I was thinking is that on each line item, we would have some form of drop-down to show about 20 rows with columns for product code, name, price, quantity, etc and this then subtotals into a line item which is all that appears on the invoice. Then repeat the process for each option/lineitem. So it would almost have to be a table within each row of the table / a mini-invoice within each item of the invoice!

              Sorry if I'm either being deluded and it's never going to happen, or where you've sent me is in fact exactly what I want! My eyes are just misted over with all the new stuff to learn on Filemaker!



              • 4. Re: Split down quotation/invoice to options at component level

                I wouldn't structure my data that way as it's complex and more difficult to modify down the road.

                Instead, I'd make each item (1 A, 2Cs, 5Ms)

                Separate line items in the portal but grouped together to show that they make up a subunit of the whole. It just makes pricing, cost accounting and reports based on your LineItems vastly easier to create and work with.

                The lineItems in your printed Invoice might look like this:

                SubUnit 1
                  1 Item A Cost: 55
                  2 Item C Cost 30
                  5 Item M Cost 25

                SubUnit 2

                and so on.

                One compromise that some developers use is to include "kit" items in their products that comprise a specified number of individual products all purchased as a group. Scripting can support such "kit" selections by enabling the user to select a "kit" and then the database fills in the item list that it represents.

                • 5. Re: Split down quotation/invoice to options at component level

                  When it comes to specs, quotes and invoices. You haved options. Some businesses prefer to keep these in separate tables, but with the same basic structure. Some start with a quote and just update it throughout the bidding process until it finalizes as an invoice. Others start a quote, then duplicate it and it's line items to generate an invoice but keep both sets of records in the same tables, but with a label field to distinguish quotes from invoices (and quote line items from Invoice line items) so that they can generate reports of either invoices or quotes or can pull up both for the same project so that they can compare the original quote to the final invoice.