different between calcfields and calcsums

nikeman77

hi all,

i am quite puzzled about the difference between calcsums & calcfields since both calculate total at run time. the only one i know is calcfield required field to be of flowfield, while calsums required field to be in sumindexfield but since they perform (i presume) almost the same thing (calculate values quicker instead of looping through to programatically calculate them.

correct me if i am wrong, i do know that sumindexfield uses sift technology to maintain a virtual table so that whenever a calcsums is used the value are populated quickly.

DenSter

nikeman77 wrote:

correct me if i am wrong, i do know that sumindexfield uses sift technology to maintain a virtual table so that whenever a calcsums is used the value are populated quickly.

No that's not true. SIFT is implemented on indexed views. It used to be in actual tables, before 5.0 SP1 came out.

CALCFIELDS is used to calculate flowfields
CALCSUM is used to calculate totals directly form the table that has the sumindexfield

David_Singleton

nikeman77 wrote:

i am quite puzzled about the difference between calcsums & calcfields since both calculate total at run time.

You seem to be confused as to what a FlowField is and what SumIndex field is. A flow field uses a sumindex. They are two completely different things. If you are working on the FlowField then you use calcfields. If you are working with the SumIndex field then you use calcsums.

My suggestion is you create two tables, one as the table with a flowfield that uses a second table with a sumindex field as its source, and learn the different concepts. this is core technology in Navision that you need to learn.

nikeman77

hi david,

thanks, i would like to experiment it with little of your help.
"a second table with a sumindex field as its source"

second table will have a sum index field?
or under first table flowfield>sumindex column place second table value?

pardon me i dont get what you mean, but i do agreed that without grasping solid understanding of what are this 2, I would not fully be learning its features.

David_Singleton

What have you tried so far? Did you get an error message?
Have you tried anything? Look at existing low fields in Navision (quantity on hand in item table for example) see how it is done and try to duplicate something like that.

Mike_HWG

I personally like to learn by example. Let's look at one:

Design the Value Entry Table.
Open the properties of the Sales Amount (Expected) field. Notice it has a FieldClass of Normal.
Click View > Keys. Notice that Sales Amount (Expected) is listed as one of the SumIndexFields

Design the Item Ledger Entry Table.
Open the properties of the Sales Amount (Expected) field. Notice it has a FieldClass of FlowField.
Click the AssistEdit button for the Calculation Formula. Notice that we are utilizing "Value Entry"."Sales Amount (Expected)"

We can see how Flowfields that contain numerical data are related to SumIndex fields.

However, since these fields are different, they require different functions for calculation.
Calcsums for SumIndex fields
Calcfields for FlowFields

nikeman77

hi mike,

few questions i would like to clarify:
1. in item ledger entry table demostrating calcsums (where secondary key:Item Ledger Entry No.,Document No.), so what navision does is to sort/index the record (and keep it virtual)by Item Ledger Entry No.,Entry Type and keep the sum of following field-
Invoiced Quantity,Sales Amount (Expected), Sales Amount (Actual),
Cost Amount (Expected), Cost Amount (Actual),
Cost Amount (Non-Invtbl.), Cost Amount (Expected) (ACY),
Cost Amount (Actual) (ACY), Cost Amount (Non-Invtbl.)(ACY),
Purchase Amount (Actual), Purchase Amount (Expected)

So when we do a set filter>> Item Ledger Entry No.,Entry Type & do a
calcsums("Cost Amount(Actual)", "Cost Amount (Expected)"), we are actually retrieving data from this virtual table?


2.besides that, how is the example that was provided link item ledger entry to value entry? do we need to set them up in the flowfield properties for this 2 tables to link them together so that we can use the calsums in c/al?

nikeman77

David Singleton wrote:

What have you tried so far? Did you get an error message?
Have you tried anything? Look at existing low fields in Navision (quantity on hand in item table for example) see how it is done and try to duplicate something like that.

hi david,

i got an error while i tried to do a renaming of table to test on your suggestion

nikeman77

David Singleton wrote:

What have you tried so far? Did you get an error message?
Have you tried anything? Look at existing low fields in Navision (quantity on hand in item table for example) see how it is done and try to duplicate something like that.

hi david,
"Look at existing low fields in Navision " - hows your low fields like / means?

anyway i tried your suggestion create 2 table
table a:master table :salesman, field1:salescode(code10), field2amount(decimal,flowfield,calculate base on salescode in ile2 table where ile2.salesman code=salesman.salescode)
table b:transactions table:ile2, field1:entry number(integer), salesman code(code10), amount(decimal) set primary key entry number, secondary key salesman code, sumindex field=amount.

conclusion:
basically if you want to have indirect access of salesman table.amount in c/al, u need to set filter by salesman you want, do a salesman.calcsums(amount)?
However if you want to do a direct access of salesperson sales amount in ILE, then we need to create ile.amount as flowfield and within c/al code do a ile2.calcfield(amount)?

i hope you can get what i am trying to say

David_Singleton

My suggestion was to create new tables, no where did I say to rename existing ones.

I you want to learn this, look at the standard code and then try to do the same. But start form an empty sheet so that you learn every step.

David_Singleton

nikeman77 wrote:

David Singleton wrote:

What have you tried so far? Did you get an error message?
Have you tried anything? Look at existing low fields in Navision (quantity on hand in item table for example) see how it is done and try to duplicate something like that.

hi david,
"Look at existing low fields in Navision " - hows your low fields like / means?

anyway i tried your suggestion create 2 table
table a:master table :salesman, field1:salescode(code10), field2amount(decimal,flowfield,calculate base on salescode in ile2 table where ile2.salesman code=salesman.salescode)
table b:transactions table:ile2, field1:entry number(integer), salesman code(code10), amount(decimal) set primary key entry number, secondary key salesman code, sumindex field=amount.

conclusion:
basically if you want to have indirect access of salesman table.amount in c/al, u need to set filter by salesman you want, do a salesman.calcsums(amount)?
However if you want to do a direct access of salesperson sales amount in ILE, then we need to create ile.amount as flowfield and within c/al code do a ile2.calcfield(amount)?

i hope you can get what i am trying to say

=D> =D> =D> =D>

Yes this is good, you are learning it now. Still paly with it more, the next thing to try out is a FlowFilter. Add a date field to table 2 and a flowfilter type date to table one, then add this into your flowfield and figure out how to make it work.

Also I if you have an older version of Navision like 4.00 repeat it on there (just import the objects) and look at how Navision creates the SIFT tables.

nikeman77

David Singleton wrote:

nikeman77 wrote:

David Singleton wrote:

What have you tried so far? Did you get an error message?
Have you tried anything? Look at existing low fields in Navision (quantity on hand in item table for example) see how it is done and try to duplicate something like that.

hi david,
"Look at existing low fields in Navision " - hows your low fields like / means?

anyway i tried your suggestion create 2 table
table a:master table :salesman, field1:salescode(code10), field2amount(decimal,flowfield,calculate base on salescode in ile2 table where ile2.salesman code=salesman.salescode)
table b:transactions table:ile2, field1:entry number(integer), salesman code(code10), amount(decimal) set primary key entry number, secondary key salesman code, sumindex field=amount.

conclusion:
basically if you want to have indirect access of salesman table.amount in c/al, u need to set filter by salesman you want, do a salesman.calcsums(amount)?
However if you want to do a direct access of salesperson sales amount in ILE, then we need to create ile.amount as flowfield and within c/al code do a ile2.calcfield(amount)?

i hope you can get what i am trying to say

=D> =D> =D> =D>

Yes this is good, you are learning it now. Still paly with it more, the next thing to try out is a FlowFilter. Add a date field to table 2 and a flowfilter type date to table one, then add this into your flowfield and figure out how to make it work.

Also I if you have an older version of Navision like 4.00 repeat it on there (just import the objects) and look at how Navision creates the SIFT tables.

hi david,

-thanks for the affirmation, will try out flowfilter, basically with the foundation in flowfield, the other 2 just built on top of it.
-Most of the time its the master table that consists of flow field while flowfilter and sumindexfield in the transactions table.
-Navision knows that as transactions table get bigger in size, it will takes a longer time to get summary of certain value (eg: quantity, amount etc etc) thus a SumIndexField & FlowFilter was 'born'
-Of course we want to have a run time 'speed' performance by creating more secondary keys, but memory wise it would be a compromising factor?

I did encounter one issue while testing this issue: which occured while i tried to re-numbered the table object, you may have came across them before? :-k
(i paste a screenshots of the error in the previous post)

David_Singleton

nikeman77 wrote:

hi david,

-thanks for the affirmation, will try out flowfilter, basically with the foundation in flowfield, the other 2 just built on top of it.
-Most of the time its the master table that consists of flow field while flowfilter and sumindexfield in the transactions table.
-Navision knows that as transactions table get bigger in size, it will takes a longer time to get summary of certain value (eg: quantity, amount etc etc) thus a SumIndexField & FlowFilter was 'born'
-Of course we want to have a run time 'speed' performance by creating more secondary keys, but memory wise it would be a compromising factor?

I did encounter one issue while testing this issue: which occured while i tried to re-numbered the table object, you may have came across them before? :-k
(i paste a screenshots of the error in the previous post)

Ignore the rename issue, that is not really important right now. Keep your focus on what you are doing.

You still have more things to learn.

The primary key of your second tabla should be integer, but if its not, create a new table3 with new fields and a new flowfield in table 1 and start to look at how navision sorts. You do that by creating a form that is linked to the detail table and then use drill down. You will need to add different keys, and you can learn how navision selects which key it does.

nikeman77

David Singleton wrote:

nikeman77 wrote:

hi david,

-thanks for the affirmation, will try out flowfilter, basically with the foundation in flowfield, the other 2 just built on top of it.
-Most of the time its the master table that consists of flow field while flowfilter and sumindexfield in the transactions table.
-Navision knows that as transactions table get bigger in size, it will takes a longer time to get summary of certain value (eg: quantity, amount etc etc) thus a SumIndexField & FlowFilter was 'born'
-Of course we want to have a run time 'speed' performance by creating more secondary keys, but memory wise it would be a compromising factor?

I did encounter one issue while testing this issue: which occured while i tried to re-numbered the table object, you may have came across them before? :-k
(i paste a screenshots of the error in the previous post)

Ignore the rename issue, that is not really important right now. Keep your focus on what you are doing.

You still have more things to learn.

The primary key of your second tabla should be integer, but if its not, create a new table3 with new fields and a new flowfield in table 1 and start to look at how navision sorts. You do that by creating a form that is linked to the detail table and then use drill down. You will need to add different keys, and you can learn how navision selects which key it does.

hi david,

yup i did create ile2.entry number(integer), basically i try to mimic item ledger entry~ile.

nikeman77

David Singleton wrote:

nikeman77 wrote:

David Singleton wrote:

What have you tried so far? Did you get an error message?
Have you tried anything? Look at existing low fields in Navision (quantity on hand in item table for example) see how it is done and try to duplicate something like that.

hi david,
"Look at existing low fields in Navision " - hows your low fields like / means?

anyway i tried your suggestion create 2 table
table a:master table :salesman, field1:salescode(code10), field2amount(decimal,flowfield,calculate base on salescode in ile2 table where ile2.salesman code=salesman.salescode)
table b:transactions table:ile2, field1:entry number(integer), salesman code(code10), amount(decimal) set primary key entry number, secondary key salesman code, sumindex field=amount.

conclusion:
basically if you want to have indirect access of salesman table.amount in c/al, u need to set filter by salesman you want, do a salesman.calcsums(amount)?
However if you want to do a direct access of salesperson sales amount in ILE, then we need to create ile.amount as flowfield and within c/al code do a ile2.calcfield(amount)?

i hope you can get what i am trying to say

=D> =D> =D> =D>

the next thing to try out is a FlowFilter. Add a date field to table 2 and a flowfilter type date to table one, then add this into your flowfield and figure out how to make it work.

Also I if you have an older version of Navision like 4.00 repeat it on there (just import the objects) and look at how Navision creates the SIFT tables.

1.Master Table-salesman
1a.change field amount: flowfield to include date=datefilter, location=locationfilter

2.Transaction Table-ile2
2a.add 2 field: DateFilter & LocationFilter (flowclass=flowfilter)
2b.add secondary key1: EntryNo+DateFilter
2c.add secondary key2: EntryNo+LocationFilter

3.PROBLEM, when i open the salesman card, under amount, i cant drill down! ](*,)

Mike_HWG

nikeman77 wrote:

1.Master Table-salesman
1a.change field amount: flowfield to include date=datefilter, location=locationfilter

2.Transaction Table-ile2
2a.add 2 field: DateFilter & LocationFilter (flowclass=flowfilter)
2b.add secondary key1: EntryNo+DateFilter
2c.add secondary key2: EntryNo+LocationFilter

3.PROBLEM, when i open the salesman card, under amount, i cant drill down! ](*,)

You are trying to stack flowfields...

The salesman table needs fields for datefilter and locationfilter.
The ile2 table needs NORMAL fields for date and location.

Again, look at an example. Look at how the Item table interacts with the Item Ledger Entry table.
Item table:
Date Filter
Location Filter

Item Ledger Entry table:
Posting Date
Location

This topic is spelled out very well in the Microsoft Courseware. There is a chapter on how to build tables that covers this!

David_Singleton

nikeman77 wrote:

3.PROBLEM, when i open the salesman card, under amount, i cant drill down! ](*,)

Most likely becasue you didn't create and assign a form to the source table. Take a look at table 32 go to table properties and look how form 38 is assigned.

nikeman77

David Singleton wrote:

nikeman77 wrote:

3.PROBLEM, when i open the salesman card, under amount, i cant drill down! ](*,)

Most likely becasue you didn't create and assign a form to the source table. Take a look at table 32 go to table properties and look how form 38 is assigned.

hi david,

how brillant!
first cause: i describe my newly created field to you guys, i did create flowfilter (dateFilter,LocationFilter) in master table salesman.

main root cause: i didn't create any drilldown form! :!:

now at the salesman card, i pull out datefilter, LocationFilter field when i entered a value (eg:date filter) the amount change, when i drill down the ile2 form was displayed (with filter of course!)

nikeman77

Mike_HWG wrote:

nikeman77 wrote:

1.Master Table-salesman
1a.change field amount: flowfield to include date=datefilter, location=locationfilter

2.Transaction Table-ile2
2a.add 2 field: DateFilter & LocationFilter (flowclass=flowfilter)
2b.add secondary key1: EntryNo+DateFilter
2c.add secondary key2: EntryNo+LocationFilter

3.PROBLEM, when i open the salesman card, under amount, i cant drill down! ](*,)

You are trying to stack flowfields...

The salesman table needs fields for datefilter and locationfilter.
The ile2 table needs NORMAL fields for date and location.

Again, look at an example. Look at how the Item table interacts with the Item Ledger Entry table.
Item table:
Date Filter
Location Filter

Item Ledger Entry table:
Posting Date
Location

This topic is spelled out very well in the Microsoft Courseware. There is a chapter on how to build tables that covers this!

hi Mike_HWG,

sorry i describe the issue wrongly, i did create : #-o
Master Table-Salesman
1.DateFilter (Date datatyper. FlowFilter)
2.LocationFilter (Code10, FlowFilter)