Occasionally upon migrating an existing BV database into Spire, you will encounter an error with duplicate GL accounts in the Pervasive Data.


The solution is to use PG Admin to edit the migrated data in Postgres and then to finish upgrading the database using the Spire tray.


Both the gl_chart_of_accounts table and gl_history_accounts tables may be affected.


Steps are as follows:


If you went to import an existing company into Spire, and received the following message.




The company data has been successfully created in Postgres, but is not available as a live company in Spire yet.




You will see the company name in italics to indicate it has not been successfully upgraded as yet.


Your next step is to open the database in pgAdmin III, the Postgres Editor that comes with your installation of Spire.


Look under your start menu or in Programs to launch.




Once launched, you should see your Postgres database. Double click on it to log in.




You will be prompted to enter your Postgres Admin password you created when you set up Spire Server.




Once connected to the main database, you will be able to see a list of further databases, representing the different companies you may have. Click on the one you just imported to view further. The path is COMPANY/Schemas/Public/Tables




Once you have opened Tables you can navigate to the needed GL ones.




Right click on the first one, gl_chart_of_accounts, and select Scripts then Create Script.




From the next window you want to copy from this article and paste into the script window the following script:


SELECT division
    ,acct_no
    ,chart_currency
    ,translation
    ,count(*)
FROM gl_chart_of_accounts
GROUP BY division
    ,acct_no
    ,chart_currency
    ,translation
HAVING count(*) > 1;





Leave the script highlighted so only that portion executes and hit the execute button (Green triangle) as outlined above.




You will see the results of the query - a list of accounts by division that have duplicate entries. Make a note of these (if the company had multiple Divisions and duplicates in each they would show here too)


(You may close the query window after making note of the duplicate account numbers.)


Next, you will run a different script, to find entries in the next table, gl_history_accounts.


Right click on that second table, and select Scripts, and Create Script as you did before.



This time, use the following script:


This time, use the following script:


SELECT year_end
    ,division
    ,acct_no
    ,chhst_currency
    ,translation
    ,count(*)
FROM gl_history_accounts
GROUP BY year_end
    ,division
    ,acct_no
    ,chhst_currency
    ,translation
HAVING count(*) > 1;
Remember to leave the script highlighted and hit the execute button. The results will display.

Make a note of these account numbers and the year they occur in - you will see the same numbers have duplicates in two different years.

You may close the query window.

Now, back to the Tables list - right click on the first one (gl_chart_of_accounts) and select View Data and View All Rows.

 

 

You will now see a display of all the data from that table. Click on the Sort button.

Modify the Sort actions to include the Account Number and delete the ID then hit OK to sort (you could include Division here too if you had a multiple division company).

Once you have the data sorted you can look for the account numbers you noted earlier.

It is normal for there to be the same account numbers in different currencies, but not duplicates in the same currency. 

For 46000LM above there are three accounts listed for Division 000 - 1 US and 2 CDN = one of the CDN is a duplicate. 

Your next step is to highlight the line of one of the possible duplicates (click in the Row number on the left) and navigate all the way to the right checking the line for entries. Generally, one of the duplicates will have entries and one will not - you want to delete the one that has all zeroes. 

In the rare instance of them both having entries, grab your calculator and transfer the values over from one to another before deleting one.

Once you have deleted the duplicate row move on to the next Account no. and continue until you have found and deleted all duplicates.

Next, you want to repeat the above sequence on the second Table, gl_history_accounts. Sort differently though, as per the example below.

 

 

Then you will get results with Year and Account displayed. Each Year can have it's own Account, but not duplicates within a Year, and the currency rule applies still too. 

Below you can see 4 entries under 2012 for 21330 - 2 US and 2 CDN. One of the CDN and one of the USD are duplicates.

Do as you did above - highlight lines then follow them to the right to the end of the data to ensure they are all zeroes. Delete the duplicate rows.

After you have finished deleting all the Duplicates you may upgrade the database in the Spire Server monitor. Select the company name in Italics.

Then click on File and Upgrade database.

Once complete the company name will display normally in the Spire Server list.

Congratulations! you have successfully imported a company and fixed the duplicate GL account errors!

You may now log in normally using Spire Desktop.