Sage 500

When you want to make lookups load faster, there is an option available that may help.  I see many environments setup that when lookup screens are loaded, all of the data is populated. While the data is populating, you can only wait until its complete or click the Stop button. After the data has populated the lookup grid, then I see data being entered as a search criteria or filter. Instead of doing that, why not suppress…

In IM Options a user can specify whether negative on-hand balances will or will not be allowed in their system. If the decision is to allow negative balances, Sage 500’s Negative On-Hand report allows users to research and to correct any remaining negative on-hand balances at the end of each business day.

Remember that items using Lot, Serial or Both track method will not allow negative quantity on hand even…

The Settle Credit Card Transactions application is used to create Cash Management Bank Deposit batches containing credit card transactions from Sage 500 that have been settled at the processor using Paya Virtual Terminal. 

Given a date range, the application selects all un-deposited transactions from Sage 500, retrieves all settled transactions from the processor, and displays them in a grid.  The left hand side of the…

If you have not read my blog entitled "How to prevent access to the sage database" you may want to do that first before proceeding.  In that blog we setup a user in Sage 500 who could only access the data from the Sage 500 application. In this blog we will look at creating a user who only has access to data from Sage 500 AND read access from applications outside Sage 500.

There may be occasions where you need…

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. 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…

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…

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 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…

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 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…

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…

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. 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:

• Download Sage 500 ERP 2018.1
• Download Sage 500 ERP 2017.8.04

2. If you are prompted…

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
Maintain…

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

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…

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…

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…

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 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…

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 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 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…

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.