.and. problems

[Blake]

I've had this problem before but I can't remember what I did to resolve it or what caused it. I have situation where I need to filter on two conditions. Condition 1 works and returns the expected items. If I add Condition 2 in, however, every item is returned.

In other words:

?table.external_record_content_get(tbl, afield", "", "cond1=.T.")

returns a subset of what is returned by:

?table.external_record_content_get(tbl, afield", "", "cond1=.T. .and. cond2()")

This, of course, should never ever be possible, logically, because anything ANDed to anything else should only be able to reduce the set. I have had it happen before (can't find it on the boards, now, but I'm pretty sure I've brought it up).

The only thing I can figure is that as Cond2() is a routine, and creates a query, that somehow that's clobbering another filter somewhere. Cond2() works fine on its own, just as Cond1=.T. does.

I know that's not a lot of info to go on, but I'm hoping to trigger a memory in someone.<s> Thanks for any thoughts.

[Tom Cone Jr]

If you don't know what cond2() might be can you at least post the source for it? if you're using the correct syntax it's a user defined function, and you may be able to find and open it (design mode) on the Code tab of your control panel. -- t

[Tom Cone Jr]

Please verify what you said.

does this expression actually work, as you presented it ?

?table.external_record_content_get(tbl, afield", "", "cond1=.T.")

Seems to me there's a quote mark needed just before afield. You'll do better here if you show the actual expression, describing the properties of each term used in the expression.

-- t

[Blake]

I changed the table names to protect the innocent.<s>

The code illustrating the problem is below, and I found the thing causing the problem which I'll post in response to your other message, though no solution occurs to me, perhaps it will to you.

eqno="00000004"

cond1 = "equip_no="+quote(eqno)
?table.external_record_content_get("item_equipinv", "trim(equipdesc)+chr(9)+trim(id_no)", "", cond1)
= 6' Scaffold	00000003
6' Scaffold	00000004
6' Scaffold	00000005


sdate=date()
edate=date()
cond2 = "EquipFree(Id_No, {"+sdate+"}, {"+edate+"}, ScheduleType)"
?table.external_record_content_get("item_equipinv", "trim(equipdesc)+chr(9)+trim(id_no)", "", cond2) 
= Forklift	00000001
4500 Watt Generator	00000002
6' Scaffold	00000003
6' Scaffold	00000004
6' Scaffold	00000005
Forklift 10 ft	00000006

Forklift 10 ft	00000007

filter=cond1+".and."+cond2
?filter
= equip_no="00000004".and.EquipFree(Id_No, {10/07/2005}, {10/07/2005}, ScheduleType)

'this should not return anything more than COND1 by itself.
?table.external_record_content_get("item_equipinv", "trim(equipdesc)+chr(9)+trim(id_no)", "", filter)
= Forklift	00000001
4500 Watt Generator	00000002
6' Scaffold	00000003
6' Scaffold	00000004
6' Scaffold	00000005
Forklift 10 ft	00000006
Forklift 10 ft	00000007

[Blake]

I'm sorry: I do know what cond2() is, it's my routine. My point is there should NEVER be a case where cond1 AND cond2 results in more items than COND1 by itself.

I found the cause, however. In cond2() is a routine that opens a table, performs a query and returns true or false based on the results of that query. However, I've reduced it to this:

      tbl = table.open("XjoBEquip")
      tbl.close()
      EquipFree=.T.

And the problem remains. If I take out the table open and close, it works. In other words, returning .T. does what's expected--I get the same thing as I would with COND1 alone. If return .F., I get nothing, of course.

But regardless of whether I return .T. or .F., if I open xJobEquip or any table, I get everything in the table, including things that COND1 should have eliminated.

[csda1]

Blake,

I suspect you might have an ordering problem with the filter evaluation. Try something that results in a filter like this

(equip_no="00000004").and.EquipFree(Id_No, {10/07/2005}, {10/07/2005}, ScheduleType)

[Tom Cone Jr]

Blake,

I see what you're saying now. It looks like something that should be reported using "Send a bug report". In the meantime I'm curious:

a) whether you get the same results if you switch the order of the two expressions within the filter?

b) what results you'd get if you used only your EquipFree() udf in the filter expression?

I suspect there's a timing issue here, since your udf may be relatively slow, but I can't say I've ever attempted what you're doing, either, so this is simple speculation on my part.

-- tom

[Blake]

Ira,

I suspect you might have an ordering problem with the filter evaluation. Try something that results in a filter like this

(equip_no="00000004").and.EquipFree(Id_No, {10/07/2005}, {10/07/2005}, ScheduleType)

I'm trying to figure out what the difference is between what you have here and what I had. Oh, parentheses. That would cause a problem, if the "00000004" was being ANDed with the EquipFree results. But then I'd get nothing.

I would test it, but right at this moment, it's giving the right result. I suspect Tom is right: It's a timing issue of some sort.

[Blake]

Blake,

I see what you're saying now. It looks like something that should be reported using "Send a bug report". In the meantime I'm curious:

a) whether you get the same results if you switch the order of the two expressions within the filter?

b) what results you'd get if you used only your EquipFree() udf in the filter expression?

I suspect there's a timing issue here, since your udf may be relatively slow, but I can't say I've ever attempted what you're doing, either, so this is simple speculation on my part.

-- tom

a) I'll try it once it starts breaking again.

b) You can see that in the code I uploaded. I get a partial set (B) when I use the simple filter, the full set (A) when I use the UDF, and the full set (A) when I use both (but I should get B). I'll have to change the data to make the UDF give different results.

[csda1]

As far as I know, since you are using a function, Equipfree(), the filter must wait for the evaluation of that function. So it should be 100% consistent and work, no matter what the speed (this is not to say thet EquipFree might not have internal timing issues affecting it's results, but the result would be consistent and waited for in the calling line)

On the other hand, the table open and close of the function happen every single time the filter is evaluated, and that is for every record in the external table (assuming LQO is not being used, and I doubt it would with your expression, this would be really slow). A table open/close is a very time and resource intensive operation. I suggest you open the table once, then do your table.external_record_get() passing the pointer to the table to EquipFree() via another parameter to it.

Then close after you are done in the main code.

[Daniel Weiss]

use the &quot;eval&quot; function

Try using the "eval" function it should work
eval("cond1") instead of cond1

here is an complex example which i copied form one of my scripts (and it works)

if use_pro_his_tbl = "prod" .or. use_pro_his_tbl = ""
	qq2 = ".and.Item_Inactive <> .t."
	colm = "L_Code +"|"+Desc+"|"+V_Code+"|"+M_Code+"|"+ "~"+L_Code+"@"+'"+c_custid+"'+"-"+'"+c_shiptoid+"'"
else
	qq2 = ".and. cust_id = "+quote(c_custid) +iif(use_pro_his_tbl = "hist_s", " .and. shto_id = "+quote(c_shiptoid),
	iif(table_name = "cust_item.dbf"," .and. shto_id ="main"",""))
	colm = "L_Code  +"|"+ Desc +"|"+ V_Code+"|"+M_Code+"|"+ "~"+Item_Cust+ "~"+recno()"
end if
qq3 = iif(selec_catog != "",".and. M_Cat = "+quote(selec_catog),"")
qq4 = iif(selec_scatog !="",".and.S_Cat = "+quote(selec_scatog),"")

if find_filter <> ""
	qqq = quote(find_filter)+"$"+fl1+qq2+qq3+qq4+" .or."+quote(find_filter)+"$"+fl2+qq2+qq3+qq4+" .or."+quote(find_filter)+"$"+fl3+qq2+qq3+qq4+" .or."+quote(find_filter)+"$"+fl4+qq2+qq3+qq4
else if find_filter2 <> ""
	qqq = quote(find_filter2)+"$"+fl1+qq2+qq3+qq4+" .or."+quote(find_filter2)+"$"+fl2+qq2+qq3+qq4+" .or."+quote(find_filter2)+"$"+fl3+qq2+qq3+qq4+" .or."+quote(find_filter2)+"$"+fl4+qq2+qq3+qq4
else if showall_filter
	qqq = stritran(qq2,".and.","",1,1)+qq3+qq4
else
	qqq = ""
end if

if qqq <> ""
	prod_list = table.external_record_content_get(table_name,eval("colm"),search_by_sort,eval("qqq"))
else
	prod_list = ""+crlf()
end if

[davej]

.and. problems

Blake,

You got a number of comments on your original question having to do with the use of the logical .and. operator. I don't know if you got a satisfactory answer of not, but let me throw in the use of .and. and .or. .

The logical operator .and. causes the ENTIRE expression to be true if, and only if, both the segment before and the segment after .and. are true (ex: (today="Friday" .and. rain="Yes") is true if today is Friday AND it is raining. If either side is false, the entire expression is false. If you replace .and. with .or., the expression is true if either side is true.). The way I used to explain it to my students is that .and. is inclusive (both sides have to be true for the expression to be true) and .or. is exclusive (either side has to be true for the expression to be true.

[Blake]

Try using the "eval" function it should work
eval("cond1") instead of cond1

Int'resting. I'll give that a shot.

[Blake]

The logical operator .and. causes the ENTIRE expression to be true if, and only if, both the segment before and the segment after .and. are true (ex: (today="Friday" .and. rain="Yes") is true if today is Friday AND it is raining. If either side is false, the entire expression is false. If you replace .and. with .or., the expression is true if either side is true.). The way I used to explain it to my students is that .and. is inclusive (both sides have to be true for the expression to be true) and .or. is exclusive (either side has to be true for the expression to be true.

Early on in my career, I learned PL/I, which had this great built-in called BOOL. It did a bitwise operation on two parameters that was governed by a eight-character string of zeros and ones. Basically, the string determined what the result was for all possible variations of both paramters. The beauty of this was that it made you look at the logical operation, rather than imposing an English-language interpretation, and all the combinations had names. So, yeah, you had AND, OR, XOR and NOT, but you also had NAND and NOR, etc.

These things are so ingrained that I confess to being at a bit of a loss when they don't work in A5.

[davej]

Boolean Logic

Blake,

The boolean logic you learned (and loved) back in the old days is constant and still applies. The way you code it is possibly different in A5 than in PL/I, 'possibly' because I've never touched PL/I.