Filter Item Form by Sales Lines

erugalathaerugalatha Member Posts: 120
Hi,

I'm showing a custom form from a drill down on the shipping tab of a Sales Order. On this Custom form I have the Item Code which is linked back to the item table.

When the custom form is displayed and I lookup on this Item Code is it possible to show only Items that are currently on the Sales Lines of the Sales Order?

Comments

  • krikikriki Member, Moderator Posts: 9,120
    Yes, but you will have to do some programming.
    I think the easiest is to create a singleinstance-codeunit with a temptable in T27:"item".
    When calling the custom form, first you have to fill up the temptable in the codeunit with the items on your sales order.
    When you do a lookup on the item list, you have to check if there are items in the temptable of the codeunit. If there are, mark those records in the OnOpenForm of the item list, and do a MARKEDONLY.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • erugalathaerugalatha Member Posts: 120
    ok thanks for replying.

    I've created the codeunit and the temptable item but I'm not sure how to fill the temptable with items on my sales lines.

    Could you help further please?
  • mightykidmightykid Member Posts: 23
    Hi! I have done something similar to this all you have to do is:

    1. pass the document no. of the Sales order to the custom form.
    2. use the document no. to filter a sales line record variable.
    3. filter the sales line record variable again to remove lines that are not items.
    4. fill up a temporary item table using all the items in sales line. like this

    REPEAT
    item.GET(salesline."No.");
    tempitem := item;
    tempitem.INSERT;
    UNTIL salesline.NEXT = 0;

    5. when doing a lookup use
    FORM.RUNMODAL(FORM::"Item List",tempitem);

    hope that helps :)
  • erugalathaerugalatha Member Posts: 120
    ok, I've done steps 1., 2., 3., 4., and 5 but when I try to lookup a second time by pressing the Item No. lookup on my custom form I get an error message saying:

    Item No. 'LS-75' already exists.

    It happens for any item - do I need to clear the tempItem table?

    Also when the Item List appears there are no OK and Cancel buttons - how do I make the list so that I can select one of the items on it and it will return that item no. into the item no. on my custom form?

    Sorry for all the questions but I think I am nearly there on this one. :)
  • mightykidmightykid Member Posts: 23
    sorry i just thought of the solution without actually coding it. you need to clear (use deleteall) tempitem table before populating it.

    did you use FORM.RUNMODAL? it should have OK and CANCEL buttons.
  • krikikriki Member, Moderator Posts: 9,120
    mightykid wrote:
    sorry i just thought of the solution without actually coding it. you need to clear (use deleteall) tempitem table before populating it.

    did you use FORM.RUNMODAL? it should have OK and CANCEL buttons.
    Use:
    IF FORM.RUNMODAL(...) = ACTION::LookUpOK THEN BEGIN
    
    END;
    
    There is some strange thing in this code. It seems that the "ACTION::LookUpOK" is send to the form so the form can act upon it.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • erugalathaerugalatha Member Posts: 120
    Thanks a lot for your help both of you. \:D/

    There's one thing happening though that seems strange - when I select an item from the list on the Item List it does not return that Item back into my custom form Item No. field.

    Do I need to do something within the IF statement below to make this happen?
    IF FORM.RUNMODAL(...) = ACTION::LookUpOK THEN BEGIN
    END;
    
  • krikikriki Member, Moderator Posts: 9,120
    CLEAR(recSomeRecord);
    // you can also put some filters on the record or give an initial record to position on
    IF FORM.RUNMODAL(FORM::"The Form",recSomeRecord) = ACTION::LookUpOK THEN BEGIN
    codSomeField := recSomeRecord."Some Field";
    END;
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • erugalathaerugalatha Member Posts: 120
    Hi,

    I just had a problem here where I need to make sure that I don't try to add the same item twice to the temporary table. e.g. on some sales orders the same item no. will appear more than once (but with different variants) this means when I loop over the Sales Lines to create my temporary table I get the error that the item already exists if there's more than one sales line with the same item no.

    Any ideas how to check what's already in the temp. table before adding?

    thanks for any help
  • girish.joshigirish.joshi Member Posts: 407
    with reference to mightykid's code, instead of using
    tempitem.insert
    

    use
    if tempitem.insert then ;
    
Sign In or Register to comment.