Custom Fields in QuickBooks

Doug Sleeter's Profile
 

Every business has some sort of unique information that is important to its operation. While QuickBooks Desktop provides the places to store the basic information that every business needs, you will often find that you need to store additional data such as a customer’s vehicle registration number or the weight of an inventory item – information that QuickBooks doesn’t already accommodate. To address this QuickBooks provides us with custom fields, a way that you can define your own places to store information in the QuickBooks database. Today we’ll talk a bit about custom fields in QuickBooks and how to use them.

Custom Field Basics

Let’s take a look at some basic concepts in “custom fields” in the Windows desktop version of QuickBooks. This information applies to Pro, Premier and Enterprise.

A custom field is a user-definable field that you can create in several different lists in QuickBooks. These can be used to store information, up to 30 characters per field.  There is a limited number of custom fields available:

Once you define the custom fields in a list you can then add them to certain transactions in QuickBooks, and include them in various reports. This can be confusing at first – you can’t have a custom field in a transaction (like an invoice) without FIRST defining them in a list (like the customer list). Once you add the custom field to a transaction the transaction has its own copy of the field. If you change a value that field in the list, it doesn’t change the value you see in the transaction.

You can create custom fields in several lists:

  • Item List
  • Customer List
  • Vendor List
  • Employee List

The item list has a limit of 5 custom fields in Pro and Premier. In Enterprise (starting with V10) you can add up to 15 custom fields in the item list.

For the customer, vendor and employee lists you have a maximum of 7 custom fields for each of these lists in Pro and Premier – and an aggregate limit of 15 for all three lists together. In Enterprise (starting with V10) you can have a maximum of 12 custom fields for each of these lists, with an aggregate limit of 30 custom fields for all three lists together.

Once you create the custom fields in the lists, you can add them to:

  • Invoices
  • Sales orders
  • Estimates
  • Sales receipts
  • Purchase orders
  • Credit memos
  • Build Assembly (only in Enterprise)
  • Various reports (more on that later)

We often see people asking for the ability to add custom fields to other transaction forms, such as Statements. Unfortunately this is not possible.

In QuickBooks 2014 you can also add custom fields to the detail section of Checks, Bills and Credit Card Charges, although with some restrictions when compared to the list above (see this article for details).

In addition, custom fields can be used in several functions, such as Add/Edit Multiple List Entries, the Advanced Excel imports, and a few others.

It is interesting to note that you can change the name of a custom field without any problems. This isn’t surprising, you can do the same with the names of items, customers and so forth. If you change the name of an existing custom field all of the data in that field remains the same, just using the new field name.

An Example to Illustrate

Let’s look at an example of some typical uses of custom fields to illustrate what you can accomplish. I’ll use an invoice transaction in QuickBooks Premier 2013. I am going to assume that you understand how to edit an invoice template.

Here’s what I want my invoice to look like. I have two fields in the invoice header, and two columns in the invoice detail. These are fields that do not normally exist in QuickBooks.

  • Pref Day is the preferred day of the week that this customer wants deliveries. This is information that should be the same for each invoice for this customer.
  • Driver is the name of the driver who is making the delivery. This can change with each invoice.
  • Case Pack represents the number of bottles in a case (I’m not using the “unit of measure” feature, I’m selling by the case). This should be the same for this item each time that I use it.
  • Total Case is a number for each line that tells me how many cases there will be, a number that varies with each invoice.

Custom Fields in QuickBooks

How can I add these fields and columns? If we go to the layout designer we see that we can add a text box or a data field:

QuickBooks Layout Designer

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 the value when I am creating 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 creating 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 the fields for the header. Select any customer on the customer list and open the Edit Customer window. Select the Additional Info tab and then click the Define Fields button in the lower right. The Set up Custom Fields for Names window will open.

Adding Custom Fields to the Customer

Add the custom fields that you want and check the Cust column for each.

Note that you only have to define the fields one time in one customer record – the fields will be added to all customer records.

Click on OK and you will see that the custom field is added to the customer record. If you want there to be a default value for just this customer when you create a transaction, enter the value in this customer record. In my example I always want the same value in the “Pref Day” when I create an invoice for this customer, but the “Driver” value will be chosen when the invoice is created.

Customer record with custom fields

Now let’s add this to the invoice template. Edit the template and click on the additional selections button (not the layout designer), then 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 on the printed form.

Custom fields in invoice header

If we create an invoice using this template you will see that the custom fields show up. If you use the customer we worked with earlier, the default value for the “Pref Day” custom field will fill in. We can edit the value in that field, and we can enter a value in the blank field.

Custom fields on the invoice

Please note the following:

  • You can only enter 30 characters of information in a custom field.
  • You cannot change the size or position of the field on the screen, but you can change that for the printed form using the layout designer.
  • If there is a value in the custom field in the customer record it will fill in the field in the form (as you see with “Pref Day” in the screen shot above).
  • Changing a value in this transaction will not affect other existing transactions, these values are stored in the transaction itself.
  • If you change a value in one of these custom fields you will be asked if you want this information to update the custom field in the customer record, to make it the new default. Note that you cannot change this behavior in QuickBooks (it can be annoying).

Changed the value in the custom field

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.

Custom Fields in Item List

In the custom fields screen click the define fields button.

Define Custom Fields in Item List

This is similar to what you’ve seen above. You can create a label for the custom field, and place a check mark in the box.

Set up Custom Fields for Items

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 when you add this item to 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.

Custom fields with values in item list

Now let’s add this to the invoice template. Edit the template and click on the additional selections button (not the layout designer), then scroll to the bottom of the list of fields in the Columns 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.

Custom fields added as columns

When we look at our invoice on the screen, you see that the custom fields have been added as columns.

Invoice with custom columns

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 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 (you can use a third party add-on such as CCRQInvoice for some calculations).
  • Select the item first, before entering a value in a custom field.
  • If there is a value in the custom field in the item record it will fill in the field in the form (as you see with “Case Pack” in the screen shot above).
  • Changing a value in this transaction will not affect other existing transactions, these values are stored in the transaction itself.
  • Unlike custom fields in the header, when you save this invoice you do NOT have the option of saving the value back to the item record if you make a change.

Looking at the Finished Invoice

Here is our invoice, with the custom fields:

Finished invoice with custom fields

Keep in mind that the same principles apply to the other forms that you can work with. All of the other sales transactions (estimate, sales order, sales receipt, credit memo) work just the same way. Purchase orders are the same, other than using the custom field that you set up in the Vendor list. Those are the primary “forms” that you are going to be working with (along with, possibly, Build Assembly forms in Enterprise).

Custom Fields in Reports

So now you have custom fields in your lists, and in your transaction templates, how about using them in reports in QuickBooks? That can be a bit frustrating, sometimes, until you understand how Intuit handles custom fields in reports.

Remember that custom fields exist in two places – in the list record and then in the transaction record. When you look at a report in QuickBooks you have to understand which kind of record is being used as the source for the data – a list or a transaction.

Looking at my sample invoice above you can see that there are differences in the value of the custom field between the list and transaction records. For example, Driver is blank in the customer record, but has a value in the invoice record. Same name for the field, but a different copy of the field in each place.

Let’s look at the Sales by Customer Detail report. I can “customize” this to include the custom fields as columns, and the custom fields from both the customer and the item list are available. Here I’m just showing the fields from the customer list as an example.

Customize the Sales by Customer Detail

Here’s the report – the values that show in the custom field are the ones found in the transaction and not in the list. That is because this is a transaction detail report. That makes sense. However, if you want to show values from the customer or item list itself, you can’t add that to this report.

Sales by Customer Detail

How about a list report such as the Item Price List? As you would expect, the values here are the ones found in the list record, since no transactions are involved.

Item Price List

Some reports in QuickBooks can be frustrating. I might want to have a Sales by Customer Summary report that I can sort or filter by a custom field, but these reports don’t let you select which columns to include (I’m always frustrated by that).

In other reports it can be hard to decide which values are going to show up. How about an A/R Aging Detail report? Since it is a “detail” report, I would expect that to use the values found in the custom fields in the transaction? Well, sort of – it DOES include the values from the header custom fields (in the transaction), but even though you can add columns for the item list custom fields, they never include any values. The “detail” is from the invoice header, not the invoice details. It is odd that QuickBooks lets you pick fields that will always be empty.

A/R Aging Detail

So you have to look at which values are being accessed in each kind of report. Is the data source the list? Is the data source the transaction? Are the records shown based on the header or main transaction record, or are they based on the detail lines of the transaction? It can be confusing.

This is one of the reasons why I often rely on a third-party reporting tool like QQube, which usually provides you with more information and in a clearer format.

Don’t Use the “Other” Fields

When you are editing transaction templates you will notice some fields named the “Other” fields. You’ll find “Other” in the Header tab, with “Other 1” and “Other 2” in the Columns tab. I recommend that you don’t use these in most cases.

The "Other" fields

These are holdovers from older versions of QuickBooks, from a time before we had “custom fields”. They only exist in the transaction, not in the list. The main reason why I avoid using them is that any value stored in these fields is NOT available in QuickBooks reports. The only place you can see their value is in the transaction itself (or, possibly, in a third-party add-on).

Another reason why I don’t like these is that (starting with QuickBooks 2013) there can be some confusion. When you edit a customer you can see several fields labeled “Other” – these are not the same fields. This is an unfortunate option for naming these fields, since there is no connection.

The other "Other" fields

Special Attributes for Enterprise

Starting with Enterprise V10 there are a number of enhancements to custom fields that are ONLY found in Enterprise. Prior to V10, Enterprise was the same as Pro and Premier.

As I mentioned earlier, the limits to the number of custom fields are higher. You can have up to 15 custom fields in the item list (up from 5), and up to 12 custom fields for each of the customer, vendor and employee lists (up from 7, with a limit of 30 for all three lists together).

Another special feature that was added in Enterprise is the ability to specify “attributes” for custom fields. Normally, custom fields are just 30 characters of text. Enterprise allows you to select one of several “input masks” that can limit the type of text that you can enter in the field.

Custom Field Attributes

It is important to note that all this does is to change how you can enter the values. They are still stored as 30 characters of text.

You can change the attributes of a field, but you have to be careful if you do that with a field that already has values in it. If, for example, you have a custom field that was set to “Any text”, and you have the value “ABC”, then you change the field to a “Numbers” attribute, it can cause some issues if you try to change the value of that field.

This is a fairly simple implementation of an input mask. The program doesn’t help you, it only nags at you if you make a mistake. If you select a “date” attribute, for example, you don’t get a popup calendar to pick from, you just have to enter the date in the right format. If you pick a phone number attribute like “Phone-optional Ext (xxx-xxx-xxxx-xxxx), if you don’t enter the number exactly right you will get a warning.

Custom field phone number

It would be nice if they used a more complex method that would accept various values and fit them in correctly. For example, with the phone number, I should be able to enter the number without dashes and have it be accepted as long as I have the right number of digits.

One area that you need to be concerned with is how these “attributes” affect data entry from Excel imports, IIF imports and updates from add-on products. If your input value doesn’t match the requirements of the custom field attributes, the data could be rejected or ignored.

Another feature of the custom field attributes is the ability to mark a custom field as “required” in both the transaction and list. When you choose this for a custom field in the item list you can specify which item “types” this requirement will apply to.

Required Custom Field

Use this with caution, because you will NOT be able to save the item or the transaction until you enter a value in the required custom field.

User’s Multi-Choice List

One of the most popular options here is the “User’s multi-choice list”. This allows you to define a list of up to 100 entries (each limited to 30 characters) that you can choose from. Note that you can sort the list. You can also choose to force the answer to be from this list, or accept an entry not on the list.

User's multi-choice list

In the transaction you see the dropdown list.

Dropdown list in an invoice

Custom Fields in QuickBooks Are Very Useful

This is a GREAT feature that is very widely used. I use custom fields in almost every installation that I work with. Intuit could make this more useful, but they’ve done a reasonable job. The main issues I have are that I wish many of the features found only in Enterprise were also available in Premier. The “User’s multi-choice list” option and the ability to have a larger number of custom fields would be greatly appreciated by Premier users. Also, I think that the “input mask” feature of the custom field attributes could be done better – it isn’t that difficult to create an input mask that helps you enter the data correctly, rather than just nagging you if you make a mistake.

Let me know if any of this isn’t clear, or if I missed something!

 

Comments

Member's Profile

Thank you. This is very helpful information.

To comment, and for full access, login or register