Querying a form and comparing two columns

A while back someone asked if anyone knew how to do searching through a form in such a way that you could compare one column to another. I have figured out a way to do this and was going to mail back my solution, but I had to change Unix accounts and lost the person's mail address, so I'm posting it. The person wanted to be able to fill in a form like this example: Col1: [ ] Col2: [col1/2 ] where the search would find rows where col2 = col1 / 2. My solution: Step 1: Create a form to do this where all of the fields are character fields. If you have numeric or other non-character fields, replace them with character fields as in the field attribute definition below: Before: a = table.number_field; After: a = formonly.number_field type char; Step 2: Run CONSTRUCT on the form as usual, using BY NAME if you want. Inform your users that if they want to compare a column with another column they should put tildes around what they type in, i.e: Col2: [~col1/2~ ] CONSTRUCT will return this part of the WHERE string as col2 = "~col1/2~" Step 3: Assuming that CONSTRUCT returns a WHERE string named qry, run the following 4gl code: define i, len smallint let len = length(qry) for i = 2 to len - 1 if qry[i,i] = "~" then if qry[i-1,i] = "\\"~" then let qry[i-1,i] = " " end if if qry[i,i+1] = "~\\"" then let qry[i,i+1] = " " end if end if end for This will change the WHERE string above to col2 = col1/2 which should do what you want. Of course, you may have problems if your users do normal searches on strings containing tildes. You will also have problems if a user, say, places a tilde before their comparison but not after. But there are workarounds for these problems if you are worried about them.