mibuso.com
Home› NAV/Navision Classic Client

Request Form - Create Lookup Field with filter

r-pr-p Member Posts: 33
edited 2012-02-28 in NAV/Navision Classic Client
Hello

I'm on report 1404 (Bank Acc. - Detail Trial Bal.).
I'm trying to create a lookup field to with a filter, so when i run the report, in tab "options" the cliente can filter by user.

Someone can help build the lookup field, but most important how to make the lookpup field work like a filter, to filter by user.

Thanks.

Comments

  • vijay_gvijay_g Member Posts: 884
    Check report no. 1497 (Trans. Bank Rec. to Gen. Jnl.). How is the lookup working in option tab.
  • r-pr-p Member Posts: 33
    vijay_g wrote:
    Check report no. 1497 (Trans. Bank Rec. to Gen. Jnl.). How is the lookup working in option tab.

    does that report makes a filter, to get the report only with the information that you select on the lookup field?
  • r-pr-p Member Posts: 33
    r-p wrote:
    vijay_g wrote:
    Check report no. 1497 (Trans. Bank Rec. to Gen. Jnl.). How is the lookup working in option tab.

    does that report makes a filter, to get the report only with the information that you select on the lookup field?

    Is this the code that i have to adjust to make the filter that i want?

    GenJnlTemplate.GET(GenJnlLine."Journal Template Name");
    GenJnlBatch.GET(GenJnlLine."Journal Template Name",GenJnlLine."Journal Batch Name");
    GenJnlLine.SETRANGE("Journal Template Name",GenJnlBatch."Journal Template Name");
    IF GenJnlBatch.Name <> '' THEN
    GenJnlLine.SETRANGE("Journal Batch Name",GenJnlBatch.Name)
    ELSE
    GenJnlLine.SETRANGE("Journal Batch Name",'');
  • vijay_gvijay_g Member Posts: 884
    This code is only for lookup the text box in request form. For filteration you need to write seperate code on you dataitem. 
    //onpredataitem
SETRANGE(Fieldname,LookupValue);
  • r-pr-p Member Posts: 33
    vijay_g wrote:
    This code is only for lookup the text box in request form. For filteration you need to write seperate code on you dataitem. 
    //onpredataitem
SETRANGE(Fieldname,LookupValue);

    Thanks.

    One more question:

    can you say if this is correct -

    on Bank Account - OnPreDataItem()

    SETRANGE("User ID","Bank Account Ledger Entry"."User ID");
  • r-pr-p Member Posts: 33
    r-p wrote:
    vijay_g wrote:
    This code is only for lookup the text box in request form. For filteration you need to write seperate code on you dataitem. 
    //onpredataitem
SETRANGE(Fieldname,LookupValue);

    Thanks.

    One more question:

    can you say if this is correct -

    on Bank Account - OnPreDataItem()

    SETRANGE("User ID","Bank Account Ledger Entry"."User ID");

    Problem solved:

    The code is correct but i must be in

    Bank Account Ledger Entry - OnPreDataItem()

    Thanks for all the help.
  • vijay_gvijay_g Member Posts: 884
    edited 2012-02-23
    Wrong..!!!!! 
    //Bank acc ledger enty onpredataitem
IF LookupValue <> '' THEN
SETRANGE("User ID",LookupValue(UserID));

    If still have issue then ,try to search help(how to Lookup)
  • r-pr-p Member Posts: 33
    vijay_g wrote:
    Wrong..!!!!! 
    //Bank acc ledger enty onpredataitem
IF LookupValue <> '' THEN
SETRANGE("User ID",LookupValue(UserID));

    If still has issue then ,try to search help(how to Lookup)


    Thanks for all the help.
  • r-pr-p Member Posts: 33
    r-p wrote:
    vijay_g wrote:
    Wrong..!!!!! 
    //Bank acc ledger enty onpredataitem
IF LookupValue <> '' THEN
SETRANGE("User ID",LookupValue(UserID));

    If still has issue then ,try to search help(how to Lookup)


    Thanks for all the help.

    Sorry to insist, it's working just fine, but ,

    if the field is empty, the report doesn't work.

    I want the possibilty to make the filter but also to get the report with all the information without any filter.
  • vijay_gvijay_g Member Posts: 884
    see this bold text... 
    IF [b]LookupValue <> ''[/b] THEN
SETRANGE("User ID",LookupValue(UserID));

    PS: Read the post carefuly first then use.
  • r-pr-p Member Posts: 33
    vijay_g wrote:
    see this bold text... 
    IF [b]LookupValue <> ''[/b] THEN
SETRANGE("User ID",LookupValue(UserID));

    PS: Read the post carefuly first then use.


    Like this:

    IF "Bank Account Ledger Entry"."User ID" <> '' THEN
    SETRANGE("User ID","Bank Account Ledger Entry"."User ID");

    if i use this code, and i don't put anything in my lookup field, i get the report only with the information for the user i'm using.
    If i select a user in my lookup field, i get the information for the user i select.

    if i left the lookup field empty, the report should give me the information for all the users, not just the user i'm logged in.
  • dansdans Member Posts: 148
    A hint: The "Bank Account Ledger Entry"."User ID" is NOT your lookup value.
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • r-pr-p Member Posts: 33
    dans wrote:
    A hint: The "Bank Account Ledger Entry"."User ID" is NOT your lookup value.

    i don't understand???

    if i want to make a lookup field with a filter, so when i run my report i need to have the possibilities:

    - if in my lookup field i choose a user, then i get the report with the information filterted for that user;

    but

    - if my lookup field is empty, i get all the information in the report, not just the information for the user i'm using.
  • mohana_cse06mohana_cse06 Member Posts: 5,504
    Did you try this?
    IF LookupValue <> '' THEN
  SETRANGE("User ID",LookupValue(UserID))
ELSE
  SETRANGE("User ID");

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • r-pr-p Member Posts: 33
    mohana_cse06 wrote:
    Did you try this?
    IF LookupValue <> '' THEN
  SETRANGE("User ID",LookupValue(UserID))
ELSE
  SETRANGE("User ID");



    i try in this moment


    IF "Bank Account Ledger Entry"."User ID" <> '' THEN
    SETRANGE("User ID","Bank Account Ledger Entry"."User ID")
    ELSE
    SETRANGE("User ID");


    same thing.
  • mohana_cse06mohana_cse06 Member Posts: 5,504
    r-p wrote:
    Like this:

    IF "Bank Account Ledger Entry"."User ID" <> '' THEN
    SETRANGE("User ID","Bank Account Ledger Entry"."User ID");
    This is wrong..

    You have to check the value with Lokkup filter value not with "Bank Account Ledger Entry"."User ID"...

    BTW: What is the Sourceexpression of your Filter text box on options tab..

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • dansdans Member Posts: 148
    how did you create your lookup field ?
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • r-pr-p Member Posts: 33
    dans wrote:
    how did you create your lookup field ?

    From report 1497, follow some instructions from user "vijay_g" you can see the messages in this post
  • mohana_cse06mohana_cse06 Member Posts: 5,504
    r-p wrote:
    dans wrote:
    how did you create your lookup field ?

    From report 1497, follow some instructions from user "vijay_g" you can see the messages in this post

    Thats Fine..

    What is your SourceExpr of the field and code written in OnLookup trigger?

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • r-pr-p Member Posts: 33
    mohana_cse06 wrote:
    r-p wrote:
    dans wrote:
    how did you create your lookup field ?

    From report 1497, follow some instructions from user "vijay_g" you can see the messages in this post

    Thats Fine..

    What is your SourceExpr of the field and code written in OnLookup trigger?


    SourceExpr of the field : "Bank Account Ledger Entry"."User ID"

    code written in Onlookup trigger - can you help, where can i found it??
  • mohana_cse06mohana_cse06 Member Posts: 5,504
    r-p wrote:
    SourceExpr of the field : "Bank Account Ledger Entry"."User ID"

    code written in Onlookup trigger - can you help, where can i found it??

    Are you a technical person or end user?

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • r-pr-p Member Posts: 33
    mohana_cse06 wrote:
    r-p wrote:
    SourceExpr of the field : "Bank Account Ledger Entry"."User ID"

    code written in Onlookup trigger - can you help, where can i found it??

    Are you a technical person or end user?

    I'm technical person, i'm new in programming, unfortunately, i was giving small steps, then i change place and in this new, first they say i just need to do small changes, but i have big things in my hands. And there are things that i don't konw.

    Almost Everything i know was from seeing others technicals and study.
  • mohana_cse06mohana_cse06 Member Posts: 5,504
    r-p wrote:
    mohana_cse06 wrote:
    r-p wrote:
    SourceExpr of the field : "Bank Account Ledger Entry"."User ID"

    code written in Onlookup trigger - can you help, where can i found it??

    Are you a technical person or end user?

    I'm technical person, i'm new in programming, unfortunately, i was giving small steps, then i change place and in this new, first they say i just need to do small changes, but i have big things in my hands. And there are things that i don't konw.

    Almost Everything i know was from seeing others technicals and study.

    I am sorry but you cant completely dependednt on others always..
    without knowing the triggers and basic technical functions..

    Please ask for the training in your office..

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • r-pr-p Member Posts: 33
    I know that, and i'm making all that i can, so they can give me the correct training, but for the moment i need to solve this problems.
  • SogSog Member Posts: 1,023
    When you don't know the correct questions you can't really program in NAV.
    No biggy, we've all been there. (I'm under the assumption that "Bank Account Ledger Entry" is a dataitem in your report).

    To create a lookup filter the following questions are handy:
    Can I use standard NAV functionality for the lookup? Yes you can. If you need to filter a dataitem, designing the request form isen't always needed. If you define the "ReqFilterFields" on the properties of your dataitem, you'll have standard the option to filter on the field when running the report under the dataitem tab on the request form lookupenabled and all.
    If you persist on designing the request form.
    How will I use the data I've looked up? - to create a filter -> Create global variable of type lookupfieldtype (Code20 for UserID, but if you want to filter on multiple values, you're better of using the standard NAV way, or use a type with more characters (i.e. Text 250))
    Do I need to use my dataitem as variable on the request form? - Almost never!
    What's the source table of the data I need? - "User" and "Windows Login" (Not "Bank Account Ledger Entry")
    --> Do I need both tables? Usually no, if yes, you can use a temporary record to merge SQL and Windows users (other solutions also possible).
    When do I need to filter - before the dataitem is being run -> onpredataitem trigger

    So you're better of with the reqfilterfield option.
    Else you can create for a single value filter a global variable of code 20.
    Design the request form, add a textbox with sourcexpression of your variable, and a TableRelation of "windows login"."User ID" (assuming your users log in with windows authentication).

    In the onpredataitem of your dataitem ("bank account ledger entry") you add the code 
    if myfiltervar <> '' then
  setrange("user id",myfiltervar) //or setfilter("user id",myfiltervar)
else
  setrange("user ID");//removing any filters on user id

    Do you understand where you went wrong?
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • r-pr-p Member Posts: 33
    Sog wrote:
    When you don't know the correct questions you can't really program in NAV.
    No biggy, we've all been there. (I'm under the assumption that "Bank Account Ledger Entry" is a dataitem in your report).

    To create a lookup filter the following questions are handy:
    Can I use standard NAV functionality for the lookup? Yes you can. If you need to filter a dataitem, designing the request form isen't always needed. If you define the "ReqFilterFields" on the properties of your dataitem, you'll have standard the option to filter on the field when running the report under the dataitem tab on the request form lookupenabled and all.
    If you persist on designing the request form.
    How will I use the data I've looked up? - to create a filter -> Create global variable of type lookupfieldtype (Code20 for UserID, but if you want to filter on multiple values, you're better of using the standard NAV way, or use a type with more characters (i.e. Text 250))
    Do I need to use my dataitem as variable on the request form? - Almost never!
    What's the source table of the data I need? - "User" and "Windows Login" (Not "Bank Account Ledger Entry")
    --> Do I need both tables? Usually no, if yes, you can use a temporary record to merge SQL and Windows users (other solutions also possible).
    When do I need to filter - before the dataitem is being run -> onpredataitem trigger

    So you're better of with the reqfilterfield option.
    Else you can create for a single value filter a global variable of code 20.
    Design the request form, add a textbox with sourcexpression of your variable, and a TableRelation of "windows login"."User ID" (assuming your users log in with windows authentication).

    In the onpredataitem of your dataitem ("bank account ledger entry") you add the code 
    if myfiltervar <> '' then
  setrange("user id",myfiltervar) //or setfilter("user id",myfiltervar)
else
  setrange("user ID");//removing any filters on user id

    Do you understand where you went wrong?


    I believe yes.

    I'm working on report 1404.
    and i want to make the lookup field filter on "Bank Account Ledger Entry".

    DataItem Name
    Bank Account <Bank Account>
    Bank Account Ledger Entry <Bank Account Ledger Entry>
    Integer <Integer>
  • r-pr-p Member Posts: 33
    r-p wrote:
    Sog wrote:
    When you don't know the correct questions you can't really program in NAV.
    No biggy, we've all been there. (I'm under the assumption that "Bank Account Ledger Entry" is a dataitem in your report).

    To create a lookup filter the following questions are handy:
    Can I use standard NAV functionality for the lookup? Yes you can. If you need to filter a dataitem, designing the request form isen't always needed. If you define the "ReqFilterFields" on the properties of your dataitem, you'll have standard the option to filter on the field when running the report under the dataitem tab on the request form lookupenabled and all.
    If you persist on designing the request form.
    How will I use the data I've looked up? - to create a filter -> Create global variable of type lookupfieldtype (Code20 for UserID, but if you want to filter on multiple values, you're better of using the standard NAV way, or use a type with more characters (i.e. Text 250))
    Do I need to use my dataitem as variable on the request form? - Almost never!
    What's the source table of the data I need? - "User" and "Windows Login" (Not "Bank Account Ledger Entry")
    --> Do I need both tables? Usually no, if yes, you can use a temporary record to merge SQL and Windows users (other solutions also possible).
    When do I need to filter - before the dataitem is being run -> onpredataitem trigger

    So you're better of with the reqfilterfield option.
    Else you can create for a single value filter a global variable of code 20.
    Design the request form, add a textbox with sourcexpression of your variable, and a TableRelation of "windows login"."User ID" (assuming your users log in with windows authentication).

    In the onpredataitem of your dataitem ("bank account ledger entry") you add the code 
    if myfiltervar <> '' then
  setrange("user id",myfiltervar) //or setfilter("user id",myfiltervar)
else
  setrange("user ID");//removing any filters on user id

    Do you understand where you went wrong?


    I believe yes.

    I'm working on report 1404.
    and i want to make the lookup field filter on "Bank Account Ledger Entry".

    DataItem Name
    Bank Account <Bank Account>
    Bank Account Ledger Entry <Bank Account Ledger Entry>
    Integer <Integer>


    Problem solved, follow your instructions and finally it's working, like i wanted.

    Thanks for all the help.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    r-p wrote:

    i try in this moment


    IF "Bank Account Ledger Entry"."User ID" <> '' THEN
    SETRANGE("User ID","Bank Account Ledger Entry"."User ID")
    ELSE
    SETRANGE("User ID");

    I can't follow this. You have a number of people trying to help you. They started out giving hints which you refused to listen to. Then they start giving you the code, which you refuse to use. Then they tell you to actually follow their suggestions, but agin you refuse.

    Now you are asking what is wrong.

    Its simple; what is wrong is that people are trying desperately to help you and you are refusing to listen to that help.
    David Singleton
  • r-pr-p Member Posts: 33
    David Singleton wrote:
    r-p wrote:

    i try in this moment


    IF "Bank Account Ledger Entry"."User ID" <> '' THEN
    SETRANGE("User ID","Bank Account Ledger Entry"."User ID")
    ELSE
    SETRANGE("User ID");

    I can't follow this. You have a number of people trying to help you. They started out giving hints which you refused to listen to. Then they start giving you the code, which you refuse to use. Then they tell you to actually follow their suggestions, but agin you refuse.

    Now you are asking what is wrong.

    Its simple; what is wrong is that people are trying desperately to help you and you are refusing to listen to that help.


    Hello i did not refuse to listen the help. did you not read my last reponse, i follow this last instructions and i was able to get this working.

    I'm the first interest to learn with all the help that you people can give me. I'm not ignoring. Maybe the problem is that something for you that is very simple, for me is a big problem because i'm in the begining.

    Thanks for all the help.
Sign In or Register to comment.