• How to prevent access to the Sage database

    Lets say you have Sage 500 user who also has SQL Server Management Studio(SSMS), and knows how to access the data through SSMS. Is there a way to prevent this user to see data from SSMS but still have access through the Sage 500 application?  The answer is yes!

    Sage 500 can make use of a T-SQL application role to restrict access to the application database.  Users are added to the Application Role group and the role uses…

  • Sage 500 ERP January 2019 Product Updates for v2017 and v2018 are released

    Sage 500 ERP January 2019 Product Updates for v2017 and v2018 are released. These updates include several fixes that are detailed in the product update release notes. These updates also include 1099 updates for the tax year 2018.

    The download links for client, server, client source, server source are available in the Sage Knowledgebase.

    Click the following link to access your update and documentation:

    Download Sage 500…

  • How to find Sage 500 Schema Changes using SQL Server Reports

    Earlier this month I wrote about a reporting feature available with SQL Server entitled "SQL Server Diagnostic Reports".  In part 2, we will drill a little deeper and look at a way to find schema changes using one of those reports.

    This time I will show how you can run a report that shows you if someone made changes to objects in a database. You will need access to SQL Server Management studio and use a login…

  • Track methods for inventory items

    Inventory items can be tracked by lot or individual serial number or can be both lot and serial tracked.  

    • Serial - a unique number assigned to a single piece
    • Lot - a unique ID assigned to a homogeneous quantity of material
    • Both - use both serial and lot numbers 
    • Negative quantity is not allowed for serial, lot or both tracked items
    • Back Flush in manufacturing module cannot be used for the raw materials that use serial…
  • Sage 500 ERP 1099 Updates for tax year 2018 are released

    Sage 500 ERP 1099 Updates for tax year 2018 and versions 2018, 2017, and 2016 are released.

    The download links for release notes, client, server, and source are available in the Sage Knowledgebase.

    1. Click the following link to access your update and documentation:

    Download Sage 500 ERP v.2018 1099 Updates for tax year 2018

    Download Sage 500 ERP v.2017 1099 Updates for tax year 2018

    Download Sage 500 ERP v.2016 1099 Updates…

  • How to import 1099 beginning balances

    1099 season is coming up and remember that in Sage 500 you can use AP/1099 Beginning Balances task to import beginning balances. A successful import inserts records into tapVend1099Bal table. 

    The supported file layout is fixed length. Delimited files are not supported. If a record contains a vendor, year, form, and box that already exist in the table, the BegBal field is updated with the value from the file. A warning…

  • SQL Server diagnostic reports

    SQL Server has some helpful built in reports to give you statistics about objects, indexes, and users and more. The best part about this feature in SQL Server is that its included without having to buy anything else. I will give you an overview and show you how to access/run the reports. In part 2 i will drill a little more into a specific report that can be used to find Schema Changes
    There are some 3rd party diagnostics…

  • How to change your preferences

    Where can you change settings for your userid in Sage 500?  Under System Manager, Tools there is a task named User Preferences

    On the first tab named Preferences, you can change things such as the ability to use the Enter Key as the Tab key. You can also turn on/off the Customizer icon and DataPorter icons as well.  If you are using SQL authenticated logins to access Sage 500 and you wanted to change your password to Sage…

  • Bank Reconciliation Hints and Tips

    Here are some hints and tips about Bank Reconciliation that will make it easier for you to perform and understand Bank Reconciliation in Sage 500:

    • The ‘Book Balance’ is the balance in Cash Management Module. It is not the General Ledger balance in General Ledger.
    • How is the Bank Balance (Book Balance) calculated in Reconcile Bank Statements: The Book Balance is the sum of any beginning balance entry made in…
  • Sage 500 ERP November 2018 Product Updates for v2017 and v2018 are released

    Sage 500 ERP November 2018 Product Updates for v2017 and v2018 are released. These updates include several fixes that are detailed on the download articles below. 

    The download links for client, server, client source, server source are available in the Sage Knowledgebase:

    1. Click the following link to access your update and documentation:

    2. If you are prompted…

  • What can you import into Sage 500?

    Sage 500 has multiple import tools and here is the list what each of them can import.

    Imports using DataPorter:

    Enter Inventory Transactions
    Enter Invoices
    Enter Journal Transactions
    Enter Purchase Orders
    Enter Quotes
    Enter Sales Orders
    Enter Vouchers
    Maintain Account Segments
    Maintain Accounts
    Maintain Allocations
    Maintain Customers
    Maintain Item Classes
    Maintain Items
    Maintain Natural Accounts
    Maintain Non-Inventory Items

  • How to create a custom lookup in Sage 500 ERP

    This month we will look at how to create a customized lookup. The customized lookup will help your Sage 500 users streamline their searching when performing lookups.  The following example is in the Maintain Vendors lookup used throughout various tasks in Accounts Payable.

    For example, when you are creating new vouchers. and click the Vendor lookup, it looks like this :

    The vendor lookup returns the columns shown above.  What…

  • Production Entry Returns vs Progress Entry Return in Sage 500 ERP

    Production Entry Returns

    Light Manufacturing and Advanced Kitting customers who use the Production Entry task can process Production Entry Returns despite the fact that they cannot maintain or manually create work orders.

    Work order transactions processed behind the scenes will be returned in the same manner as Work Order Progress Returns with variances posted against the original labor transaction. The user will; however…

  • Sage 500 Audit Tracking feature

    The built-in Sage 500 Audit Tracking feature helps track changes made in Sage 500. The areas in Sage 500 that can be tracked in this manner are  AP(Accounts Payable), AR(Accounts Receivable) , GL(General Ledger), and IM(Inventory Management).  You will be able to track additions, changes, and deletes in each module. Let's take a look at AP and review the setup first, do a couple of examples, and view the report that will…

  • Replenishment Terminology in Sage 500

    The Replenishment features in Inventory Management allow you to determine the appropriate quantity to purchase from a vendor based on current stock status, purchase and selling history, and projections. Here is the terminology used in Sage 500 to help you better understand it:

    • Demand – Projected future usage for an item
    • Working Days – Number of business days in an inventory period
    • Daily Demand – Estimated…
  • How to find invalid email addresses in Sage 500

    Here is a tip to help you find invalid email addresses associated to customer contact records. We have run across data had been imported from other systems with incomplete or wrong email addresses, that found their way into the EmailAddr field in the contact table.  These invalid email addresses will present a problem when it comes time to use the email address to send them an invoice from Document Transmittal for example…

  • Sage 500 v. 2018 enhancements for Document Transmittal when SMTP is used

    Sage 500 version 2018 has some enhancements for  Document Transmittal when SMTP transport method is used. There are two new check-boxes:

    Use SSL – Set this flag if the SMTP server requires SSL - standard security protocol for establishing encrypted links in an online communication. The best way is to find out from the SMTP server provider what their requirements are.

    Requires Credentials – Set this flag if the SMTP…

  • Sage 500 - How to find unposted batches before you upgrade

    The Sage 500 Database Upgrade Utility checks for unposted batches before the upgrade actually begins.  All batches in all companies must be posted or deleted to get past the message informing you that you have unposted batches.

    The T-SQL query I modified will help you find these open batches earlier in the upgrade process.  The first T-SQL query is the actual one that is executed near the beginning of the Sage 500 upgrade…

  • Multicurrency Revaluations in Sage 500

    Multicurrency Revaluations are taking a snapshot of the current picture to see your value as of certain date. Only foreign currency transactions or accounts are revalued, since home currency transactions are always booked at a 1.0 exchange rate. The system takes the current exchange rate from the system (based on the date) and compares it to a document (Voucher or Invoice) exchange rate. The difference is then booked as…

  • Sage 500 ERP 2018 is released

    Sage 500 ERP version 2018 is available!

    Sage 500 2018 Enhancements:

    AP vendor credit card clearing  
    New process to allow invoices paid by credit card to be recorded to the associated AP vendor to help expedite the settlement and cash distribution processes.

    Postal code and address validation
    New link connects to USPS.com to validate street addresses and postal codes on any address field which can reduce shipping-related…

  • Backing up your Sage 500 data

    Are you backing up your Sage 500 data ?  Are you prepared if your SQL Server machine crashes?  If not, you should formulate a plan to recover your data from catastrophe. There are many "best practices" and opinions vary. Here are some thoughts and ideas about backing up your Sage 500 data. You may also want to discuss with your Sage 500 reseller/partner and get their recommendations based on your environment.

  • Inserts and updates for inventory transactions tables

    Inventory transactions can originate from Purchase Order, Sales Order, Manufacturing, or Inventory Management modules. Here are the tables that are inserted and/or updated when an inventory transaction is posted:

    • Insert a record into the inventory transaction log table (timInvtTranLog).
    • Insert a record into the inventory transaction table (timInvtTran).
    • Insert one or more records into the transaction distribution table…
  • How to find details about customizations created by Sage 500 Customizer

    Customizer is a module/feature that can be used to make light modifications to many of the Sage 500 tasks. You can add textboxes, buttons, and store data into custom tables, using the Data Binding option, and also use Microsoft VBScript code. It can also work with Microsoft JScript, but VBScript has been, by far, the most common script code used.

    If you use Customizer a lot and have many customizations across different…

  • Sage 500 ERP May 2018 product updates for v2017 and v2016 are released

    Sage 500 ERP May 2018 product updates for v2017 and v2016 are released
    The download links for client, server, client source, server source are available in the Sage Knowledgebase.
    The Release Notes are contained in each download link. As a reminder, the download portal includes links to all the downloads. It is the one-stop shop for all supported versions/updates.
    After selecting the Sage 500 ERP product, click on My Downloads…

  • New Crystal Configuration Utility that helps work around the recent Sage Fixed Assets 2018.1 and Sage 500 issue

    Attention Sage 500 users who also use Sage Fixed Assets!

    I want to call to your attention to a situation you may or may not have experienced and suggest a new utility that replaces the workaround originally published.

    After installing Sage Fixed Assets v2018.1 and Sage 500 client on the same machine, customers are reporting errors when either previewing or printing Sage 500 reports.  Sage Fixed Assets v2018.1 installs a version…