Skip to main content

Business Central: Restrict Dimension Values to Specific G/L Accounts (Allowed Values Filter)

Quick answer
Business Central controls this on the G/L account, not on the dimension value. Use Dimensions-Multiple on the Chart of Accounts and set an Allowed Values Filter on the DEPARTMENT dimension. To keep MARKETING and SALES on Travel and Entertainment accounts only, you need two setups: one on the T&E accounts (allow only MARKETING and SALES) and one on every other account (block MARKETING and SALES).

Got this error? "Dimension value DEPARTMENT, ADM is not allowed for G/L Account, 8430." The account has an Allowed Values Filter. Change the department on the line to one the account accepts, or ask your BC admin which departments are allowed.

The example

The finance team wants the MARKETING and SALES departments used only on Travel and Entertainment & PR expenses. Every other account should reject them.

Why one setup is not enough

The Allowed Values Filter sits on the account. It controls which departments that account accepts. It does not control where a department can be used.

If you only restrict the Travel and Entertainment accounts, users can still post MARKETING to Office Supplies, Salaries or any other account without a filter. You need both:

Accounts Value Posting Allowed Values Filter
Travel, Entertainment & PR Code Mandatory MARKETING|SALES
All other P&L accounts Code Mandatory <>MARKETING&<>SALES

Step 1: Allow only MARKETING and SALES on the T&E accounts

  1. Open Chart of Accounts and select the Travel and Entertainment accounts.
  2. Choose Account › Dimensions-Multiple.
  1. Add DEPARTMENT and set Value Posting to Code Mandatory.
  2. Click the ... in the Allowed Values Filter field.
  1. Tick MARKETING and SALES, then click OK.

The filter now shows MARKETING|SALES.

Step 2: Block MARKETING and SALES on all other accounts

  1. In Chart of Accounts, select the other P&L posting accounts. Leave out the T&E accounts.
  2. Choose Account › Dimensions-Multiple, add DEPARTMENT and set Value Posting to Code Mandatory.
  3. Type <>MARKETING&<>SALES directly in Allowed Values Filter.
Type the filter, don't tick the boxes
Ticking ADM, PROD and PURCHASING saves a fixed list. Any department you create later is then blocked on all these accounts until someone updates them. The typed filter allows new departments automatically. If you open the ... page to check it, close it with Cancel. Clicking OK can turn your filter into a fixed list.
Don't mix both styles in one filter
ADM|PROD|<>SALES&<>MARKETING looks like "only ADM and PROD", but it allows everything except SALES and MARKETING, including PURCHASING. Pick one:
  • ADM|PROD – only these two. SALES and MARKETING are blocked automatically.
  • <>MARKETING&<>SALES – everything except these two.

Code Mandatory also makes the department compulsory on these accounts. If the department should stay optional, test whether your BC version accepts the filter with Value Posting left blank. For balance sheet accounts that never carry a department, set Value Posting to No Code.

Step 3: Test both directions

Create a General Journal line for a Travel account with department ADM and post it. Business Central stops the posting.

Then try MARKETING on any other expense account. It should be stopped too. If it posts, Step 2 is missing on that account.

Before you switch this on
  • Dimensions-Multiple applies the line to every selected account. If some accounts already have their own DEPARTMENT default, such as Same Code PROD, leave them out and update them one by one.
  • Open journals, recurring journals and unposted documents with MARKETING or SALES on other accounts will fail at posting. Clear them first. Posted entries are not affected.
  • New G/L accounts do not get the filter automatically. Add it when you create the account.

Quick Recap

1 T&E accounts › Dimensions-Multiple › DEPARTMENT, Code Mandatory, MARKETING|SALES
2 All other P&L accounts › Dimensions-Multiple › DEPARTMENT, Code Mandatory, type <>MARKETING&<>SALES
3 Don't mix a list like ADM|PROD with <> in the same filter
4 Test both directions before go-live

Have a question about dimensions in Business Central? Drop a comment below — happy to help.

Labels:

Comments

Post a Comment

Popular posts from this blog

How to Reverse Posted Documents in Business Central

  If you need to amend or change a posted documents in Business Central, there are few ways to do it instead of having you to manually create a reversal journal or Credit Memo to rectify a Posted Document. Before you proceed to Correct or Cancel any document that posted in Business Central, you need to identify the origin of the posted document, as the method of cancellation depends on the document type.   There are 2 types of documents that posted in Business Central. i.                      Journals – E.g., General Journals, Purchase Journals, Payment Journals, Sales Journals, Receipt Journals etc. ii.                    Documents – E.g., Purchase Invoice, Purchase Credit Memo, Sales Invoice, Sales Credit Memo etc.   A.       A.     C orrecting ...
Post a Comment
Read more

Foreign Currency Revaluation in Microsoft Dynamics 365 Business Central

Microsoft Dynamics 365 Business Central (BC) allow users to account for foreign currency transactions. BC also able to translate the registered foreign currency amount into functional currency that setup in the entity using the conversion rates which setup in Exchange Table.   Due to the exchange rates fluctuate constantly, as a dollar worth in foreign currency today might not equivalent to the same foreign currency worth the next day. Hence, users are required to adjust the open documents that transact in foreign currency which include open invoices, credit memos, and advance payments. Once the open documents transacted in foreign currency is revalued, the gain and loss which calculate after comparing the closing rates at the period end will be posted to profit and loss as unrealised gain and loss, ensuring that the subledgers and general ledger can be reconciled.   This process can be done using Adjust Exchange Rate in BC, a batch job to adjust the exchange rates fo...
Post a Comment
Read more

How to Consolidate Customer and Vendor Balances in Business Central

Understand that some customer of the company might be the vendor of the company too. There is a feature in Business Central that allows users to consolidate both customer and vendor balances without making unnecessary payment or receipts. Prerequisite Setup for Customer and Vendor Consolidation Page: Marketing Setup           1.        Setup a Business Relation Code for each Customers and Vendors.          2 .        Assign a number series under Contact Nos. A number will be pickup from the number series when there is a new Contact code created. To Link the Relation between Customer Card and Vendor Card 1.        Go to Customer Card, look for the customer that you need to link in Customer Contact. 2.        Confirm the Customer Name that you have selected. 3.        Link with the C...
Post a Comment
Read more