Rebuild SIFT Tables

megawavez

Hi,

Couldn't find anything using a search - is there anyway to force the system to rebuild all the SQL SIFT Tables? I can remove, then add the SIFT indexes, but this is time consuming.

We are looking at clearing out our old company and starting again. I want to delete all history (ledger entries, posted pos, etc.) in SQL (truncate). I need to system to rebuild all the SIFT tables after the information has been cleared.

Thanks,

Mega

kine

If you truncate some table, you need to truncate the SIFT tables for this table (have the ID of the table as number in the name of the table). Because if there are no records in the table, there are no records in the SIFT table too... ;-)

megawavez

kine wrote:

If you truncate some table, you need to truncate the SIFT tables for this table (have the ID of the table as number in the name of the table). Because if there are no records in the table, there are no records in the SIFT table too... ;-)

That partially helps - is there anyway to force the system to rebuilt the contents of the table?

Thanks.

kine

Disable and enable SIFT or Nav native Backup/Restore procedure.

Waldo

Well ... there is ... but it involves some coding.

You can play around with the virtual table "Key".
If you just test it out by:
1) create a new tabular form with a wizard on the "Key" table
2) Preview that form.
3) uncheck the checkbox (MaintainSIFTIndex) on the G/L Entry Table on one of the keys that contains a SumIndexFields
4) save by leaving the record
You'll see you won't have the sift tables anymore.

So, you could create some kind of functionality (report, codeunit, ...) to save the current value, switch off the MaintainSIFTIndex, save the record (COMMIT?), and afterwards put it back on. This doesn't take long at all.. .

Watch out for this though:
- the date and time of the object is changed as well. you can solve this by updating the object table while updating the key table
- You can't save the SIFT levels through the key table. May be there's another virtual tables I don't know of ... . So I think (didn't test this, sorry) all levels will be activated afterwards.

Hope this helps. May be I'll work out something later on my blog... . Looks interesting.

Waldo

:oops:
I just noticed that you're posting in the "Financials" forum, so i suppose the "Key" table is nog available yet... . Sorry.

megawavez

Waldo wrote:

:oops:
I just noticed that you're posting in the "Financials" forum, so i suppose the "Key" table is nog available yet... . Sorry.

We're using the 3.7 executables, but only the 2.6 database.

garak

you must disable all SIFTS and store the the table.
After this you must enable your needed sifts (are there buckets in these version :?: ) and now your FlowField work

kriki

[Topic moved from Navision Financials forum to SQL General forum]

Waldo

As addition ... see my blogpostfor more information on the "Key"-table... .
8)