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.
Comments (369)
Trackback URL | Comments RSS Feed
Sites That Link to this Post
- How to import custom fields on transactions and items in QuickBooks | October 31, 2011
Can you extend the number or characters in a custom field? I need to be able to enter driving directions in a cusotm field?
Sorry, Brenda, as I said in the article, you can only enter 30 characters of information at the most. That cannot be expanded.
Is this information that you want to change with each invoice, or is it something that is constant for a given customer? If it is directions for this customer, the best I can suggest is to find one of the larger fields in the customer record, such as the email or alt email. Enter the info there, then add the field in the layout designer. You can’t change it in the invoice, just in the customer record.
Other than that, if it is something that changes with each invoice, the best I can come up with is to suggest that you enter the information in a blank detail line of the invoice. You can enter up to 4096 characters in a description line (in the Windows versions of QuickBooks, not the Mac version).
We renamed the “other” field in the header to “patient name” and would now like to do a report which includes “patient name”, but we can’t figure out how to add that field to any reports – Can it be added? All the info is in QB’s but we can’t seem to get at it.
Hi, Ann. Unfortunately, the “other” fields aren’t well supported. I generally recommend that people stay away from them when possible, and use “custom fields” instead. The “other” fields can’t be used in many (if any) reports, and often aren’t supported by integrated reporting tools.
I’ve never looked at Quickbooks before today, but the company I’m briefly temping for has been trying to get some item-related custom fields to show up on their packing slips. They have managed to get the columns added, but no values show up. From your tutorial, it appears that these values have to be manually added somewhere? (I may not be understanding correctly.) And if so, what’s the purpose of defining the custom fields in the first place?
To say that I’m confused is understating things.
Leanne: If you have created the custom fields in the item list, and added them to the template as columns, there are two places the information can come from. First – if you enter a value in the custom field IN THE ITEM RECORD, then when you ADD the item to the order the value will show up. Note that you have to ADD the item to the order AFTER you have set up the field, entered the value in the record, and created the template. Second – if you have checked the box to show the column on the “screen” then when you add the item to the order you can type in a value directly to that column.
The template was altered to include columns for the custom fields some time ago. The values are in the custom fields on the item record, but when we print the packing list, there is nothing in those columns. I’ve verified that there is indeed data in those fields on the item record, so I’m stumped as to why they are not getting passed to the template.
The fields are populated, usually, when you create the order with a template that includes the columns. This is a packing list, so odds are that when you first created the order you used a different template? If this is a sales order, for example, add the custom columns to the regular sales order template that you use to CREATE the order. You only have to include them on the screen, not the printed version. When you create the order, you should see the values on the screen. Once they are created there, you should see them when you switch to the packing list template.
If not, then there isn’t much more that I can say here in these notes, I’d have to look at your company file directly.
We are instituting QBE 8.0 at our work and have customized an invoice. We added the customer’s telephone and I want to know if there is a way to format the field to read 000-000-0000. Right now all of the numbers are run together instead of being separated by area code, prefix, and number.
Thank you for your reply.
Bob
Bob, the formatting capabilities are very limited. You can’t transform data. You would have to go to a more sophisticated method of printing your invoices with a third party add-on to change this.
However, if you are using either a custom field, or a “data field” for the customer phone, it should just display the information the way you have it stored. So if it is entered in the customer record as 000-000-0000, it will print that way. If it is entered as 0000000000 then it will print that way.
Does the number of available custom fields for a product change depending on which version of Quickbooks you use?
Not really, Ed. In the US editions you have the same number of fields in Pro, Premier and Enterprise. Same in the 2008 Canadian (single currency) and UK versions.
If you have an older Canadian version, or possibly the 2008 multi currency Canadian version (although I haven’t checked that) you may find that you have some more.
I don’t know about the Australian or Asian versions, which are based on older code.
I noticed your article mentioned that it is possible to change the on-screen width of custom columns. For years we’ve been frustrated by the column widths that QuickBooks (currently using 2005 Premier Mfg & Whsle) assigns to our on-screen forms. It seems to prefer to give us very wide columns (e.g. 30%) for columns that need only contain a few characters (e.g. Qty or UOM), while limiting other columns that normally contain a lot of characters (e.g. Description) to a narrow rendition (e.g. 8%).
If you can provide us with a method that would allow us to view our forms on-screen with appropriately-sized columns, we would be tremendously grateful!
Thanks.
Brian, the simple answer to that is to upgrade to the current version of QuickBooks. This allows you to resized the width of the columns in the form that shows on the screen, where I believe that the 2005 version did not.
i have created a custom invoice following your instructions the best i could. the same address that i type into the Bill To field, though, is duplicating to each and every invoice. i cant type in unique addresses on each invoice.
on the last invoice, if i type in a different address then that same address copies over to all the previous invoices.
also, i really appreciate your detail explanation for the custom invoices.
Jesse, usually the bill to field comes from the customer record, so it would be filled in by whatever you have in the customer record itself (without a custom field being needed). How did you create this field, and how do you enter your data into it?
oh i see, well that makes sense that it would pull from the customer record. i dont need a bill to or ship to field really, but i just need a field where i can enter the property address that the specific invoice is pertaining to. i would rather it not be a column field but maybe i have to do it that way.
the point of this custom invoice is to enter our upcoming real estate commissions if the property closes. it seems that this will allow me pull a forecasted cash flow report. then once it clsoes we will go through the rcvd payment process.
so this invoice isnt being sent to anyone. its just for our own forecasting.
I want to include the “account number” on my Statements. But i cannot find where to enter the info into the field “account number” Where is this field located in the customer info.
Roy, that field is in the “payment info” tab when you edit the customer.
This clears up a lot for me. One more question, now that I have added an additional column to our template invoice, how do I get that column to show up on the statements?
thanks so much for your help.
Kirk
Thank you for your comment, Kirk. The short answer is “you can’t”. Statement templates are not very customizable.
The best I can offer is to lok at the “customer balance detail” report. You can add custom fields from the customer list there. You can export that to Excel to reformat.
Or you can write your own statements using the ODBC driver, or get someone to write a custom program for you.
Where do I get the ODBC driver?
I need to generate a statement that has only the invoice details on i.e no invoice total
Thanks
Wendy: If you are using QuickBooks Enterprise then you already have the ODBC driver, you just have to install/implement it. If you are using Pro or Premier you can purchase it at http://www.qodbc.com.
You can also request a quote to have our custom developers create a report for you – email your specifications to custom@ccrsoftware.com
We have a custom invoice template created in QB Accountant’s Edition 2007, with a custom field for an Advertising Code, labeled Adv. Code. Somehow, in the block beneath the field, the word “Orlando” (our city) was put in the block. We need to change that word, but I have been unable to find a way to get into that field. I can get into all the headers–no problems, but can’t get to that single word which appears on every invoice. The word we want to put there will be consistant on most invoices, but we also need to be able to change it when it is not applicable. Right now, we change the word “Orlando” alot. In all the fields that I can get into, I cannot find the word “Orlando”. Any suggestions?
Mary Ann, it’s hard to give you a direct answer without seeing the template. There are several things that can be going on. It’s not clear if this is the custom field itself, or something else. You can go to the Layout Designer and see if that is a separate data block of some sort, and delete it.
Or if this is the actual data field itself, and this is a custom field from the customer master list, then it could just be that the custom field in the order is picking up a default value from the customer record. Find an invoice that has this value showing up. Go to the customer record that this invoice is created for, look at the custom field in the customer record and see if there is a value there. If one is there, it will show as the default in that field when you create an invoice for that customer.