When you create the new remote datasets, they would have the point values showing in the head office dataset for those customers. But because points do not poll, those values will be incorrect.

In the original remote datasets

Create Design Customer / Supplier report Set : - sort by name - Customer Type to All - Customer / Supplier to Customer - Department to the location you are in

Save report as Points Export

Click on the columns tab. If there are any fields showing in the selected area, remove them and select the following columns: - Department - Name - State/Province - Account Number - Unique No. - Customer Points

Click on save button again

View the report

This will create the customer report so that we can export the points with the account numbers and uniques of the customer. Then we can temporarilly import the customer unique into the account number field, and still have a record of the original account numbers, which can be dataloaded back in after the points have been restored into the newly created datasets.

Sort the report by Customer Points column. This will save us a few steps when we process it in Excell.

Now that we have the report, we have to export it to excell.

The first thing we need to do is duplicate the Unique Column. It needs to be in the report twice so we can dataload the unique into the account number field based on the unique number.

Also we must eliminate all lines that do not have points. This way, only people's accounts that have a point ballance will have their account numbers changed.

The Unique Numbers and Points numbers are exported with a comma in the number denoting the thousands place, This also has to be removed prior to dataloading. This is easilly done by highlighting the column, right click and select Format Cells, click on Number, set the decimal places to zero, and make sure that the thousands separator is unchecked. And click on OK to apply the change. This MUST be done on both Unique columns, the points column, and just for good measure, the original account number.

We need the columns to be named the following:

Department Name State/Province Account Number Unique Number Customer Points None

If the columns are not named this way, change the names to match the above names. The import will not work unless columns are named exactly this way.

Save the spreadsheet as Original-Numbers.csv prefixed by the department name. eg. Sask-Original-Numbers.csv

Now re-edit this spreadsheet and re-name the columns:

The critical column name changes for the spreadsheet are: - name one of the unique columns 'Unique Number“ - name the other unique column 'Account Number” - name the original account column “Number”

  At this point you will also need to copy the existing Account Numbers to the new Account Number column.
To do this, you will want to sort the spreadsheet by the old account number column.
This will place all the accounts that already have account numbers at the top of the list.
Select all those existing numbers and overwrite the numbers in the new account number column. 

Save as a different name to preserve the original export. You will need that to re-import the original account numbers later. Name it Point-Load.csv and prepend it with the department name as well eg. Sask-Point-Load.csv

Do a customer / supplier load to load the new account numbers. You will need to open the Point-Load.csv To dataload the account numbers all we need are the two unique columns. set one as the unique and the other as account number. Set all other columns to None. No other settings need to be changed. Run the dataload. View and save the log.

Run polling and account numbers are polled to main dataset. This will need to be done from both remotes prior to the dataset re-creation. May need to run back and forth at least 2 to 3 times, as there was still Batch information polling back and forth.

Once this is done we can upgrade the main dataset, and re-create the remote datasets.

Once the remote datasets are created, we can dataload the points back into the new remote datasets based on the account number.

So we need to open the customer dataload in the remote datasets, and open the Point-Load.csv. In this case we only want to select the account number column, and the points column. All others should be set to none. Click Next Check “Update existing accounts by Account Number Click Next Run the dataload View the log - It should not contain any entries saying New. They should all be marked Update.

Repeat this on the remaining locations.