Clients First USA
Clients First knows their software, but they are also astute business people, and they really understand manufacturing and distribution in particular.
Jimmy Witcher, COO, Merrick
Read More
About UsService OfferingsProduct OfferingsIndustries ServedDownloadsBlogTestimonials

Subscribe to our mailing list

* indicates required
    Interested in    
Hot Topics

Posts Tagged ‘new york’

SAP Business One Tip – How to determine the next available FIFO layer and its remaining quantity?

Wednesday, April 10th, 2013

Have you ever wanted to know what FIFO layer in your on-hand inventory is going to be used next, its unit cost and how much of it remains on hand?

When an item is managed by FIFO (First in First Out), layers are created in the system as stock receipts are posted in the OITM or OITW tables. Information relating to the subsequent issuances of these FIFO layers is also stored in OINM. Likewise, an A/P Invoice created for this item will create new layers in OINM. These records however, do not carry information as to the open quantities and the current costs per open layer. In order to determine the next available FIFO layer, its remaining quantity and cost, the user needs to run a report on OINM using this query.


/* Unlike items managed by Moving Average and Standard valuation methods, the item cost of items managed by FIFO are only accessible through OINM. This query will enable the user to find the next item cost for a given item numbermanaged by FIFO. */

Select Top 1 T0.HTransSeq, /* The highest transaction number represent the highest revaluation line for a given layer. Selecting the top 1 when order desc will enable to select the last revaluation of the layer */

T0.Transtype, /*Provide the information on the origin of the layer*/

T0.Base_REF, /* Provide the document number when exists */

T0.ItemCode, /* Item number */

T0.calcprice, /* Cost price */

T0.openqty, /* available qty for the layer */

T0.BaseCurr, /* currency of the transaction */

T0.TransSeq, /* Layer number */

T0.HTransSeq /* Highest revaluation of a given layer */



Inner Join

OITM T1 on

T0.Itemcode = T1.Itemcode

Where T1.Evalsystem =’F’ and /* check only the item manage by FIFO */

T0.openqty > 0 and /* exclude the layers where the open quantity is null */

T0.itemCode = ‘FIFO3’ and /* Select Specific item number */

T0.Warehouse = ‘[%0]’

Order by

T0.HTransSeq desc /*Order desc enable to select the last layer revaluated */

Epicor Implementation

Saturday, February 16th, 2013

Epicor Consulting – Tips for Successful Implementation Strategies for Epicor 9


1. Epicor Implementation Team

Think about who you want on the implementation team. The quality of your team members is important to the success of the implementation. Each team member needs to have detailed knowledge of the current procedures. The Implementation team needs to understand and incorporate upper management’s vision of the future into the implementation.

2. Epicor Consulting

If there is nobody on the Implementation team that has experience with Epicor Software and/or a project manager, it is important to find an Epicor consultant to fill in the voids. The Epicor consultant can be from Epicor or one of the Epicor partners. Make sure they are experienced with Epicor implementations (not Oracle or SAP). Epicor also has a Consultant Certification program in which the consultants take dozens of tests to prove the consultants understanding of the Epicor Software product. Selecting an Epicor Certified Consultant can help ensure a successful implementation.  Many find partners to be advantageous over Epicor directly due to the close lines of communication to people who have a real stakeholder interest in your outcome.

3. Epicor Hardware Requirements

Understand the recommended Epicor “Hardware Requirements” to properly run the Epicor system. This includes servers and the client computers. Too many companies believe they can reduce cost on the recommended hardware. Epicor has a Hardware Requirement Sizing worksheet. This is the minimum recommendations. Any extra money spent on Hardware will be money well spent.

4. Get a head start on the Epicor Implementation

  • Getting a head start can help keep the implementation on-time and can help you keep your sanity. It can be tough at times to complete your tasks for the Epicor Implementation and get your normal job duties completed. It is much easier and faster to perform the following tasks as you are performing your standard routine than it is to set down and document over a couple days from memory. Meet with key personnel and have them start collecting the following information:
  • Collect sample documents given to you that you use to make decisions? Just a page or two highlight important fields and make a note of how the data is used. This includes legacy reports, Access databases, Excel Spreadsheets, Word Documents, anything else not listed.
    • Daily reports
    • Weekly reports
    • Monthly reports
    • Yearly reports
    • Other on-demand reports
    • Phone Calls
    • Emails
    • Faxes
    • Handwritten notes, Post-its, Napkins……
  • Collect sample documents that you create so you and others can make decisions? Just a page or two highlight important fields and make a note of how the data is used. This includes legacy reports, Access databases, Excel Spreadsheets, Word Documents, anything else not listed.
    • Daily reports
    • Weekly reports
    • Monthly reports
    • Yearly reports
    • Other on-demand reports
    • Phone Calls
    • Emails
    • Faxes
    • Handwritten notes, Post-its, Napkins……
  • Document reports that you get and have no value.
  • What information is missing that you need to make better decisions?
  • Approximately how much time each week are you spending doing certain functions?
  • Track it several weeks daily to get real numbers. Be brutally honest.
    • 10% Planning
    • 20 % Paperwork
    • 10 % Deleting SPAM emails
    • 60 % Firefighting
  • What Software Products do you currently use?
    • Spreadsheets Microsoft Excel Other ______________
    • Word Processor Microsoft Word Other _____________
    • Email Microsoft Outlook Other ______________
    • Contact Management Microsoft Outlook Other ______________
    • Project Management Microsoft Project Other ______________
    • Database Software Microsoft Access Other ______________
    • Flowcharting Microsoft Visio Other ______________
    • CAD Software Name : ________________
    • ERP Software Name : ________________
    • Other software not mentioned : ________________________________
  • What items are on your wish list or goals for the Epicor implementation? List them and rank them by priority.
  • Document your process flow. For example:
    • Receive email from customer with PO info.
    • Email Sales Manager to get approval of pricing
    • Email Finance Credit Manager to get Credit approval
    • After everything is approved Enter Sales Order into current software
    • Print Sales Order (4 copies)
      • Copy 1 – Email to Customer
      • Copy 2 – Take to Purchasing
      • Copy 3 – Take to Manufacturing
      • Copy 4 – Take to Finance
  • What are the challenges and problems that keep repeating?  List them and rank them by priority.
  • Begin clearing your plate of any ‘procrastinated’ projects so that when the ERP implementation begins you have as free a schedule as possible.
  • If an option, ask key implementation team members to take vacations before the project starts to minimize project delays.

Performing the above steps over a several months will give you a head start on the implementation. This is more important if you have certain monthly/quarterly/yearly documents that you may not remember.

5. Epicor 9 Software Installation Checklist

One of the most important steps of the Epicor Signature Implementation Project plan is to get the Epicor software installed and tested. The install process is complex and needs to be verified before the Epicor installation consultant leaves. Once the Epicor install consultant leaves it could be weeks before rescheduling.

Before the Epicor consultant is scheduled to arrive and install the software verify the following:

  • Verify Epicor has sent registration key codes to all Epicor modules purchased. If you are missing registration key codes, contact your Epicor Salesman immediately to get all of the codes. When you receive the fixed registration key codes, verify them again.
  • Verify Epicor has sent the correct DVD’s to all Epicor modules purchased. . If you are missing DVD’s, contact your Epicor Salesman immediately to get all of the DVD’s. When you receive the replacement DVD’s, verify them again.
  • Once you have all of the hardware, software and registration codes have arrived you can schedule the Epicor Install consultant.
  • Before the Epicor software installation is checked off as complete. Be sure to verify the following:
  • Verify database software is installed and tested. Download and install any outstanding patches.
  • Verify Epicor modules purchased are installed on server. Third party modules will probably be installed later. Download and install any outstanding patches.
  • Verify Epicor help is installed and working.
  • If embedded education was purchased, make sure the icon exists and verify education is installed and working.
  • Verify the client software is installed on several client computers and is working.
  • Verify someone at your site is trained on installing the Epicor client software.
  • Verify Crystal Reports developer is installed and document which computer it is installed on.
  • Perform prints and print previews on Crystal Reports and Progress reports from a client.
  • Verify Backups routines are created, tested and scheduled to run nightly.
  • Reboot the Epicor servers and make sure Epicor restarts automatically.
  • Get all Epicor Software media together and store somewhere safe and where it can be found.

You do not want to get everyone excited about Epicor and they can not log in to do the training because an installation step was missed.

6. Training. Training, Training

Training is one of the most important processes in the correct Implementation of Epicor 9. Over time, people come and go and are moved to new responsibilities. Training needs to be performed for each event. Not just the software training but the business processes that lead and follow that persons responsible process. It is important that the person understands the data being supplied to them and the data they are supplying to the next person. Epicor “Embedded Education” is extremely important to the success of the implementation. This is not the spot to save on the costs.

  1. Implementation Team needs this education to best merge current business processes with Epicor functionality. Do not force current business processes into Epicor. Business processes after Epicor should be much simpler.
  2. Users will then need the “embedded education” to use Epicor to the fullest extent. Education courses should be reviewed after the implementation to pick up features that overlooked during implementation.
  3. Future New/transferred employees are often just thrown into the position with no or little training. Have them go thru the “Embedded Education” for their position.

Go to the Clients First Epicor 9 Product Demos and training Video site

Sign up and watch all of the videos that pertain to your position.

7. Data Conversion

Be realistic about converting legacy data. You should not convert the entire legacy database.

  • Convert only the Master Tables. Beyond that consider converting master records used recently. For example, only suppliers used in the last two years or parts used in the five years.
  • Transactional data (Purchase Orders, Sales Orders, Inventory Transactions, etc.) should not be converted. This data can be exported to excel spreadsheets or printed to PDF for easy inquiry.
  • Be sure to understand the Epicor fields when mapping the legacy fields to the Epicor fields. For example, is the legacy field a character fields and the Epicor field is a number only or is the legacy fields 50 characters long and the Epicor fields is 30 characters.
  • Clean up the legacy data during the conversion. Most conversions use CSV formatted files. Export the CSV file, then load it into excel. It is normally easier to clean up the data in excel. Some examples of data that need converted are:
  • Fix part descriptions that are spelled wrong, incorrect abbreviations, or formatted incorrectly.
  • Delete duplicate parts or suppliers
  • Add columns for fields that Epicor needs and your legacy system does not have.
  • Enter the needed data in the new column.


8. Testing, Testing, Testing

During implementation be sure to test.

  • Each ERP system will have several different ways to do something. Test each one to see which one is correct for you company. Document your findings from the tests. I have seen on several occasions the same testing performed two or more times because the people forgot the findings.
  • Test each product line from Quote to Cash until needed
  • Test any unique internal company requirements. First decide if this is truly an unique internal requirement? After discussions you may find that it is the way it has always been done and nobody knows why. If this is the case get rid of the requirement. Document the findings and how it was handled.
  • Test any unique Customer or Supplier Requirements. Make sure it is still a requirement. Document the findings and how it was handled.
  • Test Security settings. Document the findings. Fix the finding and test again.

9. Change Management

Implementing Epicor will change the entire company. Procedures will change, peoples work and responsibilities will change. The sooner everyone understands this, the quicker people quit fighting the implementation. On the rare occasion it is required to remove a implementation saboteur.

  • Determine how Epicor will affect current procedures and job positions
  • Document and communicate what changes are taking place.
  • Someone in each organization needs to take ownership of each Epicor module
  • Document and communicate the change management heroes.

If everyone is expecting everything will be handle the same then the company is wasting time and money.

–“A successful implementation should convert the company from a reactive to a proactive organization.”

10. Implementation Scope

Plan the implementation in phases. If your company is using a smaller ERP package, don’t purchase and plan to implement all of the extra packages during phase one. For example, you eventually want to use EDI but there is not an immediate need. Purchase EDI late and implement during phase 2. Doing too much during implementation can overwhelm the implementation team and the company.

Contact us in the chat box below or call us at 866.677.6290

Dynamics NAV Navision – Implementing Mandetory Fields

Friday, April 20th, 2012

Probably this is the feature most missing from Navision. As a record is instantly saved when it’s primary key is populated, it’s almost impossible to make fields mandatory. There are simply no perfect solutions for this problem. Some of the wrong ones I managed to come up with are discussed here.

Managerial control

Set important fields red. Create a report that shows records that have missing fields, f.e. :

(OnPreSection trigger)
Global variables:
ShowRecord – Boolean
ErrorText: Text – 1024

Global Text Constants:
Text001: “is missing.”


If Description='' then begin
    ErrorText+= FIELDCAPTION(Description) + ' ,';

If "Unit Price"=0 then begin
    ErrorText+= FIELDCAPTION("Unit Price") + ' ';

ErrorText+= Text001; (more…)

How to use Pivot Tables in Excel

Monday, March 26th, 2012

Here is an ‘oldie but a goodie’ from our original site that we’ve had people ask for often.  This is more relevant for Microsoft Office 2003.  These can be used nicely with all our ERP packages including:

  • Microsoft Dynamics NAV (Navision)
  • Microsoft Dynamics AX (Axapta)
  • Epicor 9
  • Microsoft Dynamics GP (Great Plains)
  • Microsoft Dynamics SL (Solomon)
  • Sage MAS90, MAS200, MAS500 (MAS 90, MAS 200, MAS 500)
  • SAP Business One

PivotTable Reports 101

Let’s suppose you’ve compiled a large list of data – for example, sales figures for every product your company makes. But now you’re ready to distill some meaningful information from the data. For example, you might want to answer the following questions:

· What is the total sales for each product by region?

· Which products are selling best over time?

· Who is your highest-performing salesperson?

For these and other questions, you can create a PivotTable® report – an interactive table that automatically extracts, organizes, and summarizes your data. You can then use the report to analyze the data – for example, make comparisons, detect patterns and relationships, and analyze trends.


Read on to discover what you can do with a PivotTable report.


Dynamics NAV (Navision) – Export to Microsoft Word & Excel with the click of a button

Wednesday, March 7th, 2012

You have a form open and you want to copy it over to Excel. Just click a button. Sure, you’ve always been able to copy and paste to Excel from NAV, but now, with the click of a button, the entire form is sent to Excel, including a worksheet for each header tab, plus a worksheet for the data. No selecting the data, no opening Excel, no adding information so you can remember what this data relates to. BAM! It’s done.

The lines of the purchase order are shown in the sample, but note the other tabs that provide header information.

The lines of the purchase order are shown in the sample, but note the other tabs that provide header information.

You want to send a letter to a customer, either hard copy or email. Just open the Customer Card and click on the “Send to Word” button. BAM! It populates the letterhead with your logo, customer address, etc., then all you have to do is type in your message. If you’re the primary salesperson for this customer, your name is also printed at the bottom of the document. Print it, sign it, and mail it; or if you’re using Outlook, just do a send email and send it as an attachment to your customer.


Automatic ‘Send to Excel’ and ‘Send to Word’ capability was introduced in Microsoft Dynamics NAV 5.0 and uses Style Sheets to format the output from almost any NAV form, excluding matrixes, to a Word document or Excel Workbook. Just click a button, and the information will be displayed on a document or spreadsheet that is opened automatically. The output is pre-formatted using the Style Sheets with row header, tab description, field names, etc. inserted for you. It’s a great tool and its uses are only as limited as your imagination.

Don’t like the format of the Word or Excel documents? Clients First will be happy to customize the Style Sheets that create the outputted document for you. Need more Style Sheet options? We’re here to help you out. Are you already on NAV 5.0 or above and clicking on the buttons isn’t doing anything? Call your Support Rep, there is some installation needed to activate this feature.

Already using this functionality and want more control without contacting your Clients First Support Rep? New functionality has been introduced with Version 2.0 of the Style Sheet Tool for Microsoft Dynamics NAV which provides the end user with tools, including a Style Sheet card/form, which allows you to choose some field and formatting options for documents. Also included are improvements for date formatting, Mail Merge document properties, Style Sheet Tool Objects, and more. This upgrade is only available on NAV 2009 and NAV 5.0 SP1.

Please contact Clients First for more information.  Thanks to Karen Wevick from our Alabama office.