Implementing a User Defined Record Filter String in a Report

1

Hi All

I want to filter a report based on some selection(s) that a user has made in a another window and are dumped into a string. I want that string to be the expression used in the Report Properties like as shown below. I seem to recall that I need to put some special characters before the GLO:Filter entry to make this work right. I cant remember how this is done.

image

2

!Glo:Filter
The ! says its a variable

1 Like
3

That is fine you are using that Glo:Filter, but if you want to filter the report data then you can use QUEUE and filter the queue based on the filter option selected then display the records.

4

I had School example open and took some captures showing the Exclamation makes it generate a simple assignment as Filter:

image

image

This is common in many of the templates, but not all.

5

thank you both … BTW Carl, I have wondered about the topic of example files … I don’t seem to get any when I did full installs for C8. Are they somewhere else?

6

One other issue (I have lots of them :slight_smile: ):
LEGACY CODE: The filters I am using are on two fields: SCH:Date and SCH:MachineName. Things go great when I filter on the date and variations on the date (start/end dates), but when I filter on machine name, I am getting this error:

image
image901×177 12.4 KB

The Sessions file is a child to the SCH file. I am not doing any filtering on the Child file. The Report is simply printing the Parent Header (SCH:) along with the linked Child (Sessions) data.

I have researched that error and it seems that there it has been longstanding and that there is no workaround (staying in template land) and may require hand coding. TRUE?

I should add that the SCH: file consists of DATE and MachineName.

7

To help spot the problem you’ll have to show the contents of your filter string throwing the error i.e. Glo:Filter .

One likely issue is you must BIND() any variables you use in that String. Is “Laser” a variable?

E.g. if Glo:Filter='SCH:MachineName = MachineWanted'
you’ll must have done a BIND('MachineWanted',MachineWanted ).
Search the source for BIND and add yours near there.

If you are trying to match the literal “Laser” your filter string will need quotes around that literal e.g.
Glo:Filter='SCH:MachineName = ''Laser'' '. While matching a Number does not need quotes e.g. Glo:Filter='SCH:Date = 80808 '

There is a QUOTE() function to help.

1 Like
8

Here is the part of the filter building code that is meessing up:

        IF LEN(CLIP(GLO:MachineFilter)) > 0 
            IF LEN(CLIP(GLO:Filter)) > 0  ! to append or not
                GLO:Filter = CLIP(GLO:Filter) & ' AND Sch:MachineName = ' & CLIP(GLO:MachineFilter)
            ELSE
                GLO:Filter = 'Sch:MachineName = ' & CLIP(GLO:MachineFilter)
            .
        .

The last part of the IF is goofing up. GLO:MachineFilter gets filled by a dropdown list. It contains the value ‘Laser’ from the dropdown. Sch:MachineName is a field from the file being filtered. Sessions is a child file to SCH and is not in the filter, but is shown in the report as a child file. The child processes fine when the MachineFilter stuff is not in the filter.

9

A literal needs to be in Single Quotes. Quotes must be doubled in Source. That can be hard to see so some use <39> or CHR(39). Show the Filter in a message or debug to be sure what you have. QUOTE()

GLO:Filter = 'Sch:MachineName = ''' & QUOTE(CLIP(GLO:MachineFilter)) &''''
! or with <39> for Single Quote as more visible
GLO:Filter = 'Sch:MachineName = <39,39>' & QUOTE(CLIP(GLO:MachineFilter)) & '<39,39>'

You have your filter code 2 Times, once adds an ' AND ' to append to existing filter. That’s a pain. There are a few ways to eliminate that, like using CHOOSE(Condition, True Value, False Value) that is an IF Function to append the " AND ".:

  IF GLO:MachineFilter THEN     !Is it Not Blank? 
       GLO:Filter = CHOOSE(~GLO:Filter,'', CLIP(GLO:Filter) & ' AND ') & | 
            'Sch:MachineName = <39,39>' & QUOTE(CLIP(GLO:MachineFilter)) & '<39,39>'
  END

A simpler way to deal with that is to always insert the 'AND '. Then at the end of your concatenation code check IF the Filter starts with ' AND ' then remove it:

  IF GLO:DateFilter THEN     !Is it Not Zero? 
       GLO:Filter = CLIP(GLO:Filter) & ' AND ' & | 
            'Sch:Date = ' & GLO:DateFilter
  END
  IF GLO:MachineFilter THEN     !Is it Not Blank? 
       GLO:Filter = CLIP(GLO:Filter) & ' AND ' & | 
            'Sch:MachineName = <39,39>' & QUOTE(CLIP(GLO:MachineFilter)) & '<39,39>'
  END
  IF SUB(GLO:Filter,1,5)=' AND ' THEN     !Starts with just " AND " ?
       GLO:Filter=LEFT(SUB(GLO:Filter,6, SIZE(GLO:Filter) )   !Reomve first 5  byteswith 'AND '
  END
10

Consider this: The filter on the Report works when I just reduced it to the following:

Sch:MachineName = GLO:MachineFilter

Granted that is the directly entered in the the Record Filter entry in the Report Properties, and not part of a string in GLO:FIlter. Why should it not work as part of the string? No quotes are needed for the expression as it does not contain any literals.

11

That is typical Filter code with a File Variable = Variable with Value I Want . That Value Variable works because it is BINDed.

All the answers are in the Generated code. Open the Embeditor and search for GLO:MachineFilter. You should find these 2 lines:

Process:View{PROP:Filter}='Sch:MachineName = GLO:MachineFilter'

BIND('GLO:MachineFilter',GLO:MachineFilter)

The Templates spotted you used GLO:MachineFilter in a Filter and added the BIND so it would work in FILTER. Without that BIND you get error 1011. Read the Help on BIND, FILTER and EVALUATE.

1 Like