How to assign random values to each row in SQL for Acumatica

Hello everybody,

recently during testing of some logic for me it was necessary to assign some random values to table VendorPart in Acumatica. In order to achieve it, I've created such a script:

update VendorPart set NetPrice = (ABS(CHECKSUM(NEWID()))% 3000.25 + 1.1 + 1), ListPrice= (ABS(CHECKSUM(NEWID()))% 3000.25 + 1.1 + 1)
	where NetPrice = 0 and ListPrice = 0

of course such a query will not satisfy all requirements for random numbers generator, but in scope of my business scenario it was better then assign the same value to both columns.

Summary

If you want to have some diversity in digits for your values, use NEWID function for assigning values.

Import data in Acumatica

To import data (Customers for instance) from .xlsx format we need to make 3 actions:
           

  • Create Data Provider

  • Create an Import Scenario

  • Make an Import by Scenario

1: First of all, before we will start do all those steps, we need to have .xlsx file with customers and such fields at least on the first row as a heading row:

 

After that when we’ve checked Customers.xlsx file, click on the menu button and choose Integration tab.

Afterwards on the Profiles subheading click Data Providers

In the *Name field type the name of the future Data Provider to/from Excel format (Import/Export Customers to Excel) and in the next field  - * Provider Type: choose Excel Provider. After that go to Files in the top right corner and upload your Customers.xlsx file to the system:

After you have done, the screen has to be like this:

Go to SCHEMA part and make the following actions as you can see on the image:

On the form toolbar, click Save. See the screenshot of the created scenario.

  1. Menu-> Integration-> Import Scenarios

Fill this stage as shown on the image or with needed fields for you:

To fill Screen Name with the right choice will help this image:

 And click Save icon in the top left corner.

After this step we need to make the last one – to make an import by created already scenario:

  1. Menu-> Integration-> Import by Scenario

Go to DETAILS part and fill SITE MAP zone as shown below:

In the Name field choose Import New Customers and press PREPARE button on the top. It have to be close to this image: