Customer (Sales) Posting Accounts

Here’s a document I did a bit ago to help explain what the posting accounts meant as an aid to setting up a new installation.

We’ll start with the SOP Setup window.

If you use Sales Order Processing or Invoicing, choose whether to use the posting

accounts for:

• Sales from the customer or the item

• Cost of Sales from the customer or the item

• Sales Returns from the customer or the item

Sales > Cards > Customer (Accounts Button)

About Customer Accounts

The accounts entered on a customer card are used to default on to a transaction. Cash is always defaulted from the Checkbook If the Checkbook radio button is highlighted as in the screenshot above. If you do not have an account specified on the Customer Account Maintenance, the system looks to the company posting account defaults from the Posting Accounts Setup window (below). You will notice that not every account on the Posting Accounts Setup window appears on the Customer Account Maintenance window.

Administration Series > Setup > Posting > Posting Accounts

Accounts Receivable

The default account that receivable amounts for this customer will be posted to.

DR Accounts Receivable

CR Sales

Sales

The default account where sales amounts for this customer will be posted. In SOP, this account is used only if ‘Posting Accounts From’ is set to Customer.

DR Accounts Receivable

CR Sales

Cost of Sales

The default account where cost of sales amounts for this customer will be posted. The dollar amount of ‘Cost of Sales’ is not determined until the Invoice is actually posted. This is because the cost is not known until the invoice is posted. The FIFO layer cost is not known. This is a Cost of Goods Sold account. In SOP, this account is used only if ‘Posting Accounts From’ is set to Customer.

DR Cost of Sales

CR Inventory

Inventory

I have never found a use for this account because Inventory is always taken from the item. If you know when it defaults in, please let me know :).

Terms Discount Taken

Terms discounts might be offered to your customers as an incentive to pay their account balances before the due date. In order to use this account as a default the ‘Track Discounts Available in General Ledger’ must be selected on the Receivables Setup window. This is typically an expense account.

DR Cash

DR Terms Discount Taken

CR Accounts Receivable

CR Terms Discount Available

Terms Discount Available

Terms discounts might be offered to your customers as an incentive to pay their account balances before the due date. In order to use this account as a default the ‘Track Discounts Available in General Ledger’ must be selected on the Receivables Setup window. This is an Asset account typically included in the Accounts Receivable category.

DR Accounts Receivable

DR Terms Discount Available

CR Sales

Finance Charges

If finance charges are assessed on this customer, this is the account that will be credited. This is typically an income account.

DR Accounts Receivable

CR Finance Charges

Writeoffs

This account is used when a customer doesn’t pay the full balance on a document and the remainder is written off. This is typically a bad debt expense account, or a debit to the reserve for bad debts contra asset account.

DR Writeoffs

CR Accounts Receivable

Overpayment Writeoffs

This account is used when a customer pays more than the full balance and the overpayment is taken into income.

DR Accounts Receivable

CR Overpayment Writeoff

Sales Order Returns

This account is used when return documents are used in sales transactions. This is typically a contra account to sales revenue. Often, it is taken against Sales instead of using a separate account.

DR Sales

CR Sales Returns

Dex.ini file settings document download

It has come to my attention that the link to the Dex.ini settings file is broken. In addition to updating the document, I created a new link so that you can once again download it if interested. Please let me know if you have any trouble so that I can fix it – and e-mail you a copy.
Here’s the new link: Dex.ini settings document also known as https://app.box.com/s/xkgae2rz1vil1ap4o579
Until next time!
Leslie

Concatenating a range of cells in EXCEL

I often use Excel to create SQL UNION statements and update statements by harnessing Excel's concatenate function. Typically, I’m creating UNION statements. I list the column names from the tables I’m trying to union and then use Excel until I get them lined up and mapped correctly. My final step is to use the CONCATENATE function to append the necessary comma between the column names.

Recently, I created a view that combined the pay code, deduction code, and benefit code tables. I later used that view to create a SmartList object so that all of those different codes could be viewed in one place. You can download the SmartList on GPUG's 'share my code' site. Or e-mail me for it (leslievail@earthlink.net). I used Excel to build the UNIONs and the CONCANTENATE function to insert the commas. While using CONCANTENATE accomplished my objective, it was a bit time consuming than I had hoped. There has to be a better way, I thought. And there is.

The CREATE_DELIMITED function. Using a function to join a range of cells together separated by a certain character (or characters) is much easier than using the CONCATENATE function. I found an article on the Internet the other day that described such a function. It works magnificently. If you use Excel to create SQL statements as I have described, I recommend that you copy this function into your Personal Macro Workbook. By creating the function in your macro workbook, you’ll be able to use it in all of your workbooks. Katie Giles published this function on her blog on September 12, 2009. I modified it slightly to fit my needs, but the logic was created by Katie Giles.

The following text will show you how to create and call the function, how it works, and its limitations.

Concatenating the old fashioned way To combine four cells (A1, B1, C1 and D1) into one field, separated by commas, you could use the following two options: 1. Hard code the concatenation using the CONCATENANTE function and include the literal string between each cell: =CONCATENATE (A1,", ",B1,", ",C1,", ",D1) 2. Alternatively, you can use the ampersand to join the cells together =A1&", "&B1&", "&C1&", "&D1 Both of the options above will return the desired output, but if you need to join many cells together, the formula could become quite long and hard to manage.

Using a function to concatenate cells Having a function that will accept an optional parameter for the delimiter is much more efficient than creating the formula by hand. The VBA code below creates the function I now use to create my unions. Example VBA Function

Function CREATE_DELIMITED (ByVal cellRange As Range, Optional ByVal delimiter As String)
Dim c AS Range
Dim DataStatement AS String
Dim Count AS Integer
Count = 0
DataStatement = ""
For Each c in cellRange
Count = Count + 1
DataStatement = DataStatement & c.Value
If Count < cellRange. Count Then
DataStatement = DataStatement & delimiter
End If
Next
CREATE_DELIMITED = DataStatement
End Function
 
(thanks to Perpetual Acumatica Learner for correcting one of the lines of this code)

How the CREATE_DELIMITED function works To call the function, you would use the following syntax: =CREATE_DELIMITED (range [, string]) The function accepts two input parameters, a range and a string. · range is the selection of cells you want to join together · string is the character(s) you want to use for the delimiter. This is an optional parameter. The function uses a count variable to check whether the current cell is the last one in the range. You need to know when you are on the last sell so that you don't append the delimiter to the end of the statement.

Joining a range of cells with the CREATE_DELIMITED function Using the CREATE_DELIMITED function to create the concatenated result would look like this: =CREATE_DELIMITED (A1:D1,", ") The range is A1:D1 and the string delimiter is a comma and space.

Joining cells from multiple rows If the range you want to join includes more than one row, Excel works through each row in turn. For example, if your function call resembled the following: =CREATE_DELIMITED (A1:D2,", ") Excel would join cells A to D on row 1 followed by cells A to D on row 2. You could also achieve this by using two function calls and concatenating them together. The formula would look like this: =CREATE_DELIMITED (A1:D1,", ") & ", " & CREATE_DELIMITED (A2:D2,", ") If you were to use the built-in CONCATENATE function, the formula would look like this: =CONCATENATE(A1,", ",B1,", ",C1,", ",D1,", ",A2,", ",B2,", ",C2,", ",D2) The formula using the CREATE_DELIMITED function is by far the easiest one to use.

CREATE_DELIMITED Function Limitations The CREATE_DELIMITED function is only suitable for joining a range of adjacent cells. This function cannot be used to join cells in different locations on a spreadsheet or to join text strings entered directly into the formula (rather than being entered into the cells themselves).

Until next post!

Leslie

Paste to GP from Excel - the wait is over!

This window is a mock up – but it’s close to the real thing.  Included in the R2 release that is due out this spring, is the Paste feature! You will at last be able to past a general ledger entry from an Excel spreadsheet. It’s simple to set up and simple to do. First, your journal entry distributions need to be entered as below on a spreadsheet.

Next, highlight the information you want to put into your journal entry, and then put your curser on the first line of your journal entry. Finally, push the [Post] button and your distributions will populate the journal entry.

New Dex.ini switches added to the list

Thanks to Rick Kohler I can add some new Dex.ini switches to my collection. The switches below control the behavior of the HITB (Historical Inventory Trial Balance) tool.

HITB_SkipReconciles=TRUE	Enables Step 2 without having to complete all of the reconciles. Thanks to Rick Kohler for this switch.
HITB_SkipErrorChecking=TRUE	Enables Step 3 regardless of whether errors exist during the data integrity checks. Thanks to Rick Kohler for this switch.
HITB_SkipClearingTransactions =TRUE	Allows user to perform Step 5 regardless of whether balances still exist in the inventory accounts. Thanks to Rick Kohler for this switch.
HITB_SkipVersionChecks=TRUE	Allows the user to enter the window if on a version previous to build 1061. Thanks to Rick Kohler for this switch.
HITB_DebugFile=c:\somefile.txt	Used to create a detailed debug log of the reset dictionary (Dynamics GP must be re-launched to take effect):