FRx - Rebuild GL Index Option - The why and the how

Anybody who has worked with FRx for more than a day, probably knows about the Rebuild GL Index. If you have a problem with an FRx report - if it does not show accurate data, one of the first things you do is to rebuild the GL Index. Recently I was asked these questions about the Rebuild GL Index option -

Question 1 - Why do we have to rebuild the GL Index ? Can't FRx do this on its own ?

When Automatic GL Index Rebuild is disabled, the GL index files may become outdated. It is important to rebuild these files when information is updated in the general ledger. This move forces FRx to build the GL index by pulling all the accounts and subaccounts with posted information.
You can rebuild an index for the default company. To do this, follow these steps:
a.     Click Admin, and then click Build Local GL Index.

Question 2 - Can't you set it up so I don't have to do it manually ?

Yes, In Report Designer -

1.    Click Admin, and then click System Preferences.
2.    Verify that Automatic GL Index Rebuild is selected, and then click OK.
3.    Click Company, and then click Information.
4.    Select a company that is listed in the window. Click Set as Default, and then log on. Repeat this step for the next company that is listed in the window until you have repeated this step for all companies.

If you are using Analytical Accounting -

Go to Company | Information | System Specific Information for each company. Verify that the "Rebuild Chart of Accounts?" option is checked and then log in.

Question 3 - I turned on Rebuild Chart of Accounts as you suggested, but now it takes very long to login to the company.

You can setup a SQL Job to Rebuild the Chart of Accounts, which runs every night. This would ensure your performance is not affected and also that your GL Index is up to date.

The exact query would vary depend on your FRx companies setup, but here's a sample query when you are using AA and have one company.

delete from frl_acct_code_aa where entity_num = '1'
delete from frl_seg_desc where entity_num = '1'
exec csp_PopulateAccountCodeTable 1
exec csp_UpdateAccountCodeTable 1
exec csp_PopulateSegDesc 1

If you have two AA Companies the query would be like this.

delete from frl_acct_code_aa where entity_num = '1'
delete from frl_seg_desc where entity_num = '1'
exec csp_PopulateAccountCodeTable 1
exec csp_UpdateAccountCodeTable 1
exec csp_PopulateSegDesc 1
delete from frl_acct_code_aa where entity_num = '2'
delete from frl_seg_desc where entity_num = '2'
exec csp_PopulateAccountCodeTable 2
exec csp_UpdateAccountCodeTable 2
exec csp_PopulateSegDesc 2

Feel free to ask if you have any questions.