Set Filter Help Needed

[stanlyn]

Hi,

I've got a set filter not evaluating what it should be and I've tried every way that won't work. I cannot get the set filter to express a control's displayvalue into a static value within the set filter expression. I'm sure I've done this sort of thing a thousand times before, but everything I've tried has failed. Here is what I've tried and their results. The first way works, but hardcoded, while all others fail to get me the same string as the top one...

Set Filter To Alltrim(Upper(division_id)) = 'SLA-KY' In 'AR_invoice'
set ("filter") returns ALLTRIM(UPPER(DIVISION_ID))="SLA-KY"
This works correctly, but I'm hardcoding in the value string.


Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = ALLTRIM(UPPER(Thisform.cboDivisionSelector.DisplayValue))
set ("filter") returns ALLTRIM(UPPER(Ar_invoice.DIVISION_ID))=ALLTRIM(UPPER(Thisform.Cbodivisionselector.DISPLAYVALUE))

Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = EVALUATE(ALLTRIM(UPPER(Thisform.cboDivisionSelector.DisplayValue)))
set ("filter") returns errors out with error #12

cc = ALLTRIM(upper(This.DisplayValue))
Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = cc
set ("filter") returns ALLTRIM(UPPER(Ar_invoice.DIVISION_ID))=CC

cc = ALLTRIM(upper(This.DisplayValue))
Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = &cc
set ("filter") returns errors out with error #12

cc = ALLTRIM(upper(This.DisplayValue))
Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = (cc)
set ("filter") returns ALLTRIM(UPPER(Ar_invoice.DIVISION_ID))=(CC)

cc = ALLTRIM(upper(Thisform.cboDivisionSelector.DisplayValue))
Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = (cc)
set ("filter") returns ALLTRIM(UPPER(Ar_invoice.DIVISION_ID))=(CC)

cc = ALLTRIM(upper(Thisform.cboDivisionSelector.DisplayValue))
Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = eval(cc)
set ("filter") returns errors out with error #12

Any ideas?

Thanks, Stanley

 

[Olaf Doschke]

>Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = ALLTRIM(UPPER(Thisform.cboDivisionSelector.DisplayValue))
>set ("filter") returns ALLTRIM(UPPER(Ar_invoice.DIVISION_ID))=ALLTRIM(UPPER(Thisform.Cbodivisionselector.DISPLAYVALUE))
Yes, that's what you set.

>EVALUATE(ALLTRIM(UPPER(Thisform.cboDivisionSelector.DisplayValue)))
>set ("filter") returns errors out with error #12
Well, Displayvalue is typically something not evaluating, is it? Try EVALUATE("SLA-KY"), Error 12, Variable SLA-KY not found.
Of course.

>cc = ALLTRIM(upper(This.DisplayValue))
>Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = cc
>set ("filter") returns ALLTRIM(UPPER(Ar_invoice.DIVISION_ID))=CC
Yes, that's what you set

>cc = ALLTRIM(upper(This.DisplayValue))
>Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = &cc
>set ("filter") returns errors out with error #12
Again, like evaluate. What you end up here with is SLA-KY, instead of "SLA-KY"
If you want to let the filter compare against the displayvalue as string literal, you have to add what string literals neeed: add quotation marks.

Always just think what macro substitution does. It puts the string inside the variable at the place of it's name (and removes the & and an optional final .) So if cc="SLY-KY" the mere string value excludes the quotation marks and is SLY-KY, so you get Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = SLY-KA instead of the needed Set Filter To ALLTRIM(UPPER(AR_invoice.division_id)) = "SLA-KY".

Think of ? "hello". What is printed to the screen? Hello without the quotes. The quotation is just delimiting the string. The core value is inside. Your SET FILTER in the end needs a sting literal, if you don't want it to change dynamically, as SET FILTER could, so you need to add these quotation marks! That's the essential thing you're missing.

The simplest thing to do is create the whole SET FILTER as a string and execute that via Macro Substitution. It doesn't matter for performance, if a whole line is subsituted or just parts of it, the resulting line has to be compiled.

lcSetFilterCommand = "Set Filter To Alltrim(Upper(division_id)) = '+ALLTRIM(upper(This.DisplayValue))+' In 'AR_invoice'"
&lcSetFilterCommand

Make sure you don't have a ' (qoutation mark) within the displayvalue, though. You'll see where that would end up, if you ? lcSetFilterCommand, eg displayvaue O'Neil ends up in: Set Filter To Alltrim(Upper(division_id)) = 'O'Neil' In 'AR_invoice', and that'll syntax error.

Bye, Olaf.

 

[Olaf Doschke]

Sorry, and now I forgot the quotation marks...

lcSetFilterCommand = "Set Filter To Alltrim(Upper(division_id)) = '[highlight #FFFF00]"[/highlight]+ALLTRIM(upper(This.DisplayValue))+[highlight #FFFF00]"[/highlight]' In 'AR_invoice'"
&lcSetFilterCommand

You might also do Textmerge here:

lcSetFilterCommand = Textmerge("Set Filter To Alltrim(Upper(division_id)) = '<<ALLTRIM(upper(This.DisplayValue))>>' In 'AR_invoice'")
&lcSetFilterCommand

Bye, Olaf.

 

[stanlyn]

Thanks Olaf,

That did the trick...

Much appreciated, and I see and understand your code! Stanley

 

[Olaf Doschke]

By the way, as you take the displayvalue of "cboDivisionSelector", most probably a combobox, you haven't yet made the leap forward using int keys, I assume.
You could SET ORDER TO divisionid in the AR_invoice dbf and SET KEY TO cboDivisionSelector.value or SET KEY TO divisions.id, if the divisions table is the rowsource of the combobox.

Bye, Olaf.

 

[stanlyn]

>> you haven't yet made the leap forward using int keys, I assume.
No, I am using them now and am getting more comfortable with them.

The cboDivisionSelector is a combobox that currently uses a sql rowsource and sorted. There is no controlsource as it only gets and holds a single value from the division table that all other operations use, which is its displayvalue.

Thanks, Stanley

 

[Olaf Doschke]

Well, "SLY-KY" is not an int, is it? And AR_invoice.division_id would normally be an int, if you'd use int keys.

I'd never make use of a control.value or displayvalue as picking an item activates that item in the alias. sql rowsource is one I never use because it'll not enable the easier use of a picked record. Very general, the UI and it's properties of the user interface layer are of less value than IDs and record pointers in the non visible part of the ui, the business layer.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

>> Well, "SLY-KY" is not an int, is it? And AR_invoice.division_id would normally be an int, if you'd use int keys.
Well, then I guess I'm not in this case, but what could be easier that creating an ordered and filtered cursor on the fly. I don't care if selecting activates the record in the cursor as its throwaway. (no relation to the invoice table)

Thinking out loud... To make this work as Olaf suggests, I'd need the rowsource type set to alias and include 2 fields, the division.pk (123456) and division.id ("SLA_KY"). I believe you use id as the pk, but here ID is the short version of the name the user relates to, and division.name is the long version as in "Stan-Lyn & Associates - FL" Controlsource is set to none.

Now that the user has selected "SLA-KY" whose pf is 123456 in the combo, I need to filter the invoice table to only allow those records into scope. So on the invoice table, I would "set filter to invoice.division.fk = division.pk"??

I've asked before, how would we filter and sort the alias results that populates the combo without moving the pointer? This is very easily done using the sql cursor as I mentioned above. RowsourceType=3 and RowSource="select id, pk from division order by id into cursor curDivisions" and if you need to a special id to do something special it is as simple as "insert into curDivision (id, pk) values ("Cancel", 99999999). Then in code trap for the 99999999 and cancel.


>> because it'll not enable the easier use of a picked record
Please be specific here and list the cons as you see it...

Thanks, Stanley (student)