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.
Subscribe
If you enjoyed this article, subscribe to receive more just like it.
I added a custom field to customer and populated it. I can get the field itself to show on the custom transaction detail report but the field is blank for each customer? Does it matter that the invoices were created before I added this custom field?
Terry, the report is taking the information from the invoice, not the customer record. So you have to add the custom field to the invoice itself. When you create the invoice, the value will auto-fill from the customer record, and then it is saved in the invoice. You don’t mention that you added the custom field to the invoice template itself?
I did add it to one invoice itself before my original post thinking that might be the problem. Even though the invoice is “old”, I can still add the field and resave the invoice and it should work?
Got it to work. Even though I had put it onto the invoice, I did not have “all my ducks in a row”. Thank you so much for the help.
After you add a custom field how do you automatically update the field with data from the database?
I want to put a timestamp on the invoice.
It seems like it should be easy enough because I know you can print it on reports. So, I know the data is there…
Thanks,
Joel
Joel: If, for example, you create a custom field in the customer record, and then you add it to the invoice header: If you put some information in the field in the customer record for a particular customer, and then create a NEW invoice for that customer using the modified template, the information from the customer record will show in the invoice as the default value for that field.
They key issue is that it works when you create a new invoice. If you have an existing invoice, and then modify the template to add the field, it will not add the info into the custom field. It happens when you first create the invoice.
Our small consulting company invoices clients by consultant and project. If we have consultants working on three projects for the same client in a particular month, we will issue three invoices to that client and I need to display details of the project as part of the header on each invoice (client task number, approver, internal codes etc.). Given the 30-char limit to custom fields, do I have any options other than to set up several custom fields to display this information, for example Detail1, Detail2, Detail3 etc.? Thanks in advance.
John, that is pretty much your only option, without going to a custom written order entry add-on.
I need guidance please. I created a custom field that does not print on an invoice which is just what I wanted. What I can’t figure out is how to rearrange the sequence of boxes in the invoice entry screen so my new field is at the end after the PO number, terms, ship, etc. entry boxes rather than at the beginning of them. Can it be done? Thanks for your help!
Cara, you can’t control the order in which the boxes appear on the screen.
Hello Charlie,
I have a question that is boggling me. We have a new website and need to bring in a custom field of data. However you cannont import custom fields through an IIF file.
So I had to go to plan B which was use the “Other1″ and “Other2″ fields – this imports perfect but guess what it does not show up on the Sales by item report or any others for that matter.
My question is do you know a way of making it show up or know of any 3rd party reporting software for quickbooks that can do this simple task that has become a nightmare?
Thanks Much, tim
Tim: The IIF format is an old one and isn’t being updated as QuickBooks advances, it doesn’t work with all the fields you find in QuickBooks, particularly newer ones. There is some support for custom fields in some portions of IIF, but I don’t work with that at all.
The “Other” fields are an old type of field that doesn’t get a lot of support – they should have gone away, replaced by custom fields, but they stuck around for compatibility. As you note, these fields aren’t always available where custom fields usually are.
If you are working with importing data, you should do one of three things:
Use the ODBC driver at http://www.qodbc.com, which will support custom and “other” fields.
Use the programming SDK from Intuit at the Intuit Developer Network (which is what I use), which supports custom and “other” fields (the ODBC driver is based on the SDK)
Use a third party web interface product, of which there are several, that you can find in the Intuit Marketplace. These will also be based on the SDK.
IIF should be avoided in any case, because it is unreliable and has the potential for crashing your database. It is an older format with very poor error checking. Intuit recommends that you don’t use it, and won’t support you if you have problems with it (or due to it).
Hello Charlie,
After reading similar responses in your site, my problem is not unique. Can’t belive quickbooks hasn’t fixed this – seems easy to do.
However I need this to happen, can you write a program or adjust your CCR programming rather than spending money on Qreports which is far too involved for the 1 report I need per month. The reason I need it is because I need the cost to come in on the IIF file so I know what one company should charge the other. One company manufactures and other is sales.
Thanks a million, Tim
I don’t see QuickBooks “fixing” anything here, they don’t consider it broken. IIF is no longer supported – it is still there, but frozen, it is only there because many people use it and would complain if it goes away. The “other” fields are similar – they want you to use custom fields in their place. And, note, that they have a tool that lets you access custom fields, the SDK, which is what they expect people to use (or the ODBC driver). Note that with the ODBC driver you can use tools like Crystal Reports, Microsoft Access, or even Microsoft Excel to access the data. However, I’ll note that their implementation of custom fields is a bit odd from a database standpoint.
If you wish to talk about a custom solution please contact me in the address in the “About” section of this blog. Provide me with the details of what you want and I can generate a quote.
Hello Charlie,
How can I see custom fields in ‘Inventory information’ window, when I want edit item? Is it possible or i will only see it if i click on ‘custom fields’ button?
Thank you.
Kasia, you can only edit the values of the custom fields in the “edit item” window by clicking on the “custom fields” button. To do it any other way you have to use a tool that is outside of QB.
Charlie,
I am doing some work for a local non-profit. When they set up their quickbooks they created a custom field for “Client ID” but then they had a volunteer enter all of their data. They put the data in the wrong field. Now it is my job to move the data into the correct field. Is there a way to do this without going into each record individually? They have over 5,000 incorrect entries and that would take forever. Could I perhaps export all of the data, move it around and then import it back?
Thanks,
Kelly
Kelly, I assume that this is in the customer list?
The Excel export/import won’t work, as QuickBooks won’t export the custom fields for the customer list, as it does for the item list. I’m not sure why they didn’t do that.
You can export the customer list to IIF (File/Utilities/Export), and that includes the custom fields. You can open the IIF file in Excel. However, to import it back in, you might have to make some changes to the IIF file. To be honest, I avoid working with IIF, so I don’t know the details. MAKE A BACKUP COPY before you tryin importing from IIF, as it can create problems.
You can read about the IIF file format in your help file, or at http://support.quickbooks.intuit.com/support/pages/knowledgebasearticle/bde7b06f
You can find a utility that helps work with IIF files so you don’t have to learn all the arcane details at http://www.bigredconsulting.com
You can also purchase the data import and export utilities from http://www.baystateconsulting.com (check the trial versions to make sure that they handle the custom fields properly), although that is more expensive.
Hi, I am interested in making a custom field that has a much larger character limit. This field would need to appear on a print out on the right side of the page, much like the customer name and address appears on the left side. (I can send you an example of an invoice i received from another company that is exactly what i want. I need to use the field to enter instructions to technicians, these instructions will be unique to each service request. Any ideas?
Greg, “custom fields” in QuickBooks are only 30 characters, and you cannot change that.
The best you can do without going to a third party program is to enter your descriptive text as “comment” lines in the body of the invoice. If you just enter the text in the description field with no item, quantity, rate or amount, you can enter up to 4096 characters per line, and as many lines as you want. But they have to be in the detail section of the invoice.
Can you email me at the address I posted with? I would like to send you an example and get your opinion on how it was created.
Hi,
Can we add custom fields in Enter Bills Window? If yes then how?
With Thanks.
Irfan, sorry, you can’t modify that window in QB
HI,
CAN I MAKE CUSTOMIZE FIELDS IN CREDIT MEMO AND ENTER BILL OPTION AND ALSO I WANT TO FILTER ANY ONE FIELDS FROM ENTER BILL , SECOND THING CAN WE REPLACE DATA FROM ONE FIELD TO ANOTHER FIELD, THIRD CAN I DISPLAY OTHER 1, AND OTHER 2 FIELD IN DISPLAY REPORTS
CAN WE REPLACE OTHER 1 AND OTHER 2 FIELD DATA IN CUSTOMIZE FIELD IF YES HOW
THANKS
Shafiq: “Replace”? I’m not sure what you are actually asking to do. You can use custom fields instead of the “other” fields, and then they are available in reports, etc. IF you mean can you MOVE the data from other fields to custom fields – it is possible but complicated. I would look at a third party tool to do it. You can use ODBC if you want to do some programming. You can get tools from Baystate Consulting. I also have done some custom programs that do that kind of thing, but they are a bit expensive.
For your other questions: Custom fields can be used in credit memos the same way as in invoices. They cannot be used in the enter bill screen, as you have no way to edit that screen. And you usually can’t get other 1 / other 2 fields to show in QB reports.
I guess there’s some important reason why Quickbooks limits you to 7 custom fields of 30 characters per field????
Perhaps they still think we’re working with 4GB hard drives on a Pentium 2 with 64mb ram?
We’re looking at alternative software as Quickbooks is severely limited in so many respects. They don’t seem to listen to their customers and refuse to add functionality.
Hi I wanted to get info on the weights and the custom fields. We have set up the feilds as directed but the info is not being processed in our QB’s sales order. Do we need to transfer the info from our QB file to the CCR invoice program to get the weights to calculate?
I generally don’t discuss the CCRSoftware products in this blog, usually you would use our product blog (http://ccrqblog.ccrsoftware.info/) for that. In any case, to use CCRQInvoice to get total weight of an order you need two custom fields in the item list and one in the customer list. You would enter the unit weight in one custom field in the item list. You would add the three custom fields to your invoice template. When you process invoices with CCRQInvoice it will read the invoice, take the quantity and multiply it by the unit weight, store that in a custom field, then total those extended weights for each line and store that in a custom field that you have in the header or footer. All of the data is held in QuickBooks, you don’t have any information stored outside. No transfering. Just tell the program to do the calculation, it reads the QB invoices, calculcates the weight, and stores it back in the QB invoice.
I’ve made a custom field in my item list called Pack from Vendor and under each item we stock I typed in the vendor the item comes from. on my template it’s auto filling the vendor under the column ‘Pack from vendor’ This is what I want now for some reason my other 2 computers which share, won’t show up when they pull up the same templet to fill out! a couple of days ago I didn’t have an issue. Would you have any ideas on what it could be? We are all in multi user mode, everthings checked on the templet correctly. We’ve never had this issue with a custom made field before.
Thank you for your help
Kelly, I can’t say much without having access to your system. It should work if you have your multi user system set up correctly. Make sure that all users are looking at the same file. If all else fails, reboot the workstations and then the system that the Intuit database server is on. But that is just a “push the reset button” kind of answer, best I can do at this time.
I need the quantity column to add the total number of items ordered on a Sales Order. Is this possible? Can someone please help me.
Melody, QuickBooks cannot do that on its own. You can look at my company’s CCRQInvoice product, which is an add-on product that will do that specific calculation for you.
We have many employees and bill by the hour for our services. We record time using QB Timer. Each employee may use any number of service items which are common accross all users. How can we show ‘Employee’ as a field on the invoice? Only ‘Item’ is available as an option.
MikeSelby: I don’t work with QB Timer, so I can’t say much about that. In your invoice itself you could add a column for “Employee” and enter the information directly (add it as a custom field). If you want the “employee” to fill in directly, I’m not sure you can do that with QB Timer (again, I don’t work with that). Perhaps you can check with the Intuit Community forums on that.
When running a purchase report it does not retrieve the value specified in a custom field. It does add the column on the Purchase report, but does not show the value in the field. Any ideas? Do you offer a solution for it?
AJ – do the PO’s have the custom fields added to the templates? And do the fields in the actual PO’s show the data? If you don’t add the fields to the PO template and have them there when you create the PO, the information won’t be stored in the PO and won’t show on the report.
We work on medical equipmwnt & use the custom fields to record serial number,model type & approval date. This allows me to generate a report each morning listing all items that are approved for work along with the details that are in the custom fields . These fields are left blank so that we can add them when we check-in the item.
This all works great except for one annoying problem, I keep getting a pop-up that always says you have changed these fields & do you want this information to appear next time. I never need to have it re-appear on a new estimate. This has caused several problems by accidently inserting a serial number from a previous estimate.
If the pop-up only had one of those “dont ask again” check boxes i would be a happy guy, Is there any way you know of to stop it from displaying the pop-up ? Thank You
KC, I don’t know how to stop that. There is a setting in the preferences that seems to imply that you can turn that feature off, but I’ve never been able to get that to work.
Charlie: Thats exactly what Ive found out,well at least I know it not just me
. Thanks