Combining two SUMs in Flowfield question

gadzilla1

Hello all,

I'd like to create the following and want to adhere to the rules. Is the following possible in a table flowfield? If not, any suggestions? If so does anyone have any syntax tips?

This is what I want to accomplish in the Customer table:

Sum("Sales Invoice Line"."Amount" WHERE (Sell-to Customer No.=FIELD(No.))) + Sum("Sales Cr.Memo Line"."Amount" WHERE (Sell-to Customer No.=FIELD(No.)))

Please don't yell, newbie here! Thanks - gad1

diptish.naskar

Use calcfields before you perform the sum on the flowflields.

Mbad

Its not possible in one flowfield.

David_Singleton

gadzilla1 wrote:

Hello all,

I'd like to create the following and want to adhere to the rules. Is the following possible in a table flowfield? If not, any suggestions? If so does anyone have any syntax tips?

This is what I want to accomplish in the Customer table:

Sum("Sales Invoice Line"."Amount" WHERE (Sell-to Customer No.=FIELD(No.))) + Sum("Sales Cr.Memo Line"."Amount" WHERE (Sell-to Customer No.=FIELD(No.)))

Please don't yell, newbie here! Thanks - gad1

Hi Gadzilla,

you should not be doing sums like this on Posted Document tables. You should be looking at calculating this from the Customer Detail Ledger table.

Savatage

how about going to the customer card & hitting F9 (Customer Statistics)

It will show you that customers totals there.

gadzilla1

Thanks to all for the input.

David,

There currently is a FlowField in the Customer table with the following CalcFormula:

Sum("Cust. Ledger Entry"."Sales (LCY)" WHERE (Customer No.=FIELD(No.),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Posting Date=FIELD(Date Filter),Currency Code=FIELD(Currency Filter)))

I do not need to add two Sums together any longer - that's past. What I'd like to do is add another FlowField to the Customer table as follows:

Sum("Res. Ledger Entry"."Total Price" WHERE (Source No.=FIELD(No.),Posting Date=FIELD(Date Filter)))

So that's not advisable? If not, that's fine. It just makes me concerned since I see a handful of existing 'Sum' FlowFields in the Customer table, ones that I did not add. Suggestions?

Thanks!

David_Singleton

gadzilla1 wrote:

Thanks to all for the input.

David,

There currently is a FlowField in the Customer table with the following CalcFormula:

Sum("Cust. Ledger Entry"."Sales (LCY)" WHERE (Customer No.=FIELD(No.),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Posting Date=FIELD(Date Filter),Currency Code=FIELD(Currency Filter)))

I do not need to add two Sums together any longer - that's past. What I'd like to do is add another FlowField to the Customer table as follows:

Sum("Res. Ledger Entry"."Total Price" WHERE (Source No.=FIELD(No.),Posting Date=FIELD(Date Filter)))

So that's not advisable? If not, that's fine. It just makes me concerned since I see a handful of existing 'Sum' FlowFields in the Customer table, ones that I did not add. Suggestions?

Thanks!

Actually Res Led Entry is fine.

Basically we all learn in Navision 101 that you only create Navision flow fields on Ledger Entry Tables. There are many reasons for this, but primarily:

You never delete entry tables, posted document tables have no importance in Navision what so ever (eg 110,thru 115 and 120 thru 125) you can delete them as you want, so you don't want to report on them since they may just be deleted.

Entry tables are based on a simple PK, Integer. because the key is always sequential, you only ever insert at the end of the row, so performance is optimized. Also Navision adds the primary key to every secondary key so an integer (4 bytes) means that adding more keys to the table is not as expensive as if you sorted and created sifts on a table with a PK like say Option, Code20, integer, which means each key adds maybe 32 bytes to every record, add 10 keys to a table with 3 million records, and that is is 1 Gig of extra database size just through laziness.

Next the entry tables are designed to be simple and don't carry redundant information like name address post code etc. Adding all that baggage to an already loaded index is just asking for performance problems.

And of course the issue you just saw, i.e. you have to go to multiple tables to get data that should all be in one table.

So of course you can create flow fields on other tables, but just know what you are doing, and the performance impact it will have.


By the way great to see that you have your solution now. \:D/

Miklos_Hollender

"Basically we all learn in Navision 101 that you only create Navision flow fields on Ledger Entry Tables."

Yeah. And then they (MS) happily went about and added tons of fields like Qty. On Sales Order :evil:

(Though I think that might have originally been Lanham - but there is something hilarious about that we have to make a difference between best practice and standard practice)

gadzilla1

Thanks to all!

This reply is to David Singleton.

David,

Here's what I did:


-Created the key 'Source No.,Posting Date' in table 203 Res. Ledger Entry

-Added the SumIndexField 'Total Price' to that key

-Added a decimal field, 'Resource' to the Customer table

-The Resource is a FlowField

-The CalcFormula is -Sum("Res. Ledger Entry"."Total Price" WHERE (Source No.=FIELD(No.),Posting Date=FIELD(Date Filter)))


That IS a preceding negative sign in my CalcFormula. Does all of this look ok? It runs just fine in my test environment.

Ps. Where do i get a copy of Navision 101 =P~

Thanks and have a great day! gad1

David_Singleton

That's basically right, just don't put that field on a list form and you should be fine.

gadzilla1

David,

Just for future reference, why not on a list form? I believe that's where the user wanted this field.

I could persuade him to include this on a report possibly.

Thank you!

gad1