TIPS & TRICKS

Acumatica Tips and Tricks - How to import transaction details on the fly without an Import Scenario

By Laura Jaffe

Acumatica provides tools that make it very easy to upload data from a variety of data sources. One common method to upload data is using Import Scenarios. Another method, and the subject of today’s blog, is the Load Records from File button which allows an end user to interactively upload details records from a flat file (in either CSV or Excel format.) In this blog I will discuss some tips and tricks for using this feature. Also, read through to the end to see how, through the power and magic of the Acumatica xRP Platform, you can embed this very same toolbar widget on your own custom forms in two easy steps with only ONE line of code.

Tips to use Load Records from File on existing Acumatica screens

The Load Records from File option is generally used to upload details on a transaction screen. It is available anywhere you see the upload icon on the toolbar. ​

Picture

 
Here’s a summary of some of the key similarities and differences between Import Scenarios and Load Records from File.

Picture

 
End users generally have no difficulties using the Load Records from File functionality without training. Users simply click the icon and browse to choose an xlsx or csv file. After choosing a file, a dialog box allows the user to specify an import mode.

Picture

 
  • Update Existing: Updates the rows that are already present in the table and adds new rows that are not present in the table.
  • Bypass Existing: Imports only new rows that were not present in the table. The existing rows will not be updated.
  • Insert All Records: Adds all rows from the file to the table. If you select this option, you may get duplicated rows because the system won’t check for duplicates.

Next Acumatica attempts to match the column headers from the source file with the properties from the Acumatica data grid. If the names match exactly, they will be auto matched. Otherwise, the user can create the appropriate mapping using the dropdowns.

Picture

 
​HINT: A convenient way to create an Excel template with the matching column headings is to start with an Excel export from the same data grid.
​
Click OK, and the data is uploaded to the screen. Be sure to save the transaction, same as you would if the data was keyed in manually.

Picture

 
These are some of the Acumatica screens that support Upload from a File:

Picture

 

And these are just a few of the many use cases we have seen for this functionality

  • GL transactions from an external payroll provider
  • Sales order details based on a file provided by the customer
  • Inventory starting balances or adjustments
  • GL Account starting balances
  • Labor transactions from an external time clock system

How to add Load Records From File on a custom screen
A benefit of the Acumatica xRP platform, is that organizations can extend the platform and leverage the very same framework used by the Acumatica product developers. To incorporate the Load Records from File widget on a custom screen there are two only two steps required in the Acumatica customization project:

  1. Add the [PXImport] attribute on the data view. In this example we have a customer DAC named PSDeptTimeEntry, and we have decorated the DeptTime view with the [PXImport] attribute.

Picture

 
​2. Enable Allow Upload for the data grid on the form.

Picture

 
And here is the resulting form with the Load Records from File capability displayed on the toolbar:

Picture

 
Happy uploading! For more Acumatica tips and tricks ​check out the Parallel Solutions blog!
 

Exact Globe – Tips & Tricks General Ledger Budgets

By Michele Shanower

Tested on Rev419

Exact Business Software allows users to create an unlimited number of budget scenarios based on various forecasts for long or short term periods.  Using the Exact Globe Excel Add-In, it’s easy to create new scenarios and adjust existing ones.
Below are the steps on how to upload a Budget into Globe from an Excel Spreadsheet.

IMPORTANT Tips to remember are marked in Red below.

  • Open Excel and create a new worksheet.
  • On the Add-ins Tab, click Exact Globe Next, Reports (or the magic wand icon)

Picture

 
  • On the Open Company screen, select a company, and click OK.
  • On the Reports screen in the Generate field, select Budget.
  • Select a budget Source:  Scenario (To use a budget scenario, you must have one defined in Globe.) If you selected a Scenario, enter a Budget scenario. 

Picture

 
Actuals                
   If you want to create a new budget based on a previous year. 
   You must enter BOTH years (this year and last) for Financial Year

Picture

 
  • Enter the criteria for what part of the budget you want to work on right now.

Please be aware, if the spreadsheet is closed it will lose the update link back to Globe so you may want to work on sections at a time.

Picture

 
  •  Click OK. The Excel Add-in displays the report
  •  Enter the budget

Please be aware, if the spreadsheet is closed it will lose the update link back to Globe.It is important that you upload all the information at one time without saving the spreadsheet.

​​

Picture

 
  • To write the budget back to Globe, On the Add-ins Tab, click Exact Globe Next,, Update
  • Select the Scenario to update OR if creating a new one enter the information on the screen and it will create the Scenario for you.

Note the year you update must be included in the range of years used to create the Excel Budget in step 6 aboveor you will get a message that states it cannot update the data.

Picture

 
  • To View or CHANGE repeat above.  

Each time you click the update button it will OVERWRITE the budget scenario in Globe with the data from the Excel Spreadsheet.

  • You can find the review budget scenarios in Exact Globe under System/Finance/Budgets