SAP Business One Training Tips #1


SAP Business One

Table of Contents

By Ryan Howe

Linking Customer/Vendor to a specific P&L account

A customer recently approached one of our Implementation Consultants and asked, “How can I track revenue/expenses to a specific P&L GL account by customer/vendor?” While the Accounting Tab of the Business Partner Master allows the user to define Payables and Receivables accounts for each customer/vendor accordingly, it is not possible to assign specific Expense and Revenue accounts at the BP level.

Business scenario: 

The customer needs to link a G/L account to his customers or vendors, so in the marketing documents, this account will be used automatically.


Using a User Defined Field, assign a G/L account to the BP master records and default the account to the Marketing Document via a formatted search. 

The solution varies for Items documents and Service documents.

  1. Create a user defined field in the BP master data as alpha numeric; for example UDF_GL and make sure that the UDF field # of characters correspond with the total characters of GL account in the COA.
  1. link the following query to the field as a formatted search:

SELECT T0.FormatCode, T0.AcctName FROM OACT T0 WHERE T0.ActType = ‘E’ Or T0.ActType = ‘I’ FOR BROWSE

The FormatCode field contains the complete account code including the different segments, without the separators.

The ActType identify the account type. In this example E=Expenses and I=Revenues (Income)

When creating a new BP, the user will chose from this list of GL account the relevant one to use or can be use update an existing blank BP record for this field.

For Items Documents:

  1. Create a user defined field as in Step 1 for account code field in Marketing Documents Title.

     2. In AR Invoice Window link the following query as a formatted search to this field:


This query will copy the GL account chosen in the BP to the Marketing document.

Note: Do not define Auto Refresh.

  1. Display the GL Account column in the Invoice rows  using the Form settings  and link the following query to the field for the formatted search:


Note this query is dedicated for AR  invoices; you may replace the table name according to your needs.

This query will copy the GL account from the header to the rows.

Define an Auto Refresh when the GL account header field is modified.

Process steps will be:

  • Choose customer (or vendor)
  • Choose items
  • Press Shift+F2 on the GL account Header fields
  • The GL Account in the rows will be refreshed automatically with the relevant account.

For Service Documents

  1. Link the following query to the GL account code in the rows


Define and Auto Refresh when exiting the column Description.

Process steps will be:

  • Choose customer
  • Type the row description
  • The GL account will be populated automatically
Ryan Howe

Ryan Howe

Partner, Clients First Business Solutions New Jersey

Ryan Howe is a Certified Public Accountant turned SAP Business One consultant. Leading the SAP Business One practice at Clients First, Ryan has over 23 years’ experience in the financial management and ERP industry, specializing in business analysis, relationship building, and business process optimization. When he’s not helping customers leverage SAP Business One to meet their short and long-term needs, you can find him at any Michigan State sporting event with his family – go Spartans!