mibuso.com
Home› NAV/Navision Classic Client

Purchase Orders List - If Unit Cost zero use Std Cst field

emulsifiedemulsified Member Posts: 139
edited 2007-11-09 in NAV/Navision Classic Client
We have a report that we created to show us Purchase Orders totals and totals not received for each purchase order. Below is the code: 
OBJECT Report 50045 Purchase Orders List
{
  OBJECT-PROPERTIES
  {
    Date=10/11/07;
    Time=10:26:06 AM;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table39;
        ReqFilterFields=Document Type,Buy-from Vendor No.,Document No.,Type,No.;
        TotalFields=Amount,Outstanding Amount;
        GroupTotalFields=Document No.;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1000000001;Label  ;0    ;0    ;7500 ;423  ;FontSize=12;
                                                         FontBold=Yes;
                                                         CaptionML=[ENU=Purchase Orders List;
                                                                    ESM=L¡n. compra;
                                                                    FRC=Ligne achat;
                                                                    ENC=Purchase Line] }
            { 1000000002;TextBox;12150;0    ;6000 ;423  ;HorzAlign=Right;
                                                         FontSize=12;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1000000003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
            { 1000000004;TextBox;16950;423  ;1200 ;423  ;FontSize=12;
                                                         CaptionML=ENU=Page;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1000000005;Label  ;15750;423  ;1200 ;423  ;ParentControl=1000000004;
                                                         FontSize=12 }
            { 1000000006;TextBox;15450;846  ;2700 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         SourceExpr=USERID }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1000000009;Label  ;0    ;0    ;1800 ;846  ;ParentControl=1000000008;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000012;Label  ;2250 ;0    ;1800 ;846  ;ParentControl=1000000011;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000015;Label  ;4350 ;0    ;1800 ;846  ;ParentControl=1000000014;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000018;Label  ;12900;0    ;2250 ;846  ;ParentControl=1000000017;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Order Amount }
            { 1000000021;Label  ;15450;0    ;2250 ;846  ;ParentControl=1000000020;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000024;Label  ;6600 ;0    ;1800 ;846  ;VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Order Date }
            { 1000000027;Label  ;8850 ;0    ;1950 ;846  ;VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Last Posted Date }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT(FALSE);
                         END;

          }
          CONTROLS
          {
            { 1000000008;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Document Type" }
            { 1000000011;TextBox;2250 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Buy-from Vendor No." }
            { 1000000014;TextBox;4350 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Document No." }
            { 1000000017;TextBox;12900;0    ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr=Amount }
            { 1000000020;TextBox;15450;0    ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr="Outstanding Amount" }
            { 1000000026;TextBox;6600 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Order Date" }
            { 1000000028;TextBox;8850 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Posting Date" }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000000;TextBox;15450;0    ;2250 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         SourceExpr="Outstanding Amount" }
            { 1000000007;TextBox;12900;0    ;2250 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         SourceExpr=Amount }
            { 1000000010;TextBox;4350 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Document No." }
            { 1000000013;TextBox;2250 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Buy-from Vendor No." }
            { 1000000016;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Document Type" }
            { 1000000025;TextBox;6600 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Order Date" }
            { 1000000029;TextBox;8850 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Posting Date" }
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000019;TextBox;15450;0    ;2250 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr="Outstanding Amount" }
            { 1000000022;TextBox;12900;0    ;2250 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=Amount }
            { 1000000023;Label  ;7050 ;0    ;5700 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         LeaderDots=Yes;
                                                         CaptionML=ENU=Report Totals }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {

    BEGIN
    END.
  }
}

The problem is sometimes we place Purchase Orders for items that do not yet have a final "Unit Cost" so we have the item created at 0.00 Unit Cost and the purchase order was made with the item "unit cost" as 0.00.

We want this report to:

1st - Check to see if a "Unit Cost" exists if it does not, is 0.00 then

2nd - Check the "Last Direct Cost" or "Standard Cost" fields and use one of these to calculate everything for a particular item

I've checked CU's to see where the "Unit Cost" gets picked up at and don't really want to modify the CU's, but rather the report because it seems to be the most logical way.

Any suggestions, ideas or help?
Half-empy or half-full how do you view your database?

Thanks.

Comments

  • ara3nara3n Member Posts: 9,256 
    OBJECT Report 50045 Purchase Orders List
{
  OBJECT-PROPERTIES
  {
    Date=11/07/07;
    Time=[ 7:49:32 AM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table39;
        OnAfterGetRecord=BEGIN
                           IF Item.GET("No.") AND (Amount = 0) THEN BEGIN
                             Amount := "Quantity (Base)" * Item."Last Direct Cost";
                             "Outstanding Amount" := "Quantity (Base)" * Item."Last Direct Cost";
                             IF Amount = 0 THEN BEGIN
                               Amount := "Quantity (Base)" * Item."Standard Cost";
                               "Outstanding Amount" := "Quantity (Base)" * Item."Standard Cost";
                             END;
                           END;
                         END;

        ReqFilterFields=Document Type,Buy-from Vendor No.,Document No.,Type,No.;
        TotalFields=Amount,Outstanding Amount;
        GroupTotalFields=Document No.;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1000000001;Label  ;0    ;0    ;7500 ;423  ;FontSize=12;
                                                         FontBold=Yes;
                                                         CaptionML=[ENU=Purchase Orders List;
                                                                    ESM=L¡n. compra;
                                                                    FRC=Ligne achat;
                                                                    ENC=Purchase Line] }
            { 1000000002;TextBox;12150;0    ;6000 ;423  ;HorzAlign=Right;
                                                         FontSize=12;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1000000003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
            { 1000000004;TextBox;16950;423  ;1200 ;423  ;FontSize=12;
                                                         CaptionML=ENU=Page;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1000000005;Label  ;15750;423  ;1200 ;423  ;ParentControl=1000000004;
                                                         FontSize=12 }
            { 1000000006;TextBox;15450;846  ;2700 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         SourceExpr=USERID }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1000000009;Label  ;0    ;0    ;1800 ;846  ;ParentControl=1000000008;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000012;Label  ;2250 ;0    ;1800 ;846  ;ParentControl=1000000011;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000015;Label  ;4350 ;0    ;1800 ;846  ;ParentControl=1000000014;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000018;Label  ;12900;0    ;2250 ;846  ;ParentControl=1000000017;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Order Amount }
            { 1000000021;Label  ;15450;0    ;2250 ;846  ;ParentControl=1000000020;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000024;Label  ;6600 ;0    ;1800 ;846  ;VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Order Date }
            { 1000000027;Label  ;8850 ;0    ;1950 ;846  ;VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Last Posted Date }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT(FALSE);
                         END;

          }
          CONTROLS
          {
            { 1000000008;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Document Type" }
            { 1000000011;TextBox;2250 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Buy-from Vendor No." }
            { 1000000014;TextBox;4350 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Document No." }
            { 1000000017;TextBox;12900;0    ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr=Amount }
            { 1000000020;TextBox;15450;0    ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr="Outstanding Amount" }
            { 1000000026;TextBox;6600 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Order Date" }
            { 1000000028;TextBox;8850 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Posting date" }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000000;TextBox;15450;0    ;2250 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         SourceExpr="Outstanding Amount" }
            { 1000000007;TextBox;12900;0    ;2250 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         SourceExpr=Amount }
            { 1000000010;TextBox;4350 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Document No." }
            { 1000000013;TextBox;2250 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Buy-from Vendor No." }
            { 1000000016;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Document Type" }
            { 1000000025;TextBox;6600 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Order Date" }
            { 1000000029;TextBox;8850 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         SourceExpr="Posting date" }
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000019;TextBox;15450;0    ;2250 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr="Outstanding Amount" }
            { 1000000022;TextBox;12900;0    ;2250 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=Amount }
            { 1000000023;Label  ;7050 ;0    ;5700 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         LeaderDots=Yes;
                                                         CaptionML=ENU=Report Totals }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      Item@1000000000 : Record 27;

    BEGIN
    END.
  }
}

    Try this.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • emulsifiedemulsified Member Posts: 139
    Thank you, worked excellently. :D:D:D
    Half-empy or half-full how do you view your database?

    Thanks.
  • ara3nara3n Member Posts: 9,256
    you are welcome.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
Sign In or Register to comment.