How to add Item Card info (COGS) to a Invoice Register

boku25

Tables:
DataItem Name
Currency <Currency>
Integer <Integer>
Sales Invoice Header <Sales Invoice Header>
Sales Cr.Memo Header <Sales Cr.Memo Header>

Variables
Items record Item

I am trying to insert a new field to Invoice Register Report:(COGS) and Gross Margin, but this information only exist in the Item Card?
How do I link them?

On The Sales Invoice Header
OnAfterGetRecord
Items.GET("No.");

Is there a better way to do this?
and what keys should I use?
Can someone help?

Newbee

kriki

in the Sales Invoice Header you won't find the item no. the "No." you find there is the invoice no.
If you want item info in the header, you have another problem : 1 header can contain more lines, so more than 1 item. If you only have 1 line in each header, you don't have this problem.

2 possibilities:
1) you have to print more lines per header:
create a new dataitem "Sales Invoice Line", connect it to the "Sales Invoice Header" (
properties :
DataItemIndent=1
DataItemLink=Document No.=FIELD(No.)
DataItemTableView=WHERE(Type=CONST(Item)) // this if you want only the lines of type item

"Sales Invoice Line".OnAfterGetRecord :
recItem.GET("No.");
With this you can use the fields of recItem in the Body-section of the "Sales Invoice Line"


2) you need to make some total:
"Sales Invoice Header".OnAfterGetRecord :
recSalesInvoiceLine.RESET;
recSalesInvoiceLine.SETCURRENTKEY("Document Type","Document No.","Line No.");
recSalesInvoiceLine.SETRANGE("Document Type","Sales Invoice Header"."Document Type");
recSalesInvoiceLine.SETRANGE("Document No.","Sales Invoice Header"."No.");
recSalesInvoiceLine.SETRANGE("Type",recSalesInvoiceLine.Type::Item);
IF recSalesInvoiceLine.FIND('-') THEN
REPEAT
recItem.GET(recSalesInvoiceLine."No.");

// here you can use the fields of recItem
UNTIL recSalesInvoiceLine.NEXT = 0;

boku25

Gracias!!! mi amigo

I have one more question I am trying to get the total for the new colums
I created Four new variables COGS, GrossM COGSTotal and GrossMTotal

COGS := 0;
GrossM := 0;
COGSTotal := 0;
GrossMTotal := 0;
ONAFTERGETRECORD
Items.SETRANGE("No.",SIL."No.");
SIL.SETRANGE(Type,SIL.Type::Item);
IF Items.FIND('-') THEN
COGS := Items."Unit Cost";
COGSTotal += COGS;

IF (COGS<>0) AND (Items."Unit Price"<>0) THEN
GrossM := COGS / Items."Unit Price";
GrossMTotal += GrossM;

I am trying to get the Total, but nothing shows up, I am I missing something?

DenSter

boku25 wrote:

Items.SETRANGE("No.",SIL."No.");
SIL.SETRANGE(Type,SIL.Type::Item);
IF Items.FIND('-') THEN
COGS := Items."Unit Cost";
COGSTotal += COGS;

I am trying to get the Total, but nothing shows up, I am I missing something?

Think this one through....
First, you do Items.SETRANGE with the No from the SIL, but you don't look for any SIL until the next statement. Of course you won't find anything. The idea is a good one it seems, but you have to find the SIL before you can use any field value. Also, it seems as though you want to do more than one line with the Item that you find. You will have to put a BEGIN and an END around the lines you want, so that would be this:

IF Items.FIND('-') THEN BEGIN
  COGS := Items."Unit Cost";
  COGSTotal += COGS;
END;

By the way... the Item table's primary key is the "No." field, so you're better off doing:

SIL.SETRANGE(Type,SIL.Type::Item);
IF SIL.FIND('-') THEN BEGIN
  REPEAT // you may find more than one SIL
    IF Item.GET(SIL."No.") THEN BEGIN
      // do your thing here
    END;
  UNTIL SIL.NEXT = 0;
END;

I don't know what you want to do, but you should get better results if you stick to the proper sequence of statements.

hth

boku25

Thank it worked.. I was close.. but your advise saved me thanks,
it runs fine..

Newbee

boku25

what I am trying to do, is to get all the Item's in the invoice 'unit cost'
so I am looking for the Total of the COGS for the invoice..
ex:

Invoice:
Item1 Unit Cost
Item2 Unit Cost
Total Unit Cost

for each Invoice

does anyone have any idea how to do this

Savatage

So you want to do something like the "Order Statistics"? "F9"?

You can see how it's done in the Sales Invoice Statisics below -See CostUSD Variable

this is how the sales statistics get you the totals on an invoice for:
Amount
Inv. Discount amount
Total
Tax Amount
Sales($)
Cost($)
Qty in pieces
etc etc.

Code
Form - OnAfterGetRecord()
  SETRANGE("No.");
  CLEARALL;
  
  IF "Currency Code" = '' THEN
    currency.InitRoundingPrecision
  ELSE
    currency.GET("Currency Code");
  
  SalesInvLine.SETRANGE("Document No.","No.");
  
  IF SalesInvLine.FIND('-') THEN
    REPEAT
      CustAmount := CustAmount + SalesInvLine.Amount;
      AmountInclTax := AmountInclTax + SalesInvLine."Amount Including Tax";
      IF "Prices Including Tax" THEN
        InvDiscAmount := InvDiscAmount + SalesInvLine."Inv. Discount Amount" / (1 + SalesInvLine."Tax %" / 100)
      ELSE
        InvDiscAmount := InvDiscAmount + SalesInvLine."Inv. Discount Amount";
      CostUSD := CostUSD + (SalesInvLine.Quantity * SalesInvLine."Unit Cost ($)");
      LineQty := LineQty + SalesInvLine.Quantity;
      TotalNetWeight := TotalNetWeight + (SalesInvLine.Quantity * SalesInvLine."Net Weight");
      TotalGrossWeight := TotalGrossWeight + (SalesInvLine.Quantity * SalesInvLine."Gross Weight");
      TotalVolume := TotalVolume + (SalesInvLine.Quantity * SalesInvLine."Unit Volume");
      IF SalesInvLine."Units per Parcel" > 0 THEN
        TotalParcels := TotalParcels + ROUND(SalesInvLine.Quantity / SalesInvLine."Units per Parcel",1,'>');
      IF SalesInvLine."Tax %" <> TaxPercentage THEN
        IF TaxPercentage = 0 THEN
          TaxPercentage := SalesInvLine."Tax %"
        ELSE
          TaxPercentage := -1;
    UNTIL SalesInvLine.NEXT = 0;
  TaxAmount := AmountInclTax - CustAmount;
  InvDiscAmount := ROUND(InvDiscAmount,currency."Amount Rounding Precision");
  
  IF TaxPercentage <= 0 THEN
    TaxAmountText := Text000
  ELSE
    TaxAmountText := STRSUBSTNO(Text001,TaxPercentage);
  
  IF "Currency Code" = '' THEN
    AmountUSD := CustAmount
  ELSE
    AmountUSD :=
      CurrExchRate.ExchangeAmtFCYToLCY(
        WORKDATE,"Currency Code",CustAmount,"Currency Factor");
  ProfitUSD := AmountUSD - CostUSD;
  IF AmountUSD <> 0 THEN
    ProfitPct := ROUND(100 * ProfitUSD / AmountUSD,0.1);
  
  IF Cust.GET("Bill-to Customer No.") THEN
    Cust.CALCFIELDS("Balance ($)")
  ELSE
    CLEAR(Cust);
  IF Cust."Credit Limit ($)" = 0 THEN
    CreditLimitUSDExpendedPct := 0
  ELSE
    CreditLimitUSDExpendedPct := ROUND(Cust."Balance ($)" / Cust."Credit Limit ($)" * 10000,1);
  
  SalesInvLine.CalcTaxAmountLines(Rec,TempTaxAmountLine);
  CurrForm.Subform.FORM.SetTempTaxAmountLine(TempTaxAmountLine);
  CurrForm.Subform.FORM.InitGlobals("Currency Code",FALSE,FALSE,FALSE,FALSE,"VAT Base Discount %");

If I'm way off sorry I read the post very quickly O:)

boku25

I forgot about the F9
Thanks I just needed to change the code around.. thanks for the help

It works fine

newbee

Savatage

\:D/