Filter Item List by Cross-Reference No. (Item Cross Reference)

Kris_twKris_tw Member Posts: 3
Hi All!
How can I filter Page Item List by Cross-Reference No , I add lookup FlowFiled to Item table , with calFormula (ItemNo = No) but when I filter Item List (choose Cross-Reference No from advanced filter) show only Item which have only one line in Item Cross Referance , how should I set up this additional FlowFiled used for filter?
0oxx0krms23c.png
j7rew9w6k0d6.png
l45y84yk30kf.png

Answers

  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    You have asked NAV to show you the line where Cross Reference No is TEST1 and it showed exactly this.

    The Items list page shows you a list of, well, Items. You have ONE item which can be described in many ways (many item cross references), so it shows up on the list once.

    Where is the problem? What do you want to see? A list with mixture of items and item cross references on one page?
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Kris_twKris_tw Member Posts: 3
    Thx for answer,
    As You see on the second print screen, in Item Cross Reference exists line with item = 02130090005120 and Cross Reference NO = TEST2 but do not show on item list
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    edited 2018-02-09
    It is not possible to filter lines in that way. NAV does not work that way,

    Lookup brings you just one value from the other table, usually the first one, and if you filter on the lookup field the result is filtered by the value which was actually brough in, not on values in the lookup table which can be potentially brough in.

    In other words if you apply a filter on a flowfield the filter applies AFTER the internal code behind the lookup brings some value from the lookup table.

    You need to look at the Item Attributes and the coding behind it, this is functionality what does exaclty what you need.

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Kris_twKris_tw Member Posts: 3
    There is no possibility to that ? maybe is some event after filter textbox lost focus and there put code with appropriate filter. I care to that on Item List , user must have possibility to see item by Item Referen. No
    Maybe new Item List as Worksheet ?? ..
    Thx
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    You could if you desingned it in different way.

    Add two fields in the Item, one is Cross Reference Exists, a flowfield and another Crosss Reference Filter, flowfilter.

    Then in Cross Reference Exists flowfield expression use Crosss Reference Filter, somting like
    EXIST(WHERE Item No.=No,Cross-Reference Type No. = Crosss Reference Filter,Cross-Reference Type=...)
    

    Then to get the list fitlered for a specific Crosss Reference you need to filter lines for Cross Reference Exists=True, and specific no in Crosss Reference Filter.

    But will not be fast, and more data you have in the Item Cross Reference table the slower the filtering will be.


    The other way to do this is to build in code a filter expression which includes all the item nos which share the same cross reference, and apply this filter to the item list. This is exactly how the Item Attibutes are implemented

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sign In or Register to comment.