Custom Fields in QuickBooks
Every business has some sort of unique information that is important to its operation. While QuickBooks provides the places to store the basic information that every business needs, you will usually find that you need to store additional data such as a customer’s vehicle registration number, or the weight of an inventory item. QuickBooks provides us with custom fields, a way that you can define your own places to store information. Today we’ll talk a bit about custom fields and how to use them in estimates, sales orders and invoices.
Note: After reading this see the article on QuickBooks Enterprise 10 Custom Fields for an update
While watching postings in the QuickBooksGroup user forums I see that many people don’t understand how custom fields work in QuickBooks. Part of the problem is that QuickBooks handles them in a quirky way that isn’t always obvious. I’ve worked extensively with custom fields while developing my CCRQInvoice invoice utility for QuickBooks, and I’ll run through a simple example here with an invoice. Note that these steps are essentially the same for invoices, sales orders, estimates and sales receipts (and very similar to what you can do with purchase orders).
What we want to accomplish
For our simple example we’ll create an invoice form for a computer repair shop. On each invoice I want to have a field in the invoice header that identifies the particular computer system that was worked on, and for each detail line in the invoice I want to specify (if appropriate) the serial number of the component that was repaired. The following is a screen shot of what I want my invoice to look like.
How can I add this field and column? If we go to the layout designer we see that we can add a text box or a data field:
Those won’t help, though. A text box is merely a box of text that I enter when designing the form – it doesn’t let me change it when I am entering an invoice. A data field comes closer, but I can only add a limited number of fields that QuickBooks provides to me. I still can’t change the values of these fields when I am entering an invoice. So what can we do? This is where custom fields come into play.
Creating Fields for the Header
Let’s start with adding a field for the computer system that the invoice applies to. I’m making one invoice per computer system, and I want the ID for that system to show in the top (header) of the invoice. Some customers have only one computer system, others may have several.
To start, we have to add a custom field to the customer list. Edit any customer in the customer list and select the additional info tab. Click the define fields button.
This opens a window that lets you define custom fields in the customer list (as well as vendor and employee lists). Enter a name for the field you want to define (I’ve used “Computer System” here) and check the box.
This screen can be a bit misleading. It looks like you can add 15 custom fields here. However, QuickBooks will limit you to seven custom fields for any of the record types shown here.
Click on OK and you will see that the custom field is added to the customer record.
If you have information that you want to always show up in an invoice you create for this customer, enter that value in the field here. You can, however, leave it blank.
Now let’s add this to the invoice template. Edit the template and click on the additional selections (not the layout designer) and scroll to the bottom of the list of fields in the header tab. You will see that your custom fields have been added to the bottom of the list. Place a check mark in the screen and print columns to add the field in both places. You may need to use the layout designer to place the field in the desired location.
If we save this and look at an invoice that uses this template, you see that there is a field for “computer system”.
If you create a new invoice using this template you can enter the value of the “computer system” in this field, and it will print on the invoice. If the customer has one computer system we can put the ID in that field in the customer list, and when we select that customer for the invoice the value will show up in this field by default.
Please note the following:
- You can only enter 30 characters of information in a custom field.
- You cannot change the size of the field on the screen, but you can control the size of the field on the printed form in the layout designer.
- It is not possible to create a drop-down list in a custom field, or to have it formatted so that it only accepts dates or numbers.
Creating Columns
Fields created in the customer list can only be used in the header or footer of an invoice. If you want to create a new column in the invoice you must add a custom field to the item list.
Edit any item in the item list and click on the custom fields button.
In the custom fields screen click the define fields button.
This is similar to what you’ve seen before. You can create a label for the custom field, and place a check mark in the box. Note that you can only create five custom fields in the item list.
As with the customer record you can enter a value in the custom field for any item on the item list, and it will show as the default value in the invoice. You can also leave it blank. Note that you only have to define this field in one item, it will then be available in all items.
Edit the invoice template and select the additional selections button. You will see that the custom field added to the item list shows on the columns tab. Check both the screen and print boxes.
When we look at our invoice on the screen, you see that the serial column has been added.
You can enter the values for the serial numbers in this column. Please note the following:
- You can only enter 30 characters of information in a custom field.
- You can change the width the field on the screen.
- It is not possible to create a drop-down list in a custom field, or to have it formatted so that it only accepts dates or numbers.
- It is not possible to create a column that is used in a calculation, such as the difference of two values, or that has an effect on the quantity, rate or amount columns (this is a feature you can get in my CCRQInvoice product).
- Select the item first, before entering a value in a custom field. When you select an item it will replace the custom fields with the values from that item record (which could be blank).
Looking at the finished invoice
Here is our invoice, with the custom fields:
A few last points to make:
- Edit your template before you create the invoice. If you create an invoice, then edit the template to add the fields, your existing invoices might not contain the information from the lists. These fields are “populated” when the invoice is created.
- Values from custom fields show on some reports, but not always where you expect or with the values you want. Some reports will show the values from the list records (the item and customer list). Some reports will show the values from the transaction records (the invoice). Sometimes you will see the fields listed in a report and they never show any value. It is hard to predict.
- You can erase or remove custom fields from the lists, but only if you first remove them from any form template that uses the field.
This has been a quick review of custom fields – let me know if this helps you, or if there are any points that you would like to see clarified.
Note: See the article on QuickBooks Enterprise 10 Custom Fields for an update to how custom fields are managed in that release – the feature has been expanded significantly
Category: Featured, General Tips, Invoicing
About the Author (Author Profile)
Charlie Russell is the founder of CCRSoftware. He’s been involved with the small business software industry since the mid 70′s, focusing on inventory and accounting software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor. Look for Charlie’s articles in the QuickBooks and Beyond blog, as well as his California Wildflower Hikes blog.
Connect with Charlie at Google
Subscribe
If you enjoyed this article, subscribe to receive more just like it.
Thank you for the information I got of this article.
and I have a concern about if I can automatically create a unique account number for my customers.
Any ideas will be highly appreciated.
Doctor: If you are asking that QuickBooks automatically create the customer ID for your customer records – it doesn’t do that.
They are fields in the customer list on the “additional info” tab. I’m not adding the data on the order itself, rather I have a template set up to merge the customer list info with the template. Thanks again.
Lisa, hard to pin things down without seeing the company file. Make sure that the record in the customer list has the data, make sure that you have added the field to the template (put it on the screen version at the very least), make sure that you are creating a new transaction with that template.
Thanks. New question: on my letter template, i have inserted a field called “estimate detail”. It takes the information in the table from my estimate and inserts it in my letter. The problem is that I want different columns to appear. It seems there are pre-set columns for the estimate detail field and i want to change these. Is this possible? Thanks again for your help.
Lisa: Are you talking about the Microsoft Word export? I’ll be very honest here, I don’t usually work with that. I’m not sure if you can change that.
Is it possible to resize the receipt so that I can get 3 receipts per 8.5×11 sheet and populate each receipt with different data sets. i.e. 6 receipts for 6 customers would print on 2 sheets of paper with 3 receipts on each ??
Since we ONLY have one line item receipts it would save us on paper costs !!
Thanks
Gary, that is a bit off topic for this discussion. It is difficult to do that in QB – the best I’ve seen is to change your printer settings (in the printer options itself, in the driver) to print multiple copies per page. But that may only work well if you are printing multiple receipts at a time. You have to play with things on the printer driver side of the issue, I believe.
QB 2009 Premier –
We are in the process of creating a new invoice format. Two questions – We are using a column for “Backorders” but will enter to it manually. We will not be using sales orders. These numerical entries are left oriented and we would like them right oriented to look like real numbers. Second question – I am using data boxes in my header. I can’t seem to enter a label in these boxes that has two separate lines, one above the other. Is this possible?
Thank you
Jay: You can right justify in the print form, not the screen form. Use the “layout designer”, right click on the column, select “properties”, and one of the properties is justification.
For data boxes – I don’t think you can put a line break in the value – why not two boxes, one for each line?
QB Premiere – Manufacturing -
Sales Order and Invoice templates
Change column size with layout designer – looks ok in print preview – but when actually printed changes back. Have created new templates and always the same thing happens. How to keep the columns where I want them? Thanks
On our single entry time sheet, there is a box called “billable” which is defaulted with a check mark. I want it defaulted as unchecked. Where do i go to make this change?
Lisa – that is off the topic, but for a quick answer I don’t think that you can change that behavior – at least not that I’m aware of.
We are using QB PRO 2008. In the Item List, we are trying to display the “Location” field. We added the column in the Item List. Also, we went into specific items and added the contents to the field (“Location”). However, when we go back to the Item List, the “Location” field column does not display any data. Are there any other steps required to display this custom field on the Item List?
Thanks in advance.
Juan: Is this a “custom field” that you added yourself in item list, in the “custom fields” section? Or, is this the “location” field that you can see in the item list itself, if you add columns to the item list display? If it is the field that you can see in the item list, not a “custom field” you added yourself – you can’t use that here. That is the “location” field that only applies to “fixed asset” items. It shouldn’t be allowed to be added to the item list display, because you can’t use it in regular items. It’s a bug in QB that lets you see that field in that place…
Hi,
I am using quickbooks pro 2008. I am faing a problem with the currency symbol being shown before the total amount. Earlier whenever i would prepare a quotation/invoice in the total just the total figure wiuld appear, but lately a dollar symbol occurs before the total figure. How do i remove the dollar symbol?
Hi Charlie,
I have solved the problem. Got it done after going through your articles. Now i have another problem that is preparing a delivery note and an partial invoice. Quickbooks pro does not have a feature of preparing a delivery note. so i have to do it manually. if we invoice for partial delivery the price gets divided and not the quantity. so how do i solve this issue?
Samantha: I was going to refer you to the “currency” article when I read your first comment – glad you found it.
Your latest question is off topic, having nothing to do with custom fields. I’m not sure what you mean by a “delivery note” – something like a packing slip? I generally don’t work with Pro, I highly recommend moving up to Premier if that is possible. Then you have more of the tools you need to run a business.
I read the article “Custom Fields in Quickbooks” and attempted to add two custom fields to the Item List. I click “Custom Fields,” get the message that “Currently there are no custom fields defined for items. To define them, click on ‘Define Fields.’” I click OK and get a window where click the Define Fields button, and I add the two fields: “Domain Name” and “Website Expires Date.” I check the “use” box for both and click “OK” but when I look at the item again, the two fields I just added aren’t there. What am I doing wrong?
Steve: After setting up the custom fields, did you edit an item, and click the “custom fields” button again? You won’t see the fields in the item window itself, they always are found in the “custom fields” button…
Im using Quickbooks Premier 2009/Manuf edition. I am trying to edit a bill of materials/inventory assembly form. I can’t quite figure out how to edit this form. I added my custom field for Vendor Part #, but do not know how to add this custom field to print on a Bill of Materials?
Adriane: The reason you can’t figure out how to do that is because you CAN’T edit the BOM printout in QuickBooks. They don’t provide a way to change that listing. You have to do one of the following:
Export the data to an outside file and try to create your own BOM listing. Please note that this would be very complicated to do.
Purchase the ODBC driver (www.qodbc.com) and use an ODBC compliant reporting tool to create your report. Simpler than the first option, but STILL very complicated because of the way that Intuit organizes the database.
Go to a third party product that works with the BOM.
My company, CCRSoftware, produces a third party product that lets you modify the BOM layout. See information on this at CCRQBOM. However, please note that if you download a trial version, the publicly available release (at this time) won’t let you add the custom field to the report. That is a feature that will be available in the next release, due out in April. If you want to see more on this release you can go to my CCRSoftware product blog – or you can contact me directly and I can get you a preview version.
Charlie,
Delivery Note as in when we first prepare a quotation and once we deliver the goods we have to prepare a delivery note and than invoice the client based on delivery note as at times we deliver partially.
Hi every one,can some one please help me customizing the BIlls and WRITE CHEQUES in quick books pro,i have added custom field but the report is not able to display the information entered.
Shailen, I’m assuming you have a Canadian version? There are differences between the non-US versions and the US versions, and the differences are greater the older the year of QB you have. However, regardless of what you have, custom fields are often very limited outside of the invoice, estimate, sales receipt and sales order screens in QuickBooks.
Hello Charlie.
I tought will be easy to modify the invoice template…. but had been very difficult for what I need. Basically is a Subtotal, after that 10% Discount we call Retainage and then the total. I saw a couple Templates with Discounts but are based on previous information, not on something I can get at that moment. And this three numbers should appear in the invoice like the total does. Could you please guide me where to find this kind of format? Or how make it by myself? Thanks!
Diana, if you mean that you want the discount to show in the report footer, you can’t do that with QuickBooks. You have to put it in the details section. In summary, you would add a subtotal “item” after all the detail lines, then a discount “item” after that to apply to the prior subtotal. But you have to add these two items to the bottom of every invoice when you create it.
I charge my customers the shipping costs associated with shipping the ordered products to them. Is there a way to add a “shipping costs” line in the footer of an invoice? Thank you
Keith: Sorry, there isn’t a way of doing that (and having it show up automatically) in QuickBooks. You can add a custom field and manually enter the cost in the field, and have it show there, but if you want it in the total you would have to add the charge as a detail line, so it would show twice. I’m going to add the ability to handle this in an upcoming release of my CCRQInvoice add-on product, but that feature won’t be out until later this year, probably end of summer.
Hi Charlie,
Well we normally quote in euro but being based in UAE we would like to quote in Dirhams. Is there a possibility that we can save prices in euro in quickbooks and than convert it into dirhams as per the exhange rate available??????
Samantha, both of these questions are off topic. I also don’t know what version/year/nationality of QuickBooks that you have. The US 2009 edition supports multiple currencies, but there may be problems getting it to work properly in your system in UAE (I’m not sure, as I’ve not tried the US version on a non-US computer). Some people in the Intuit Community forum are pointing out problems if your Windows system (assuming you have Windows and not a Mac) has it’s currency settings to something other than US settings. For invoicing, I would normally create an estimate for the full amount and invoice for the partial amount.
Hi Charlie one more thing. When you invoice a client, i face difficulty in invoicing for partial delivery. Say Overall amount is 100000. you want to put payment terms as 25% advance payment, 25% on confirmation and 50 % on delivery. And now i want to invoice for only 25% that is 25000. how do i do it?
Hi Charlie,
I am using Quickbooks Pro 2008.
Enjoying reading your blog. Hard work always pays off.
I have to create an invoice in Quickbooks Pro 2009 where we are paid a commission on products we source for our clients. I have to show the amount upon which the commission is based, the commission % and the amount of commission. I have been able to do all this however the problem is that the total of the invoice can only be the sum of the calculated commissions. If I place the amount upon which the commision is based into the AMOUNT column that amount is included in the total with the calculated commission but the percentage calculation in Quickbooks Pro only calculates from an amount the the AMOUNT column.
Is there a way to create an invoice that will calculate the commission but only total the commisions and not include the amount upon which the commission was based?
Michael, this probably isn’t an issue relating to custom fields, so it is a bit off topic. The total amount of the invoice has to be calculated in the “amount” column. You can address this in several ways – I’m not sure I understand exactly how you want things so this might not work for you.
Just a few thoughts, there may be other ways. If you want something more sophisticated you may have to go to a third party product. QB only calculates the amount in a line by rateXquantity, and the total at the bottom is the sum of the line amounts.
Hello,
You seem very knowlegeable on custom fields. The problem I am having is that I created a “Cost” custom field for all items in quickbooks, then I went to the report “Sales detail by item” and nothing shows in the field. Will creating a column on the invoice as shown in your demonstation show the default value I entered in the quickbooks items and then show up on this report? I hope you can help me and save manally entereing cost on 1000 plus records each month. Thanks Much Tim
The Sales by Item Detail report is based on the transactions – invoices – so the information must be included in the transaction. When you see your custom fields listed there, the values are going to be taken from the invoices, not from the item list. So you need to add the custom field as a column in your invoice template.
When you create a new invoice, the value that is in the field in the item record will automatically populate into the column. And when you save that invoice, the value will show in the report.
The problem is, if you create a template, the values will only show in NEW invoices you create, it won’t go back and show in all of the existing invoices already in the company file.
Thank you Charlie, I realize it will not work on past, but at least is will save me many hours in the future. Thank you again for your time.
One more quick question if the cost is say 10 for an item and a one line quanity say 5 of said item will it show up as $50 in the sales by item detail or will it just say 10. Either way it is a work around which I can do a simple calculation once exported to exel. Thanks again, Tim
Tim, if you are entering a “cost” in a custom field, it isn’t going to be used in any financial calculations. When you sell the item, the “rate” or “price” column value will be used to calculate the amount that is posted to sales (quantity X rate = amount). The “average cost” value of the item, if it is an inventory item or assembly, will be used to post to COGS. Your custom field is ignored.
I added a custom field called agent in customer additional info. Each time I set up a customer I add the agent name. What I wanted to do was have an AR report by customer but sub totalled by each agent. I cannot seem to find a way to run the aged receivables by this field (or at least subtotal it)
Colin, you won’t be able to modify a report in QB that way. You may have to send the report to Excel to modify, or use the ODBC driver to extract your data and make your own report.