Archive | Tips and Training RSS feed for this section

Quick Tip: Year End in General Ledger and Sub-Ledgers

3 Jan

(reading time < 10 min)

Year End in Sage 300 is a simple process.  There are a few things involved but the timing of them is pretty much up to you.  The following assumes a calendar fiscal year but the steps are the same if your Fiscal Year does not end on December 31.

  • Create the new year in the Fiscal Calendar.  This can be done at any point but must be done before any sub-ledger transactions can use a date in the new year. Most people do this by mid-December.



General Ledger

The create New Year function closes the Income Statement Accounts to retained earnings and potentially deletes old Fiscal Sets (used for financial reporting) and old GL detail transactions (no sub-ledger data is touched). Most people run this within the first couple weeks of January but it can be run at any time.

You will not be able to post GL entries for the new year until this process is complete. THIS IS OK. The GL batches can sit unposted until you are ready to close the year.

  • Ensure that the prior year’s Closing Period and Period 12 (for GL) are unlocked – this will allow the Income and Expense account automatic closing entries to post.


  • Optional: Confirm settings – if the Current Fiscal Year is the same as the calendar year (2017 at the time of this writing), then the year has already been closed.  Note: the sample date is set in 2020.
  • Recommended but not required: It is a good idea to confirm account types.  Balance sheet accounts should be tagged as balance sheet and revenue and expense accounts as income statement.  Incorrectly tagged accounts will either not get closed when they should or will get closed when they shouldn’t.  The GL Accounts | Chart of Accounts screen is a great place to see this setting.
  • While you are here:
    • you can adjust the number of years of data to keep (subject to Sage 300 edition limits)
    • you can confirm that the Allow Posting to Previous Years option is set as you would like


  • Make sure you have a good backup of your database.
  • Open Create New Year from the Periodic Processing group – the screen will tell you the year to be created and the year(s) to be removed.  When you are ready click the Process button.




Nearly all of the sub-ledgers have a Periodic Processing menu.  Most have one or more items labeled “year end”, “period end”, “clear history”, etc.  I’ve shown the AP Year End below as an example.  NOTE:  ALL of these items are technically optional although you may choose to run certain features for business reasons.


Suggestions and Recommendations

  • Press F1 on each screen to get a description of each of the options.
  • DO NOT reset batch numbers – this will affect your ability to drill-down from the General Ledger.
  • Clearing Activity Statistics resets things like “highest invoice this year”, “last payment date”, “average days to pay”, etc.  Again, refer to the help files for specifics.
    • Note:  You will need to print all posting journals (preview is sufficient) for all batch types and create any GL batches from the sub-ledger prior to running the sub-ledger year end process.
  • There are statistics that are automatically segregated by year/period (depending upon the options selected for that module).  You don’t have to do anything to make this happen or maintain the statistics.
  • If you are going to clear history in one or more modules:
    1. Make sure you have a good backup. You can ask us about making copy of the database that you can open as a different company so that you can refer back to the data.
    2. Read the help files carefully.  Each option refers to a specific set of database tables/transactions.  The options are somewhat subtle at times and some of them sound similar.  History is only cleared within the module where you are working.

Quick Tip: Simple Screen Customization

27 Sep

(reading time < 5 min)

You are probably aware that Sage 300 grids can be modified user-by-user to size, reorder or hide fields.  Place the cursor between two column heading and left-mouse-click-and-drag to change the width (see below).


Click and drag on a column header to move it to a new location.

Right click on a column header to hide it (or to restore a hidden column or column ordering).


Sage 300 also allows for simple screen customization to remove fields outside of the grids that are not needed or that you want to hide from some users.  This must be done as the Sage ADMIN user.  On the screen you wish to change choose “Customize…” from the File menu.


Click the New button next to the Working Profile drop-down to create a profile.  Later we will assign users to this profile.  The customize screen shows the list of controls available on the screen.  Clear the checkbox next to the fields you wish to hide.


Fortunately, the process is interactive.  When you clear a checkbox the control will disappear on the screen in the background so you can see whether you have selected the desired field(s).  Unfortunately, there is currently not a way to keep a field visible but make it read-only.


Once you have made your selections, save the profile.  Note: if this is a new profile, all fields will reappear at this point because the ADMIN user is not assigned to the profile.  Now go to Administrative Services and start “Assign UI Profiles.”  Insert a Sage User ID and select the profile.  Log in as the assigned user and open the customized screen to see the changes.


Note:  Users can be assigned to more than one profile for different screens.  It is possible to have multiple profiles for the same screen.  Users can also be assigned to more than one profile for the same screen although this is not recommended as it can be confusing and the profiles can work to cancel each other.

What Do I Need to Include in My Backup?

16 Mar

(reading time < 10 minutes – these few minutes can save you hundreds of dollars!)

In the last few weeks we’ve had a few clients (and us too) fall victim to different variants of an encryption virus. This virus encrypts files on your machine and leaves behind messages explaining how to pay to get the files back.

In more than one case, we are finding that critical files are not getting backed up!  In more than one case, the database was part of the backup job but no one was monitoring it to make sure the backup was actually finishing.

Obviously, the databases need to get backed up and the job needs to be monitored to make sure it is running every day.  This is not a set-it-and-forget-it thing.  Think about how much money it would take to reenter one day of work let alone a week or more.  There needs to be an offsite copy of the backup as well.  What if your server gets stolen (has happened)?

In addition to the databases there are a few critical files that need to get backed up as well. The easiest thing to do is backup the entire Shared Data Directory including sub-directories.  You can see where this is by going to Help | System Information.


In addition to critical folders, this folder (root) contains your license files and possibly some custom macro configuration files.  Here are the specific folders you need within the Shared Data Directory:

COMPANY – this folder contains mainly log and temporary information.  Not so critical.  Although it may contain some custom macro configuration files.

DATA – unless you are on the Pervasive.SQL database platform this folder is probably empty.  If you are, this folder probably contains you Pervasive.SQL databases although it is not required for them to be within this folder.

SITE – this folder has your company and user lists.  Very important!

USER – this folder contains all of your user customizations – shortcuts, column ordering, printer selections, etc.

CUSTOM – this folder is one we typically create (or have you create) to store all custom forms, reports and macros.  This folder represents a huge investment for your company.  It is not required that you have this folder but nearly all of you will have it.

Financial Reports – you may have a folder within the shared data directory or within the custom folder that contains your Financial Reporter specification files. Being Excel workbooks these are especially “tasty” to these virus programs.  However, you need to verify on your system where these files are.  I know of more that one client who stores these files outside of the Shared Data Directory.  This is fine.  Just make sure they are getting backed up.

Payroll, ACA, Aatrix Folders – if you run Payroll 7.2 you will have some folders related to form filing.

I recommend just getting the whole Shared Data Directory tree to be sure!  Also, many think about what to do in case of fire or earthquake.  You need to have a disaster plan for your server and your data.  This really is a case where “an ounce of prevention is worth a pound of cure.”

Quick Tip: Your Friend the Finder (Part 2)

16 Mar

(reading time < 10 minutes)


In Part 1 of this tip we talked about some of the basic features of the finder and the ability to Set Criteria for a complex search.

The finder, like any grid in Sage 300, can be customized by user.  You can change column sizes by dragging the line between column titles.  You can move a column by dragging its title to a new position.


You can hide a column by right-clicking the column title and choosing the Hide option.  You can also use the right-mouse click to restore a hidden column or to put everything back to defaults.


Here are some other things you can do with columns in the finder:

Add a Column – open the Settings menu and choose Columns… to see and select any of the available columns for this finder.  If this menu item is not active, please ask your ADMIN user to give you rights to the finder columns.

The columns window is divided into two panes.  Available columns are on the left and selected columns are on the right.  Use the Include/Exclude or All/None buttons to move columns between the two lists.  Use the Up/Down buttons on the right to order the selected columns.  Click OK to save your changes and return to the finder.

All selected columns are available in the Find By list.


Highlight a Column – you can highlight frequently referenced column in the finder.  Let’s say that we want to make the Order Reference column stand out.  Open the Settings menu and choose Color…  Select Order Reference from the Field menu.  Use the Text Color and Background Color buttons to select the column coloring.  I’ve chosen a light-yellow background below.


Click OK to return to the finder.  The column is now formatted.  You can format any of the columns and they don’t have to be the same.  Use Settings | Restore Color Default to put everything back


Export from the Finder – this is similar to exporting from other screens but the finder only exports the table to which it is attached.  For example, exporting from the Order Entry screens export order headers, details, optional fields, etc.  Exporting from the order number finder exports only the order headers.

Choose Export from the File menu of the finder to open the export screen.  Select the desired file type from the list and enter/browse for a file name.

Note: the Excel version really has to do with the type of export Sage is going to perform.  It doesn’t have very much to do with the actual version of Excel you are running.


Select the columns you would like to see in your export.  Selecting the top item (orders in my example) and then right-clicking it will allow you to select/unselect all columns.

Even though the finder may have been filtered, the export is not by default.  Use the Set Criteria button to filter the export.  See Part 1 of this tip for an explanation of the Set Criteria feature.

Use Save Script to have record the file type, name, columns selected and criteria filter for a future export.  It will as you for a file name under which to save this information.

Use Load Script to load a previously saved script file.  You’ll be able to edit the columns, criteria, etc. prior to exporting.  This allows you to build one or more export templates.

Click OK to begin the export.

See the Field Names – when you export, Sage uses the field names as the column titles.  For example “Order Number” shows as ORDNUMBER in the export file.  You can see these same titles in the finder if you like.  Select the Settings menu and choose Field Name as Column Heading.  Go back to the menu and click it again to return the column descriptions.

Quick Tip: Your Friend the Finder (Part 1)

8 Mar

(reading time < 10 minutes)


The finder is useful during data entry and it can also be very helpful for researching transactions.  Here are some tips to get the most out of this great tool.

F5 – Everyone knows you can click the finder button (the magnifying glass button) next to a field to open its finder.  You can also open the finder for the you are currently on with the F5 function key. If the field has a value in it, the finder list will open to the closest matching value.  So if you know your item number starts with STR you can enter this before opening the finder to immediately get to this part of the list.

Find By – You can search using any column shown in the finder.  Use the Find By drop down to select the column.  Depending upon the column you choose you can either do a “Starts with” or a “Contains” search, or you can select an operator.  Then enter the value you are searching for in the Filter box.


Go to Filter First – In conjunction with the F5 key this is probably the greatest time saver for data entry.  By default, when you open a finder the cursor is in the grid.  If you want to search for a value, you have to click on the filter box each time to enter a value.  With the Go to Filter First setting, the cursor is in the filter box when you open the finder ready for your search.  Use the Tab key to get from the filter box to the grid.


Enter – Once you have found the record you want there are three ways to select it.  You can click the Select button, you can double-click on the record (any column), or you can simply press the Enter key.

Auto Search – when this is checked the finder will begin searching for records when you have paused for a couple of seconds.  Depending on which field you are searching, the number of records in your database and network conditions the search could take a bit to return.  If you weren’t ready to search it can be frustrating to wait for it to finish.  If you uncheck this box, the finder will not search until you click the Find Now button.

Advanced tip: if you are frequently searching on a field like a description, an address field, or a name and the search takes a long time, ask us about creating an index on this field.  It can dramatically improve your searching speed.

Set Criteria – the Find By box only allows you to search one field at a time.  Use Set Criteria to search by multiple fields/values.


  1. Select a column
  2. Click Add
  3. Double click in the cell to add a value

Below I’ve added the Order Date to the grid and entered >= 1/1/2016.  I double-clicked again on the cell to show the entry screen – this is how to edit the values that have been entered.  This will show all orders this year and later.


Next I’ve added Order Type and Ship to State to the criteria.  This will return orders (no quotes, future orders or standing orders) this year or later to with a WA shipping state.


By filling in the next row I can have it search in Oregon as well.  Note:  I do need to repeat the date and order type values.  Otherwise I would get orders this year for WA and everything for OR.



  • Delete – clears the currently selected column from the grid.
  • Save – save the criteria.  Until you change or clear the criteria they will be applied whenever you open this finder.  If you don’t save it, the criteria will go away when you close the finder.
  • Clear – remove a cell, column, row or the entire criteria filter.
  • Edit – same thing as double-clicking on the currently selected cell.
  • Show Filter – see the criteria translated to its query form.  This is a way to double check the filter.
  • OK – returns to the finder and applies the filter whether you saved it or not.
  • Cancel – returns to finder without applying the filter.

Stay tuned for part 2 where we discuss the columns in the finder and exporting from the finder.

Quick Tip: How to Reverse a G/L Batch

1 Mar

(reading time < 5 minutes)

Sage 300 makes it very easy to reverse a posted G/L Batch (or Entry).  First open the posted batch that you would like to reverse.

GL Rev Batch1

Option 1: Reverse the entire batch

Click the Reverse button at the bottom of the screen.

GL Rev Batch2

The Create Reversing Batch screen will open.

GL Rev Batch3

  1. Select the Batch option
  2. Create a new batch by clicking the new/add button next to the batch number (you can use an already-existing, unposted batch if you like)
  3. Update the batch description if you choose – you can change this later
  4. Set the option for the entry descriptions if you like – you can change these later
  5. Click the process button

This will create a new, unposted batch with all of the entries reversed using the same period in which they were originally posted.  You can modify this batch as much as you like and post it when you are ready.


Option 2: Reverse an entry in the batch

Find the entry number that you would like to reverse within the batch.  Then click the Reverse button at the bottom of the screen.

GL Rev Batch2

The Create Reversing Batch screen will open.

GL Rev Batch4

  1. Select the Entry option
  2. Create a new batch by clicking the new/add button next to the batch number (you can use an already-existing, unposted batch if you like)
  3. Update the batch description if you choose – you can change this later
  4. Update the entry description if you like – you can change these later
  5. Click the process button

This will create a new, unposted batch with the entry you selected reversed using the same period in which it was originally posted.  You can modify this batch as much as you like and post it when you are ready.

Quick Tip: How to Add a New Account While Comparing to an Existing One

29 Feb

(reading time < 5 minutes)

Sage 300 allows you to open multiple copies of nearly every screen so you can open an existing account (or customer, item, vendor, order, etc.)  on one screen while entering a new account on another.

Multiple Copies1

To enable this feature right-click on the screen’s shortcut and choose Properties.

Multiple Copies2

Check the Start Multiple Copies checkbox then click OK.

Multiple Copies3

If that option is grayed out for you, ask your ADMIN to change your Administrative Services security permissions to enable the Customize Company Desktop right.  After that is done, log out of Sage 300 and log back in and you’ll be able to set the option for your shortcuts.

Multiple Copies4