How to apply large filter in "Location Filter" field in Item table

xplosive_fibonacci
xplosive_fibonacci Member Posts: 1
in NAV/Navision Classic Client
I have to create a report that calculates the calc field "Net change" for some items in the Item table. The "Net change" field is function of the "Location Filter" flow filter field, so if, for example, you do "Location Filter" := 17 for a given item, the "Net change" is calculated just for Location number 17.

My client wants that on the request form appears a checkbox that allows calculating the "Net change" for different categories of Locations (imagine that, for example, Locations 24 and 67 belongs to a category and 44,36,12 to another).

The problem here is that some categories contain so many Locations than the filter would be something like "1 | 3 | 45 | .... | 10456" and the string is too big that doesn't fit into a Text variable.

Somebody knows some tricky way to create filters better than just enumerate the codes of the Locations?

Thank you very much, all tips/suggestions are welcome!

Answers

  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    How long is your text variable suposed to keep your filter expression? The maximum text lentght is 1024 characters so your selection filter must be really long if it does not fit in 1024 characters.

    If you look into form 18 G/L Account List you will find there GetSelectionFilter function which is supposed to build nicely 'compressed' filter expression

    For example, if you have in your system G/L Accounts numbered 10, 20, 25, 30, 40, 50, 55, 60, 65, 70, 75 and 80 and you do a lookup using form 18 and select accounts 20, 25, 30, 50, and all from 60 to 80, the GetSelectionFilter function is expected to return the filter expression in following form: 20..30|50|60..80 - rather than a simple list like this: 20|25|30|50|60|65|70|75|80

    You could try and copy this function from form 18 to form 15 Location List (renaming it as there is already GetSelectionFilter function on it), and adapt it to work on Location table.

    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03

Categories