Accounting Setup and Usage - Sage

Accounting Setup and Usage - Sage



Accounting Setup and Usage - Sage




//Preparing for Sage 50 Integration (DLLs)


Realtrac integration with Sage 50 is such that we have to use a specific set of files depending on the version/year of the Sage 50 product that the customer uses.  Sage releases updates often during the course of the year and when this happens, it may break compatibility with the Realtrac and Sage integration. It’s always a good idea to check in with Realtrac before performing your Sage upgrade to make sure the integration is compatible.




Fig 1 




 For example, in Fig 1 above, Sage released 4 different versions of the Sage 50 2015 product. The folders are labeled “2015.0.0.106”, “2015.1.0.150”, “2015.2.0.196” and “2015.3.0.248”. So how do you determine which file you need to use?  On a customers PC with Sage loaded, we need to check what version of Sage is currently installed.

Open up Windows Explorer and navigate to the C:\Program Files (x86)\Sage\Peachtree\API\ directory (this is on a 64 bit version of Windows, for 32 bit Windows, go to “normal” Program Files, not Program Files (x86)). Located in that directory is a file called Sage.Peachtree.API.dll. Right click on this file go to Properties and then switch over to the Details tab. Note the version of the file.



Fig 2



After right clicking on the file in Windows Explorer, we see the file is version 2015.0.0.106. This corresponds with the name of a directory in Figure 1. Traverse to the proper directory in Fig 1, and copy all the files to C:\Program Files(x86)\Realtrac\Realtrac Client\. Confirm that it’s OK to overwrite the existing files. The 4 files you will overwrite will be: 
  1. AccountingSetup.exe 
  2. RealtracPeachtree.dll 
  3. Sage.Peachtree.API.dll
  4. Sage.Peachtree.API.Resolver.dll 
There, that was easy, right? (Yes, when the customer upgrades from one version to the next, they will need to change these files. It’s unfortunate. We may have a more elegant solution in the future.) 


//Selecting Company and Accounts

Now we need to pick the Sage 50 company and specific accounts we wish to integrate. With Sage 50 running and logged in (if the company has password protected their account, it needs to be logged in) run the Realtrac Accounting Setup program (available in the Start Menu). Realtrac Sage Integration v.3.1 Page 4 When you run Realtrac Accounting Setup, the year of the Sage on your customers PC should match the title bar, IE: “Realtrac Accounting Setup (Sage 2014)”. Flip over to the Sage 50 tab, and click the Company button. After a moment, the My Company drop down box should unlock, and you can drop down in that box and pick your company. With the company selected, then click the Accounts button. When you click the Accounts button, Sage 50 needs to authorize an external program to allow it to access the account list. After you click the Accounts button, switch over to Sage 50. If there is not a prompt on the screen saying “An external application is trying to access Sage 50”, restart the Sage 50 program. Upon loading Sage 50 again (and logging in, if necessary), the prompt will appear. Click “Always Allow”. Clicking “Allow Once” or “Don’t Allow” is permanent! Do not do that. If you had to reload Sage 50, you may have to click the Account button again in the Setup utility. Momentarily, the Purchase Orders, Invoices and Credit Memo boxes should unlock and allow you to drop down and select from the list of accounts. Typically the Invoices and Credit Memo accounts will be the same, and in most cases, the account is named “Accounts Receivable”. We need them to select all 3 accounts, even if they user is not using Realtrac for generating Purchase Orders to post to Sage (which is normal). With all 3 accounts selected, click the Save button.

Note: Much of the above information will never be used if a user is running their Realtrac in “Sage GL Account” mode. That said, this initial account setup must still occur and be saved before the integration can continue.



A Note on “Sage GL Account” Mode
Realtrac offers a “Sage GL Account” mode which we shall document fully later in this manual. Running in “Sage GL Account” mode is not recommended for a typical Realtrac customer. Immediately following this section, this manual discusses how Invoice and Purchase Order posting occurs in Realtrac. Please understand that some of the details in these sections differs when running Realtrac in “Sage GL Account” mode. Differences in the operation shall be detailed in the specific “Sage GL Account” mode section near the end of this document.



//Creating Invoice Item in Sage 50

Before we push our first invoice in to Realtrac, we need to create our default item in the Sage 50 inventory. Within Sage 50, click on the Inventory tab, and within the Inventory Items box on the screen, click and choose “New Inventory Item”. Create an inventory item similar to the one in the screen shot below. We need the item ID to be “REALTRAC”, but the description can vary. Typically the GL Sales Acct is “Sales of Materials”, but the user can adjust that if they wish to use a different credit account for sales.








//Post an Invoice 

At this point, we are ready to test our system. We will try to test post an invoice (you can try a PO if you choose as well). Load up an individual invoice, and click the POST button. Once again, you will need to confirm that the application is allowed to connect to Sage 50. Switch over to Sage 50 and there should be a prompt asking if it’s OK. Click “Always Allow”. (If there is no prompt, restart Sage 50, open the company and you will see the prompt.) Hopefully at this point, the Invoice will switch from Not Exported to Exported in the upper right. If there is a problem, the Options button will illuminate green, and you can click in there and read the System Messages.



Purchase Order Integration

Sage 50 currently does not allow 3rd parties (such as Realtrac) to automatically add items to the master item list in the Sage 50 product. Before we’re able to export a Purchase Order from Realtrac to Sage 50, the items on the Realtrac Purchase Order need to be on the Sage 50 Item List. Realtrac Sage Integration v.3.1 Page 6 Let’s take a look at an example. Below is a simple 1 line Purchase Order for 20 units of 1018 10.0” RD. stock.








Over in my Sage 50 Company, I don’t currently have an entry for 1018 10.0” RD. (To see your current item list in Sage 2015, click on the “Inventory and Services” tab, then under the “Inventory Items” button, click on “View and Edit Inventory Items” menu option.) 







This means if I attempt to Export my Realtrac Purchase Order to Sage 50, Realtrac will provide the following warning:








Realtrac provides us a warning saying we need to add the item: 1018 10.0” RD. to the Sage 50 item list. Switching over to Sage 50, we navigate to the Inventory & Services tab, then from the Inventory Items button, we select “New Inventory Item”. The Sage 50 Item ID must match the Item Name on the Realtrac Purchase Order. Realtrac does not use/need the Description field, so you may provide as much or as little information in the Description field as you choose. The Item Class does not matter for Realtrac, so choose the Class best appropriate for the material and your organization (most organizations typically select “Non-stock item”). Confirm that the Account Codes meet your needs, and click the Save button on the form.








With the Item saved in the Sage 50 item list, we can now try to export our Realtrac Purchase Order again. After returning to Realtrac, and clicking the POST button again, we see the Purchase Order posts successfully.






Switching back to Sage 50, we can load up Purchase Order 14404 and confirm that the Purchase Order did successfully make its way to Sage 50




We only need to create the Sage 50 item the very first time we post a Purchase Order with it. In this specific example, since I’ve added my 1018 10.0” RD. to Sage 50, all future PO’s for this item will simply work direct from Realtrac in to Sage 50.



Sage GL Account Mode

Within the System Settings tab of the Systems Group, Realtrac offers the option to provide a specialized “GL Account” mode specific to the Sage and Realtrac integration. With this mode active, users are able to define the specific GL Accounts that each invoice and purchase order line item will be assigned to when the resulting invoices and purchase orders are posted to Sage. Before we discuss the usage of this mode, let’s first highlight some limitations and requirements that occur when running in this mode:

  1. Realtrac users must manually enter the GL Account code for every invoice and/or purchase order line in Realtrac, before attempting to post the invoice and/or purchase order to Sage. In other words, there are no default accounts, so this work must be manually done on every Realtrac invoice or PO line before the posting operation.
  2. This mode also enables Sage Job level cost and income tracking. This means that for invoices and/or purchase order lines that are associated with a job, that specific job must exist in Sage. In other words, if you create a Realtrac purchase order, buying some material for Realtrac Job 18J1234, then there must be a Sage Job, numbered 18J1234 created in Sage before posting the Realtrac purchase order to Sage. Similarly, if a user creates a Realtrac invoice in which they are shipping parts out that were created in Realtrac Job 18J2345, then a Sage Job numbered 18J2345 must exist in Sage before posting the invoice to Sage. If Sage does not have a matching job based on the Realtrac PO/Invoice Job Numbers, the post will be rejected.
  3. Sage Purchase Orders will be created with no item. Realtrac will fill out the PO item description, quantities and costs, but no item will be attached to the Sage PO item line.

Due to these limitations (and deviations from how our accounting integrations typically work), it’s generally not recommended to run Realtrac in Sage GL Account mode. Only organizations that need precise, item by item and line by line control over their GL codes, and are willing to do the manual labor required on an line by line and item by item basis show venture in to the Sage GL Account mode.

Enabling the Sage GL Account mode is straightforward. Visit the System Settings tab in the System Group, and enable the option on the screen. Users can disable this mode in the future if they decide to return to a normal Sage / Realtrac integration. Let’s take a look at a specific purchase order example. First, let’s take a look at a couple of items on a Realtrac Purchase Order.








Figures 1 and 2 above show 2 lines on a Realtrac Purchase Order. We’ve highlighted 3 key pieces of information: The Item Names, the assigned Job Number and the GL Codes associated with these 2 purchases. Since we are in GL Mode, the GL Code field is required to be filled in with a valid Sage GL Account code. If there is no code present, Realtrac will abort the attempt to post the PO to Sage. Since these purchases are for a job, that job must be present in Sage prior to the attempt to post the purchase order over to Sage. Let’s post this PO over to Sage, and check on the results. 






We’ve highlighted the same 3 fields in Figure 3 above. What we note:
  1. When running Sage GL Account mode, the resulting PO will never have any items.
  2. The GL Account codes match between the Realtrac PO (Figure 1) and the Sage PO (Figure 3). If the GL Accounts didn’t exist in Sage, the PO would be rejected.
  3. The Sage Job number matches the Realtrac Job number.

If a user attempts to post a Realtrac Purchase Order in which one or more of the lines are missing a GL Code, the attempt to post will fail and the Options button in the upper right corner of the PO interface will illuminate green. Selecting the Options button and choosing System Messages, the user will see an error message similar to: 






For the most part, the Realtrac invoicing function works similar to the PO function, with one small difference. When posting Realtrac Invoices to Sage, the resulting Sage invoice will always use the generic Sage “REALTRAC” item (in other words, the item on the Sage invoice is the same whether the user is running in Sage GL Account mode or normal Sage / Realtrac integration mode). So similar to running in regular Sage integration mode, users in Sage GL Account mode still must create the stock REALTRAC item in the Sage master Item List. See Creating Invoice Item in Sage 50 section for more details. Other than that, the same limitations and expectations exist in regards posting a purchase order to Sage in GL Account mode exist when posting an invoice:

  1. Every Realtrac invoice line must have a GL code. If one or more lines are missing GL codes, or have GL codes that do not exist in Sage, the invoice will be rejected and an error shown.
  2. If there is a job associated with the invoiced item, that exact job number must exist in Sage prior to the post. The screenshot below shows a Realtrac Invoice in the upper half, and a Sage Invoice in the lower half.






Of note in the figure above  
  1. The part on the Realtrac invoice was associated with Job 18J2443, so the resulting Sage Invoice is also associated with that same job.
  1. The item on the Sage invoice is the generic REALTRAC invoice item. Not shown in figure 5 is the Journal entry, which notes that the Sage invoice item is associated with GL account #4000, which you can see on the far right of the Realtrac invoice on top. If a user attempts to post a Realtrac invoice that has one or more rows without GL Codes, the post will fail and the Options button will illuminate green. Selecting System Messages will show an error similar to:




Similarly, if the user attempts to post a Realtrac invoice where one or more of the invoice line’s GL Codes is incorrect (no GL account associated with that ID in Sage), the System Messages will show an error similar to the following:






Impact on the Sage Jobs
Since the PO’s and Invoices that Realtrac automatically creates in Sage are tied to Sage Jobs, the postings of these documents has an impact on the job. Invoices that are posted will be immediately recognized on the Sage Job. Purchase Orders are only recognized by Sage Jobs when the PO items are received in Sage.





The figure above shows a Job General Ledger report in Sage. We note there are 2 transactions on the report, one for an invoice that has been posted in association with the job, and another for a PO Item that has been received and is associated with the job. PO items that have not yet been received will not appear in the job’s ledger until the PO item is received.