Pass a Parameter to a report that uses a different table

Savatage · 2008-10-30

I've been messing around with this but need some help.

Many times we want to return all items from a specific manufacturer. Instead on manually entering all the items I figured we can call a report to populate the line table. I can't seem to get the doc no & doc type from the form to my variables on the report.

I'm trying to develope a report that can be accessed from a purchase order (for now, eventually sales order, purch credit, etc). I want to insert items into the puchase line table.

I have made an export dataport & an import dataport of the item table which works fine but I was thinking one report that based upon some selections made in the request form that I could just insert these items without creating a file & importing a file.

So the plan is that if I access the report from the functions of a puchase order I need to pass:
the order Number :ex.123456
the document type:ex. "Credit Memo"

to some variables in my report vDocNo & VDocType

RESET;
PurchHeader.SETRANGE("Document Type","Document Type");
PurchHeader.SETRANGE("No.","No.");
REPORT.RUNMODAL(50061,TRUE,FALSE,PurchHeader);

send the type & no IF I use Puchase header as a dataitem - but I'm only using Item table.
the report will basically insert lines in the purchlin table

this is just real raw to get the idea accross!

purchline."Document Type" := VDocType --> Gotten by passed parameter from form
purchline."Document No." := vDocNo  --> Gotten by passed parameter from form
purchline."line no." := vLineNo + 10000;
purchline.type := 'Item';
purchline."no." := item."no.";
purchline.quantity := item.quantity;
purchline.insert;

if I add purchheader as a non linked dataitem - the fields are populated but a can't get them to the request form.
so to stess again when I call the eport from a specific purch order I would like the vDocNo variable on the request form to be filled. I can manulatt fill it - but that's not fun. and once I figure out how to get the doc no - it will allow me to also eliminate the doc type from having to choose it on the request form too.

kapamarou · 2008-10-30

I don't know if I'm getting this right, but if you declare a report as a variable and instead of calling
REPORT.RUNMODAL(50061,TRUE,FALSE,PurchHeader);

call
CLEAR(myReportVariable);
myReportVariable.SetParameters(ParamList);
myReportVariable.SETTABLEVIEW(...);
myReportVariable.RUNMODAL;

Then in SetParameters you can fill the variable you use on the request form.

Slawek_Guzek · 2008-10-30

Hi,

Do I understand correctly you want to have ONE report which can handle different tables ?

like:

Report.runmodal(XXX,false,false,PurchaseLine)
Report.runmodal(XXX,false,false,SalesLine)
Report.runmodal(XXX,false,false,SalesShimpentLine)

Try to make one report having all necessary table type variables on indent level 0, in every section on OnPreDataItem collect all requires parameters from (using GETFILTER("FIELD")), then the latest section might be based on Integer/whatever and do the all necessary job.
Or just process each table in its own section.

Parameters can be passed using filters.

Inside the code where you need it just run the report against different tables as in the exampe:

SalesLine.SETRANGE("Field1", value1);
SalesLine.SETRANGE("Field2", value2);
...
Report.runmodal(XXX,false,false,SalesLine)

and in another place

PurchaseLine.SETRANGE("Field1", value1);
PurchaseLine.SETRANGE("Field2", value2);
...
Report.runmodal(XXX,false,false,PurchaseLine)

(the same report called with two different tables)

Read this viewtopic.php?f=5&t=26550 - might be a better explanation of the idea

Regards,
Slawek

Savatage · 2008-10-30

It actually works like a charm - in it's raw form
but It still requires the user to enter the doc no and make selections from the request form.
I'm trying to help fill those line to kind of make it idiot proof
I'll try your suggestion - I was thinking getfilters too.

Edit**Corrected & Working Code

OBJECT Report 50061 Get Items For Insertion
{
  OBJECT-PROPERTIES
  {
    Date=10/31/03;
    Time=12:36:51 PM;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table27;
        DataItemTableView=WHERE(Blocked=FILTER(No));
        OnPreDataItem=BEGIN
                        vLineNo := 10000
                      END;

        OnAfterGetRecord=BEGIN
                           CALCFIELDS("Real QOH");

                           IF (vOrderType = vOrderType::"Purchase Order") AND (vDocumentType = vDocumentType::Order)
                           THEN PurchOrderLineImport;

                           IF (vOrderType = vOrderType::"Purchase Order") AND (vDocumentType = vDocumentType::"Credit Memo")
                           THEN PurchCreditLineImport;

                           IF (vOrderType = vOrderType::"Sales Order") AND (vDocumentType = vDocumentType::Order)
                           THEN SalesOrderLineImport;

                           IF (vOrderType = vOrderType::"Sales Order") AND (vDocumentType = vDocumentType::"Credit Memo")
                           THEN SalesCreditLineImport;

                           vLineNo := vLineNo + 10000;
                         END;

        ReqFilterFields=No.,No. 2,Description,Shelf/Bin No.,Gen. Prod. Posting Group,Blocked,Real QOH,Vendor No.;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=12000;
            SectionHeight=846;
          }
          CONTROLS
          {
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemTable=Table36;
        DataItemTableView=SORTING(Document Type,No.)
                          ORDER(Ascending);
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=12000;
            SectionHeight=846;
          }
          CONTROLS
          {
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemTable=Table38;
        DataItemTableView=SORTING(Document Type,No.)
                          ORDER(Ascending);
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=12000;
            SectionHeight=846;
          }
          CONTROLS
          {
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=16940;
      Height=9680;
    }
    CONTROLS
    {
      { 1000000000;OptionButton;1320;2090;3740;440;SourceExpr=vOrderType;
                                                   OptionValue=Sales Order }
      { 1000000001;OptionButton;1320;2640;3740;440;SourceExpr=vOrderType;
                                                   OptionValue=Purchase Order }
      { 1000000002;OptionButton;1320;3850;3740;440;SourceExpr=vDocumentType;
                                                   OptionValue=Order }
      { 1000000003;OptionButton;1320;4400;3740;440;SourceExpr=vDocumentType;
                                                   OptionValue=Credit Memo }
      { 1000000004;Label  ;770  ;1540 ;4290 ;440  ;CaptionML=ENU=Choose The Order Type }
      { 1000000005;Label  ;660  ;3300 ;4400 ;440  ;CaptionML=ENU=Choose The Document Type }
      { 1000000006;TextBox;1320 ;880  ;3740 ;440  ;SourceExpr=vDocumentNo }
      { 1000000007;Label  ;770  ;330  ;4290 ;440  ;CaptionML=ENU=Document No. }
      { 1000000008;Label  ;660  ;5060 ;4400 ;440  ;CaptionML=ENU=Choose The Quantity To Be Used }
      { 1000000009;OptionButton;1320;5610;3740;440;SourceExpr=vQty_Used;
                                                   OptionValue=Actual Quantity On Hand;
                                                   OnPush=BEGIN
                                                            CLEAR(vQty_Entered);
                                                          END;
                                                           }
      { 1000000010;OptionButton;1320;6160;3740;440;SourceExpr=vQty_Used;
                                                   OptionValue=User Defined Quantity }
      { 1000000011;TextBox;3300 ;6710 ;1700 ;440  ;Name=vQty_Entered;
                                                   Numeric=Yes;
                                                   SourceExpr=vQty_Entered;
                                                   MinValue=1;
                                                   OnValidate=BEGIN
                                                                IF (vQty_Entered <> 0) AND (vQty_Used <> vQty_Used::"User Defined Quantity")
                                                                THEN ERROR('You Must Select User Defined Quantity!');
                                                              END;
                                                               }
      { 1102606001;Label  ;1320 ;6710 ;1870 ;440  ;CaptionML=ENU=Enter Qty }
      { 1102606002;TextBox;6380 ;1540 ;3900 ;440  ;SourceExpr=vDocumentNo }
      { 1102606003;TextBox;6380 ;2640 ;4010 ;440  ;SourceExpr=vOrderType }
      { 1102606004;TextBox;6380 ;3630 ;4010 ;440  ;SourceExpr=vDocumentType }
    }
  }
  CODE
  {
    VAR
      vDocumentNo@1000000000 : Code[20];
      vOrderType@1000000001 : 'Sales Order,Purchase Order';
      vDocumentType@1000000002 : 'Quote,Order,Invoice,Credit Memo,Blanket Order,Return Order';
      vQty_Used@1000000003 : 'Actual Quantity On Hand,User Defined Quantity';
      vQty_Entered@1000000004 : Decimal;
      SalesLine@1102606000 : Record 37;
      PurchLine@1102606001 : Record 39;
      vLineNo@1102606002 : Integer;
      Purchheader@1102606003 : Record 38;
      salesheader@1102606004 : Record 36;

    PROCEDURE SalesOrderLineImport@1102606000();
    BEGIN
      IF salesheader.GET(1,vDocumentNo)
       THEN BEGIN
        SalesLine.VALIDATE("Document Type",1);
        SalesLine.VALIDATE("Document No.",vDocumentNo);
        SalesLine.VALIDATE("Line No.",vLineNo);
        SalesLine.VALIDATE(Type, SalesLine.Type::Item);
        SalesLine.VALIDATE("No.",Item."No.");
        IF vQty_Used = vQty_Used::"Actual Quantity On Hand"
         THEN SalesLine.VALIDATE("Qty. Ordered",Item."Real QOH")
         ELSE SalesLine.VALIDATE("Qty. Ordered",vQty_Entered);
        SalesLine.INSERT;
      END
       ELSE BEGIN
        ERROR('Order Type: %1\Document Type: %2\Document No: %3\\Does Not Exist',vOrderType,vDocumentType,vDocumentNo);
      END;
    END;

    PROCEDURE PurchOrderLineImport@1102606001();
    BEGIN
      IF Purchheader.GET(1,vDocumentNo)
       THEN BEGIN
        PurchLine.VALIDATE("Document Type",1);
        PurchLine.VALIDATE("Document No.",vDocumentNo);
        PurchLine.VALIDATE("Line No.",vLineNo);
        PurchLine.VALIDATE(Type, PurchLine.Type::Item);
        PurchLine.VALIDATE("No.",Item."No.");
        IF vQty_Used = vQty_Used::"Actual Quantity On Hand"
         THEN PurchLine.VALIDATE(Quantity,Item."Real QOH")
         ELSE PurchLine.VALIDATE(Quantity,vQty_Entered);
        PurchLine.INSERT;
      END
       ELSE BEGIN
        ERROR('Order Type: %1\Document Type: %2\Document No: %3\\Does Not Exist',vOrderType,vDocumentType,vDocumentNo);
      END;
    END;

    PROCEDURE SalesCreditLineImport@1102606002();
    BEGIN
      IF salesheader.GET(3,vDocumentNo)
       THEN BEGIN
        SalesLine.VALIDATE("Document Type",3);
        SalesLine.VALIDATE("Document No.",vDocumentNo);
        SalesLine.VALIDATE("Line No.",vLineNo);
        SalesLine.VALIDATE(Type, SalesLine.Type::Item);
        SalesLine.VALIDATE("No.",Item."No.");
        IF vQty_Used = vQty_Used::"Actual Quantity On Hand"
         THEN SalesLine.VALIDATE("Qty. Ordered",Item."Real QOH")
         ELSE SalesLine.VALIDATE("Qty. Ordered",vQty_Entered);
        SalesLine.INSERT;
      END
       ELSE BEGIN
        ERROR('Order Type: %1\Document Type: %2\Document No: %3\\Does Not Exist',vOrderType,vDocumentType,vDocumentNo);
      END;
    END;

    PROCEDURE PurchCreditLineImport@1102606003();
    BEGIN
      IF Purchheader.GET(3,vDocumentNo)
       THEN BEGIN
        PurchLine.VALIDATE("Document Type",3);
        PurchLine.VALIDATE("Document No.",vDocumentNo);
        PurchLine.VALIDATE("Line No.",vLineNo);
        PurchLine.VALIDATE(Type, PurchLine.Type::Item);
        PurchLine.VALIDATE("No.",Item."No.");
        IF vQty_Used = vQty_Used::"Actual Quantity On Hand"
         THEN PurchLine.VALIDATE(Quantity,Item."Real QOH")
         ELSE PurchLine.VALIDATE(Quantity,vQty_Entered);
        PurchLine.INSERT;
      END
       ELSE BEGIN
        ERROR('Order Type: %1\Document Type: %2\Document No: %3\\Does Not Exist',vOrderType,vDocumentType,vDocumentNo);
      END;
    END;

    PROCEDURE SetGlobalParameters@1102606004(DocSubType@1102606000 : Integer;DocType@1102606001 : Integer;DocNo@1102606002 : Code[20]);
    BEGIN
      vOrderType := DocSubType;
      vDocumentType := DocType;
      vDocumentNo := DocNo
    END;

    BEGIN
    END.
  }
}

David_Singleton · 2008-10-31

Normally when I do something like this, I start with report 292-Copy Sales Document, or 482-Copy Purchase Document, and work from there.

Savatage · 2008-10-31

I was thinking that too - but the difference is I want to be able to select any items I want from all available items & i'll select the items using filters. Todays a new day - I haven't gone back to it. Like I said it works if you fill in the request form. I'll let you know how it turns out

garak · 2008-10-31

Hi Harry,

hope i understand it correct (after 3 private moves in the last 2 weeks i'm very powerless) ...

in your Report you need a function like

SetGlobalParameters(DocSubType : Integer;DocType : Integer;DocNo : Code[20])
vOrderType := DocSubType;
vDocumentType := DocType;
vDocumentNo := DocNo;

on Sales Order or Purch Order you run the Report and call there before the new Function

clear(GetItemForIns);
GetItemForIns.SetGlobalParameters(1,"Document Type","No.");
GetItemForIns.RUNMODAL;

Now if you only need to filter some items, you can also send this Item Filter to your report.

Regards

Savatage · 2008-10-31

Brilliant - I guess you can teach an old dog new tricks!

Now I can create a Sales Order, Sales Credit, Puchase order or Puchase Credit header.
go to functions "Get Items" it calls the report based on the item table where I can Insert any items I want with a click.
I can also determine the Qty to be inserted either a User Defined Value or the Qty on hand.

For Sales orders on Function (button) I added "Get Items" Menu Item using code
Add global=> GetItemForIns - Type REPORT - 50061

Clear(GetItemForIns);
GetItemForIns.SetGlobalParameters(1,"Document Type","No.");
GetItemForIns.RUNMODAL;

For Puchase orders/Credits on Function (button) I added "Get Items" Menu Item using code
Add global=> GetItemForIns - Type REPORT - 50061

Clear(GetItemForIns);
GetItemForIns.SetGlobalParameters(0,"Document Type","No.");
GetItemForIns.RUNMODAL;

This will come in hand many times. If we need to pull 1 pc from a specific vendor from inventory for samples - Click DONE!

For our yearly merchandise show - create a picking ticket for vendor A B & C or specific items - click DONE!

If a vendors merch isn't selling and we want to create a return to the vendor - Click DONE!

I'm sure I'll find more & More Uses - thanks to all that helped!

Note: I edited the post above that had the reports code in case someone wanted to try it. The code is correct!

Howdy, Stranger!

Quick Links

Categories

Pass a Parameter to a report that uses a different table

Answers