Calculate and Display a Total for a variable field

johnsogj

Hello, I am building a report from the Posted Package and Posted Package Line tables. I am calculating "Total lines per package" and "Total Pieces per package". These are obviously variable fields since they are not stored in either table. Now, I want to be able calculate "total lines for all packages for a given ship to" and "Total pieces for all packages for a given ship to". The ship to number is not stored in the Posted Package Line table. I tried to use a groupfooter section, but I'm assuming that that doesnt work since the fields to be summed are variable fields.

here is the code I have so far:

PostedPkg.RESET;

first I look in the posted pkgs table to find the total number of packages for a given posting date
PostedPkg.SETCURRENTKEY("Posting Date");
PostedPkg.SETRANGE("Posting Date", "Posting Date");
TotalPkgsDay := PostedPkg.COUNT;

then I find out how many packages were shipped on that date to various ship to customers
PostedPkg.SETCURRENTKEY("Posting Date", "Ship-to No.");
PostedPkg.SETRANGE("Posting Date", "Posting Date");
PostedPkg.SETRANGE("Ship-to No.", "Ship-to No.");
TotalPkgs := PostedPkg.COUNT;

then, for each of those packages, I go look in the posted package line table to find out how many lines were in each of those packages and how many pieces were in each package
PostedPkgLine.SETCURRENTKEY("Package No.", "Line No.");
PostedPkgLine.SETRANGE ("Package No.","Posted Package"."No.");
TotalLinesPkg := PostedPkgLine.COUNT;
PostedPkgLine.CALCSUMS(Quantity);
TotalPiecesPkg := PostedPkgLine.Quantity;

Now, I want to be able calculate "total lines for all packages for a given ship to" and "Total pieces for all packages for a given ship to".

DaveT

Hi,

You could write code to loop the posted package table and work out the totals from the posted package lines tables but have you considered adding flowfields to the posted package table for the quantity and lines?

johnsogj

I've never added flowfields to a table so I dont know how. is it very complicated? can you explain it?

DaveT

Hi,

I'm sure there's loads of posts and you will find a description in the application developer guide but here a quick summary

Add a field to the table
Go to the properties and change the FieldClass to Flowfield
Enter the CalcFormula - use the assist edit

Have a look on the customer table at "No. of Invoices" and Balance for examples.

kine

johnsogj wrote:

Hello, I am building a report from the Posted Package and Posted Package Line tables. I am calculating "Total lines per package" and "Total Pieces per package". These are obviously variable fields since they are not stored in either table. Now, I want to be able calculate "total lines for all packages for a given ship to" and "Total pieces for all packages for a given ship to". The ship to number is not stored in the Posted Package Line table. I tried to use a groupfooter section, but I'm assuming that that doesnt work since the fields to be summed are variable fields.

here is the code I have so far:

PostedPkg.RESET;

first I look in the posted pkgs table to find the total number of packages for a given posting date
PostedPkg.SETCURRENTKEY("Posting Date");
PostedPkg.SETRANGE("Posting Date", "Posting Date");
TotalPkgsDay := PostedPkg.COUNT;

then I find out how many packages were shipped on that date to various ship to customers
PostedPkg.SETCURRENTKEY("Posting Date", "Ship-to No.");
PostedPkg.SETRANGE("Posting Date", "Posting Date");
PostedPkg.SETRANGE("Ship-to No.", "Ship-to No.");
TotalPkgs := PostedPkg.COUNT;

then, for each of those packages, I go look in the posted package line table to find out how many lines were in each of those packages and how many pieces were in each package
PostedPkgLine.SETCURRENTKEY("Package No.", "Line No.");
PostedPkgLine.SETRANGE ("Package No.","Posted Package"."No.");
TotalLinesPkg := PostedPkgLine.COUNT;
PostedPkgLine.CALCSUMS(Quantity);
TotalPiecesPkg := PostedPkgLine.Quantity;

Now, I want to be able calculate "total lines for all packages for a given ship to" and "Total pieces for all packages for a given ship to".

Look at help about CurrReport.CREATETOTALS(..). You can reate automatic totals for your variables through this... and if you want only count the lines, just set the variable to 1 and it will count the lines in appropriate groups automatically. ;-)

i4tost

Remember, you can use CurrReport.CREATETOTALS(..) for the same field in all levels means, if for example you write line in first table (Posted Package) CurrReport.CREATETOTALS("Posted Package Line".Quantity) and the same line in related table (Posted Package Line), so you will have calculations in both levels and you will be able to show totoals for groups...

johnsogj

Thanks! Using the CREATETOTALS function in the "PreDataItem" section worked. Now I can calculate the total pieces per package and the total lines per package and then display the total lines for all packages for a given ship to and display the total pieces for all packages for a given customer. I've displayed those totals in a footer section. Now I need to take that calculated total value (displayed in red below) and divide it by a value (Variable) in the normal body section (also displayed in red below). Basically, I need to divide the total lines in all packages for a given ship to by the number of packages for that ship to in order to get the avg lines per package for that ship to.

How do I take that calculated value (that is not defined by a variable) and use it as part of another calculation?

thanks!!!



Posted Package - OnPreDataItem()
CurrReport.CREATETOTALS(TotalPiecesPkg);
CurrReport.CREATETOTALS(TotalLinesPkg);

Posted Package - OnAfterGetRecord()
PostedPkg.RESET;

PostedPkg.SETCURRENTKEY("Posting Date");
PostedPkg.SETRANGE("Posting Date", "Posting Date");
TotalPkgsDay := PostedPkg.COUNT;

PostedPkg.SETCURRENTKEY("Posting Date", "Ship-to No.");
PostedPkg.SETRANGE("Posting Date", "Posting Date");
PostedPkg.SETRANGE("Ship-to No.", "Ship-to No.");
TotalPkgs := PostedPkg.COUNT;

PostedPkgLine.SETCURRENTKEY("Package No.", "Line No.");
PostedPkgLine.SETRANGE ("Package No.","Posted Package"."No.");
TotalLinesPkg := PostedPkgLine.COUNT;
PostedPkgLine.CALCSUMS(Quantity);
TotalPiecesPkg := PostedPkgLine.Quantity;