[AL] How to use user input from request page for report generation

DanHint
DanHint Member Posts: 3
edited 2021-08-31 in NAV Three Tier
Hey all,
I am completely new at AL programming and am doing a little testing in my business central sandbox.
On my report's request page I would like to add a field in which I can choose a customer and then only show data for the chosen customer in the report.
I have successfully added the field but fail to properly configure the DataItemTableView, so the data passed to the report layout is pre-filtered.

This is the field I added ("ChosenCust_Code" is the variable I want to store my user-chosen value in): 
field(ChosenCust; ChosenCust_Code)
                    {
                        Caption = 'Customer to notify:';
                        ToolTip = 'Specifies the customer to create the overdue notice for.';
                        ApplicationArea = All;
                        TableRelation = Customer."No.";
                    }

In my data item, I tried to use the following line to filter the data based on the user-chosen value: 
DataItemTableView = where("No." = const(ChosenCust_Code));
"No." is the field in my data item table (PK) which should align with the ChosenCust_Code variable.
I have searched the web for half an hour now and figured I might be faster just asking here.

Best Answer

  • DanHint
    DanHint Member Posts: 3
    Answer ✓
    I solved it this way:
    I created a trigger OnPreDataItem() in my Customer DataItem and inside the trigger I wrote 
    if ChosenCust_Code <> '' then Cust.SetRange("No.", ChosenCust_Code);

Answers

  • DenSter
    DenSter Member Posts: 8,307
    If it's a field in the table then the user can simply set a filter on the field. You can use the RequestFilterFields property to define which fields are shown in the filter pane, and the user can then add more fields.

    No need for a complicated variable trick.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • DenSter
    DenSter Member Posts: 8,307
    By the way, since it's a foreign key field, shouldn't you call it "Customer No."?
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • DenSter
    DenSter Member Posts: 8,307
    More than 20 hours of development training, ALL for FREE!!!
    https://www.youtube.com/embed/videoseries?list=PL1FESh9FqyhQxkZXYYp1X1tTpXf3-9iSR
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • DanHint
    DanHint Member Posts: 3
    edited 2021-08-27
    Thanks for your answer.
    I just tried getting into variables and thus wanted to try this example.
    Also if I was a user it would save me two clicks and some scrolling or searching.
    So is there any way to properly pass user input to a query ("query" not meant in AL-syntax)?

    The DataItemTableView is for the customer-table, so "No." is the field name. Or what did you mean by the foreign key "customer no."?
  • DenSter
    DenSter Member Posts: 8,307
    I mentioned the RequestFilterFields property, which you can use to define fields that are shown in the filter pane by default. That'll save you two clicks, and a bunch of programming to filter on the field.

    The way you explained it, I thought you had a table with a link to the customer number, so that's why I suggested the name.

    You can create a field on the request page and set a variable as the source. Each control has triggers for OnValidate and OnLookup. You can program those to display the customer list and catch the record that the user selects. Then you can use the variable as the filter value in the dataitem trigger.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • DanHint
    DanHint Member Posts: 3
    Answer ✓
    I solved it this way:
    I created a trigger OnPreDataItem() in my Customer DataItem and inside the trigger I wrote 
    if ChosenCust_Code <> '' then Cust.SetRange("No.", ChosenCust_Code);

Categories