SIFT table for data extraction

CobaltSS

Hi,

Our Sales Force uses a tool outside of Navision to manage their accounts (SalesLogix, if anyone cares). Currently, we extract unit sold/return information out of Navision each night, which in turn is updated into their contact database.

I've been asked to also extract the Aged Accounts Receivable data for each customer. I realise this might be almost undoable due to the processing requirements of calculating the Aged Balances for every customer. Both platforms run on SQL, and our NAV database is 3.7.

So, I was looking at the $21$0 table in SQL and wondering if it might be possible to use that instead of the Detailed Cust Ledger Entry. But there is a bunch of data in there I don't know anything about (i.e. what's the bucket field for, etc.).

So I have two questions. Obviously the first is has anyone done this before, and if so, has it worked well? The second question is documentation related; like is there any for these tables?

cheers,

DenSter

the tables that are formatted like CompanyName$Tablenumber$Indexnumber are the SIFT tables, they are used to keep track of SIFT buckets. I would not retrieve data directly from those tables, but figure out how to get the data from the NAV core tables.

Something to consider also: those SIFT tables have been abandoned in NAV 5.0 SP1, so if you ever do an executable upgrade to that version, your solution would stop working right away.

CobaltSS

Thanks Daniel for the quick reply.

I was unaware of the NAV 5 issue. Seems like I need to read a little more. BTW, I found a copy of the ADG and there is a GREAT explanation of SIFT on SQL (chapter 24).

I'd still like to know if anyone has used the SIFT tables as a source for data extraction before. The more I read, the more I think that it would be an ideal source for the kind of information I need to pull out of NAV. I can't think that recalculating the data in a series of SQL queries is the best way to do this. And the Detailed Cust Ledger Entry table is too big to copy and work on outside of NAV.

cheers,

ara3n

Here is a thread on the subject and an example


http://www.mibuso.com/forum/viewtopic.p ... ect+++sift




You need to look at the keys in nav sumindex field and do the comparison.


To Make sure that after an upgrade to 5.0 sp1 it still works.

I would create a view that is called the same as the view would 5.0 sp1. That way no change needs to be done on your code.

CobaltSS

Thanks Rashed,

I think will help me a great deal.

OT: but...... It's amazing how the search on this forum finds what I type and not what I mean.... :shock: #-o

ara3n

I have hard time with my clients as well, trying to figure out what they mean.

My trick is to ask them the same question multiple times worded differently.

kine

CobaltSS wrote:

Thanks Rashed,

I think will help me a great deal.

OT: but...... It's amazing how the search on this forum finds what I type and not what I mean.... :shock: #-o

Problem is, that you are typing something else than you mean... if you type what you mean, the search will find it... 8)