No announcement yet.

Right Adjust Filter field on NAB

  • Filter
  • Time
  • Show
Clear All
new posts

  • Right Adjust Filter field on NAB

    Using NAB, I have an app with a grid. One of the fields is our location, and although it's always numeric, it's in a 5A field. I have a filter on that field so that users can show data for just one location if they want. But they type in '76', for example, and nothing is displayed because the data field contains '___76'. Or '__135', or "____8'. So the number is right adjusted in the 5A data field. The filter works if they type the location like that, but of course they find that confusing and they should be able to just type in the location number without leading blanks.

    How can I accomplish letting them type in '76' in the filter and having it get right adjusted so that it finds all the '___76' records? I have a recurring need for this that will be useful in multiple apps. Thanks.

    p.s. Valence 5.2

  • #2
    I would suggest trimming your padded column at the data source.

    If you’re not using an SQL data source and it’s just a wizard create a calculated column that trims your file column

    trim(yourFileColumn) as trimmedCol


    • #3
      Yes, you're right, trimming the location field would solve the filtering, but it will also make the sorting by location not sort numerically... location 108 would sort before location 11, etc., when the users click to sort on that column.


      • #4
        Sounds like you need to treat the column as numeric. Try doing something like this in your data source:

        select INT(TRIM(your_location_column) as loc,
        from your_file
        Another trick you could try is this, provided you're running on 7.4, or a recent TR of 7.2/7.3:
        select INT(TO_NUMBER(your_location_column) as loc,
        from your_file
        TO_NUMBER() — an alias for DECFLOAT_FORMAT() — is a relatively new built-in function in DB2 SQL that converts character strings (including values containing thousands separators, plus/minus signs, etc.) to regular numbers. Unfortunately the function returns a decimal float value, which the current build of Valence 6.0 does not support, hence you must wrap it in INT(). Valence 6.1 and the final build of Valence 6.0 will include support decimal floats so you can drop the INT() wrapper once you're upgraded.
        Last edited by robert.swanson; 12-30-2021, 02:15 PM. Reason: added TO_NUMBER() example


        • #5
          I like the TO_NUMBER, thanks. Yes, I can get that column into numeric that way, but the filter field is still taking the attributes from the database field and is therefore still 5A... How can I similarly convert the filter field to numeric as well so that I can put a number in there (without leading spaces) and have it match my location numbers in my location column?


          • #6
            May I suggest two solutions:

            1. Filter on the numeric location column that you defined instead of the original column.

            2. Do not change the data source and use a filter program in the widget, based on EXNABFLT:

            ctl-opt debug(*dump);
            /copy qcpylesrc,vvHspec
            /define nabFilter
            /include qcpylesrc,vvNabTmpl
            dcl-proc Process;
            dcl-s post_location char(5);
            dcl-s lFilterString varchar(256);
            post_location = GetValue('F1.LOCATION':'value');
            if post_location <> *blanks;
            evalr post_location = %trimr(post_location);
            lFilterString = 'F1.LOCATION = ' +SQ+ post_location +SQ;
            vvOut_toJsonPair('success:true,' +
            'filter:(' + lFilterString + ')');
            end-proc Process;
            /include qcpylesrc,vvNabTmpl
            If you use the filter program, you will need to type its name in the FILTER PROGRAM field of the Filters tab of the widget. Also you will need to authorize the app to the program in Portal Admin, 2 Settings, enter the name of the program in the PROGRAM NAME field,
            then click on ADD. You will need to add programs VVQRYAPP, VVWDGT and VVDATASRC too and check the Requires Back-End Authorization box.

            Both solutions tested successfully in build 5.2.20201209.0