CalcFormula Help Please!!

jjkst47

I am pretty new to CalcFormula's in Navision. Could somebody please help me break down what the following CalcFormula means. It seems like a varied form of SQL:

Sum("Posted Whse. Shipment Line"."Qty. (Base)" 
WHERE (Location Code=FIELD(Code),Assigned User ID=FIELD(User Filter),Shipment Date=FIELD(Date Filter),Truck Number=FIELD(Truck Filter),Item No.=FIELD(Item Filter),Contract Site Code=FIELD(Contract Site Code Filter),Type of Order=FIELD(Type of Order Filter),BOM Version=FIELD(BOM Version Filter),MVR Version=FIELD(MVR Version Filter),Incident No.=FIELD(Incident No. Filter)))

Thanks Much!!!!!

DenSter

Sum("Posted Whse. Shipment Line"."Qty. (Base)"
WHERE (Location Code=FIELD(Code),Assigned User ID=FIELD(User Filter),Shipment Date=FIELD(Date Filter),Truck Number=FIELD(Truck Filter),Item No.=FIELD(Item Filter),Contract Site Code=FIELD(Contract Site Code Filter),Type of Order=FIELD(Type of Order Filter),BOM Version=FIELD(BOM Version Filter),MVR Version=FIELD(MVR Version Filter),Incident No.=FIELD(Incident No. Filter)))

This CalcFormula filters the "Posted Whse. Shipment Line" table where the specified fields equal the values of the corresponding fields in the table where you got this definition from, and then it totals the "Qty. (Base)" field to display in the flowfield that this calcfields definition belongs to.

Savatage

post erased - Denster was too fast! His had more detail than mine.

DenSter

woohoo \:D/

Hey you beat me to it a number of times

jjkst47

I understand the Sum part. Not sure about the WHERE part:

WHERE (Location Code=FIELD(Code)

What table does 'Location Code' come from and what does 'FIELD(Code)' mean???

Thanks so much. Sorry for the beginner questions![/quote]

DenSter

jjkst47 wrote:

I understand the Sum part. Not sure about the WHERE part:

WHERE (Location Code=FIELD(Code)

What table does 'Location Code' come from and what does 'FIELD(Code)' mean???

Thanks so much. Sorry for the beginner questions!

Don't worry about the type of questions, this whole forum is meant to answer them anyway.

the first field (in this case the 'Location Code') comes from the table that you're summing in (which is the "Posted Whse. Shipment Line" table remember). The second one is always presented as FIELD(<fieldname>) in case the calcformula is of the type 'field', and in this case refers to the 'Code' field of the table in which you found this formula.

Go into the table designer, go to the properties pages of that field, and in the CalcFormula property, click the ellipsis to see the calcformula editor. In there you'll find another ellipsis in the Table Filter box that you can look at the field level detail of the calcformula.

hth

jjkst47

VERY, VERY Helpful!!!

It's a bit difficult picking Navision up due to the lack of documentation out there. Thanks again.

Savatage

jjkst47 wrote:

VERY, VERY Helpful!!!

It's a bit difficult picking Navision up due to the lack of documentation out there. Thanks again.

On each Navision product CD, you find a Doc directory containing following
pdf-files:
- Application Designer's Guide
- Installation & System Management: Microsoft® Business Solutions–Navision®
Application Server
- Making Database Backups in Microsoft® Business Solutions–Navision®
- C/FRONT Reference Guide
- Automated Data Capture Systems for Microsoft® Business Solutions–Navision®
- Installation & System Management: Microsoft® Business Solutions–Navision®
Database Server
- Installation & System Management: Microsoft® Business Solutions–Navision®
SQL Server Option
- Installation & System Management: Microsoft® Business Solutions–Navision®
Commerce Gateway 4.00
- Microsoft® Business Solutions–Navision® ODBC Driver 4.0 Guide
- Terminology Handbook

and some Word documents:
- Business Notification Installation Guide
- Smart Tag Installation Guide 400

Hope this helps.

Luc Van Dyck
webmaster mibuso.com

& I have some other docs here: http://www.geocities.com/navision_attain/downloads.html

DenSter

Also, I recommend you take the developer classes, or at least buy the training material from Microsoft (it's not cheap). It'll give you priceless insight into Navision development in a relatively short time, and it can get you certified too.