Sumindexes SQL

Dennis_Decoene

When researching the use of Sumindexes in SQL, i found that they are stored in a table with a name like Company$Tableno$KeyNo. When you look at such a table, you find that the columns in that table corespond to the fieldnumbers as defined in the key in navision. What I cannot figure out, is what the field "Bucket" is... Anyone have a clue?

It would be wise to persue this. If we can figure out exactly how the sumindexes are stored, we can probably use them in applications making use of the navision data.

Happy seeking... <img border="0" title="" alt="" src="images/smiles/icon_smile.gif" />

---

Dennis Decoene
Navision Software Engineer

MicroComputing NV
Nijverheidsweg 1
9820 Merelbeke
Belgium

Tel: +32 (0)9/231.26.88
Fax: +32 (0)9/231.77.57
http://www.microcomputing.be

John_Tegelaar

The "bucket" holds the number of filters to apply. The SumIndex is retrieved through a SQL stored procedure, which takes a number of parameters. Which parameters to pass can be derived (when you've got the grip on these) by analyzing the expression of the flowfield in the designer. Or you switch on SQL tracing (eh, Performance Optimizer? It's under the tools menu of Enterprise Manager) and call up the form holding the flowfields you want to use. In the trace log you can see how Navision is doing the job.

John

Dennis_Decoene

I want to thank you for responding to my question. You helped me on the way and i found out a lot allready on the flowfield enigma. Nonetheless, I still cannot figure out when to use which bucket. I looked at setting different filters and seeing what the results where when i did a trace but it still is very obscure. I had one flowfilter set and the bucket was 4.
Could you help me otu please? My time is running out...

John_Tegelaar

Dennis,

The field being sumindexed can be used by more than one flowfield. Therefore you can't analyze by just set a single filter, as this doesn't change the definition of the flowfield.

Look at the definition of the flowfield. You will find the sourcetable (pad this with $n, where n is the sequencenumber of the sumindex of the sourcetable) and the parameters used for setting the sumindex conditions. It's the number of parameters that makes up the bucket number of filters. You need to pass a value for each filter (blanks if empty) to give the stored procedure the proper number of parameters. If you switch on SQL tracing, and call up a form containing the flowfield you want to use (make a temp form with only that field, to limit the amount of activity to monitor), you will be able to see the actual query for getting the value for that field. Copy the query and insert it in the Query Analyzer (? not sure about the name, but also under Tools in SQL Enterprise Manager) to play with values. Run the query and compare to the results you got from Navision. When everything is under control, you can copy the query to your program.

John

Dennis_Decoene

What you describe here is exactly what I did allready. The way you explain the bucket field, I would seem that the value never changes, which is not the case. Depending on the folwfilters set, the bucket number changes in the SQL Query Analyzer. Yet I cannot see the logic behind which bucket is used when.
I'm doing my tests on the inventory field of the item table.

The calcformula is as folows:

Sum("Item Ledger Entry".Quantity WHERE (Item No.=FIELD(No.),Department Code=FIELD(Department Filter),Project Code=FIELD(Project Filter),Location Code=FIELD(Location Filter),Drop Shipment=FIELD(Drop Shipment Filter),Variant Code=FIELD(Variant Filter),Bin Code=FIELD(Bin Filter)))

As you can see, there are 7 filters defined. Yet when you look at all the rows in the SQL table company$32$0, you see there are 8 buckets. Now, when I set the location filter, the bucket in the analyzer is 4. When i set only the bin filter, it changes to 1. WHere is the logic in that????

Anyway, I'm going to do more tests when I find the time...

BTW, if you read this Marcus, my signature is updated <img border="0" title="" alt="" src="images/smiles/icon_wink.gif" />

---

Dennis Decoene
Navision Software Engineer
http://www.microcomputing.be