- How does a filter perform updates? Filters perform updates for different types of data as described below.
Financial information (CMA_JOURNALENTRY, CMA_JOURNALDETAIL)
An import filter can select one of four methods for the Import Wizard to use to keep track of updates: the number method, the date method, the string method, or the "none" method. These methods are described below.
- In the number method, the import filter assigns a number to each entry in the data file. When importing data, the Import Wizard saves the highest entry number received during the import process. On subsequent imports from the same file, the Import Wizard ignores all entries less than or equal to this number. An import filter assigns numbers to entries when the Import Wizard calls the filter's CMA_LoadEntry function by setting the lEntryNumberStamp member of the CMA_JOURNALENTRY structure.
- In the date method, the import filter's CMA_LoadCompany function specifies values for the current fiscal year and the current time period in the nCurrentFiscalYear and nCurrentGLPeriod members of the CMA_CTRLINFO structure. During the import process, the Import Wizard replaces all entries at and beyond the specified time period.
In cases where the user can make changes to prior periods in the current year but not in prior years, you can use the date method and set the nCurrentGLPeriod member of the CMA_CTRLINFO structure to 0 until the year is closed. This will refresh the entire year every time an update is run.
- In the string method, the import filter assigns two-part strings to entries, and the Import Wizard uses these strings to track updates. The first part of each string identifies an accounting subsystem, and the second part specifies the number of the last entry that was imported for that subsystem. For example, the Import Wizard might store a string such as "GL145," indicating the last imported GL entry is number 145. This information is contained in the szEntryStringStamp member of the CMA_JOURNALENTRY structure.
The string method allows you to use a different numbering system for each subsystem when determining which entries to include in an update. This information is stored in the EntryStringStamp field of the table AA_ControlInformation in the resulting SBCM database. When an update is performed, the process will check the number only for transactions from the appropriate subsystem when determining whether or not to update the record.
- If there is no method for determining changed or new data during the update process, the filter needs to use the "none" method of updating. The none method performs a complete import (that is, it replaces all data) every time the Import Wizard is used.
Account part information
The filters delete the old records before importing the new ones. They do this by deleting the primary unique row in the filter data structure. Any rows not in the source data are left alone (that is, a row is not deleted during an update if it is not part of the incoming data). For the prime account part table, there is already special logic in the filter process for performing updates (make sure that you keep the account category assignment when making any changes).
For instances where an account value is deleted, the filter process will not delete this account from the SBCM database. Since there may still be historical transactions that include this account, deleting an account may result in unreliable data.
These update methods cover most situations but they may not be suitable for determining incremental update information to pass to the filter for your application. If this is the case, you will need to implement a method for determining incremental update information within the filter. Methods can include storing pertinent information in the accounting application's data or in the Windows registry.
- What are the requirements for names of account parts? Account part names cannot contain spaces or hyphens. The individual account part values can contain spaces but not the name of the account part (that is, the szPartName member of the CMA_PART structure).
- I have an account part that represents my customers. How does this combine with the values contained in the CMA_ORGANIZATION structure? The value of the nAccountType member of the CMA_PART structure needs to equal ACCOUNTTYPE_CUSTOMER. In addition, the aPartCode members of the CMA_ACCTPARTCODE structure need to match the values passed into the szOrganizationID member of the CMA_ORGANIZATION structure.
- What are the requirements for the actual names of the account parts? Certain account parts must have specific names. For example, the account part that represents the customer or client values in the transactions must be named "Customer," and the account part that represents the product or stock attribute of the transactions must be named "Product."
- What are the requirements for account part codes? Each value specified in the szCode member of the CMA_PARTCODE structure must be unique within the account part. For example, each szCode value for the "GLAccount" account part must be unique. If the source accounting application does not require these values to be unique, your filter must implement a method to ensure that the account part values passed by way of the filter (CMA_LoadAcctPartCode) are unique.
For some applications where account numbers are not required, you need to adopt a methodology for creating account numbers that gives consistent account numbers for each import of the same data set.
- How does the import process get the account numbers for the individual journal detail transactions in CMA_LoadDetail? A journal detail line from the source application normally has various account values associated with it. The following example assumes there are two account parts defined in CMA_ACCTPART, GLAccount (Prime and Customer):
| Dr |
Cr |
GLAccount Number |
GLAccount Description |
Customer Number |
Customer Description |
| $1,000 |
|
1000 |
Cash |
10 |
Bob Francis |
| |
$1,000 |
1120 |
Accounts Receivable |
10 |
Bob Francis |
Examining the CMA_JOURNALDETAIL structure, there does not appear to be any members in which to place the account values associated with this transaction. The address of the CMA_ACCTPARTCODE structure, however, is referenced in CMA_LoadDetail. In the aPartCode member of CMA_ACCTPARTCODE, place your account values in the array in the order the account parts were defined in CMA_ACCTPART. The Import Wizard will pair this array with the detailed transaction to store a complete detail transaction line. The following are the values for the example above:
- How does the import process handle accounting calendars that are not 12 periods in length? The import process currently only supports 12-period accounting calendars. This will cover most situations in the small business environment. However, for situations where there are more or less than 12 accounting periods in the data, you can use different methods. You can compress the data from the additional accounting periods past 12 into the 12th accounting period. If there is a 13th accounting period for adjustments, include information from the 13th accounting period with the 12th accounting period. For a short accounting year (such as less than 12 periods) or for a long accounting year (such as more than 13 periods), either import only the accounting years for which there are 12 periods of data or display an error message to the user (by using the window handle passed in CMA_LoadCompany), indicating that the data cannot be imported. Otherwise, because the Import Wizard cannot handle short accounting years, it will simply use stFiscalYearEnd in CMA_CTRLINFO and create 12 period accounting years by counting backwards, regardless of any short years in the data. The raw data and any resulting month-to-month analyses, however, will still be correct.
- What is the entry/detail approach to importing transactions? The filter SDK assumes that the accounting transactions being passed to a given filter conform to an entry/detail (or header/detail) methodology. An entry is defined as top-level information for a set of detail transactions that balance (for example, debit = credit). In addition, all detail lines associated with an entry should share the same transaction date, transaction description (that is, primary reference), source code, and posting period and year. For accounting applications that support only the transaction details without supporting the corresponding entry methodology, the filter must determine which group of detail transactions should be grouped by entry (based on the preceding definition). In addition, it is crucial that the sum of the debits and credits of the detail lines associated with an entry are equal to one another, or balance.
- How do I ensure that adequate transaction details are presented to the filter? Determining how to create detail transactions and assigning the account part values to the transactions is usually a straightforward task. To ensure that adequate transaction details are presented to the filter for detail transactions such as sales invoices, however, you may need to trace the detail transaction (as represented in the accounting application's general ledger) to the sales journal. For example, if the accounting application contains summaries of sales information, it is necessary to trace this information back to the sales journal to get the detail account values and detail amounts of the transaction, such as product, customer, sales person, quantity, and amount. If you do not create detail transactions with this level of detail, the user will likely have a less-than-ideal experience when they use data from the SBCM database.
- When is it important to enter beginning balances? When performing the first import of data, it is important to remember to enter the beginning balances for the prime account part. In addition, you should enter the beginning balances for customers, products, and any other pertinent account parts. This will allow the database to adequately maintain a rolling balance for products, customers, and so forth so that SBCM can perform its analyses correctly. There are various approaches to doing this, such as "exploding" the detail for a transaction, or creating transactions that represent the detail beginning balances for a customer or product, and then setting the offset amount to a null value for that customer or product (by using the same prime account value for all these transactions).
- How does the Import Wizard process handle multiple currency information? If the source system allows for multiple currency data processing, the Import Wizard provides a method for bringing this data into the database. First, the fDualCurrency member of CMA_CTRLINFO should be set to True to indicate the data set contains multiple currencies.
The approach in this SDK to handling multiple currency information is to require that any information that contains a currency amount be duplicated to handle the amount in different currencies. The following is an example that uses the CMA_JOURNALDETAIL structure assuming the source system tracks transactions in both French Francs and euros.
Transaction # 101 Total Amount 1,000 FF Total Amount 166 euros
The filter process will pass two lines of data during the CMA_LoadDetail function for this one invoice. The first line will include the details of the journal detail line with the amount of 1000.00 and the dwCurrencyCode member set to CMACYID_FRANCE. The second line will be the same details of the journal detail line, except the amount will be 166.00 and the dwCurrencyCode member will be set to CMACYID_EURO. So, in essence, the SBCM database will contain lines that are duplicated except for the dwCurrencyCode value and the amount.
- What if I don't have any predefined currency codes in my software application? Some applications allow users to completely define the names and identifications of their currency codes. This leads to the problem that the import filter creator does not know which currency identifier is used for different currencies. In this case, determine the preferred methods of currency definitions in your user base and hard code the mapping to currency definitions used by the Import Wizard in their filter.
- How should I assign VAT accounts to an account category? VAT accounts are unique because of how they operate. For example, you might debit a VAT account when you purchase goods (since you pay VAT), then credit the same account when you sell goods (since you are collecting VAT). SBCM does not provide a detailed VAT analysis. For this reason, please assign your VAT accounts as you would any normal liability account to AcctCat 2190.
- What are the Account categories and how do I use them? Account categories are natural or posting accounts grouped in the accounting source application by purpose or by the nature of the accounts. Account categories allow for proper account groupings on financial reports and within the analysis tools. One example of these groupings is Cash. All cash accounts are grouped together within this category for proper reporting.
These categories are used for internal purposes only. They are assigned to the individual values of the Account Part that has a Part Type of "Prime" (the value of the szPartType member of the CMA_PART structure). The user will only see the account numbers as they are defined in their accounting application. Use Account categories to group the accounts by type (such as Cash, Receivables, Payables, Sales, and so forth).
Note If the same account can be in different account categories from one fiscal year to another, any reports will only use the most current account category. In this case, historical financials cannot be accurately reproduced.
- What are the main categories for the prime account part? When assigning account categories to the prime account part, most source accounting applications do not have the granularity of definitions offered by the filter. Use the account categories listed in the following table for these main groupings.
| Account category |
Default AcctCat mapping |
| Assets |
1190 |
| Liabilities |
2190 |
| Equity |
3100 |
| Revenues |
4000 |
| Cost of sales |
5000 |
| Expenses |
6500 |