"Not an Open Index" with flattenquery update

[Chris.Tanti]

Searched the site and looks like this error occurs A LOT, at various occasions.
I am having to resort to using A5V5 again because this is driving me nuts.
I am querying a set with a 1 to many link, searching the transaction for a string and also a value in the parent table, then doing a simple update of a field for every match. I have to repeat this for around a dozen fields with a slightly different query each time.

Have tried using the auto generated syntax that A5v7 creates, and even saving the procedure as an operation and running that instead of the syntax, same results.

ERROR RUNNING UPDATE OPERATION
NOT AN OPEN INDEX

Removing the flattenquery command removes the error but also doesnt get me the results I want. Works lovely in A5V5!

dim s as p 
s = set.open_session("_set3.set",FILE_RW_EXCLUSIVE)
dim tbl as p 
tbl = s.table_get()
query.filter = "flattenquery(('ECSTRA'$Tbl_Transaction->Mediagrp.or.Tbl_Transaction->Mediasub='SEED').and.Mps<>'N'.and.'MULTIPLE'$Tbl_Transaction->Mediasub=.F..and.del_flag=.f.)"
query.order = "recno()"
query.options = ""
query.description = ""
tbl.query_create()
update.fields = 1
update.field1 = "Club_Ecstra_R"
update.expr1 = "1"
tbl.update()
s.close()

[Tom Cone Jr]

Will the filter work in the interactive editor?

I ask because this looks wrong:

.and.'MULTIPLE'$Tbl_Transaction->Mediasub=.F.

-- tom

[Edward Larrabee]

How about restating the query without using the "$" operator?

[Tim Kiebert]

... or what about using !$ (Substring Exclusion Operator) (new to v7)

 .and.'MULTIPLE' !$ Tbl_Transaction->Mediasub

[CALocklin]

Thanks, Tim. I hadn't seen that one before. I keep using something like:
.not. 'MULTIPLE'$Tbl_Transaction->Mediasub
and getting errors because that construction is wrong. It needs parenthesis:
.not. ('MULTIPLE'$Tbl_Transaction->Mediasub)

The need for parenthesis may be have been Chris' problem but I like the new operator better. Now I just have to remember it!

[csda1]

Hi Cal,

I always put parenthesis around the left and right sides of a $ operator if anything more than a single object and around the whole term, as in

.and.((x+y) $ z).and.

It seems Alpha gets confused at times. I suspect !$ operator falls into the same category. Unless I'm absolutely sure of the precedence of the operations, I always put in the parenthesis just in case Alpha interprets it wrong. This has saved me often.

[csda1]

Hi Cal,

The need for parenthesis may be have been Chris' problem but I like the new operator better. Now I just have to remember it!

Does this sound remotely familiar?;)

[Chris.Tanti]

Thanks for the tips on alternative methods of string searching, but this method works absolutely fine usually. Did try the new !$ method, exactly the same result, NOT AN OPEN INDEX

To me theres is something rather odd happening, and its rather frustrating.
Thought about it last night and I am going to try flattening the set as a 1 to 1 starting transaction end up, and then doing the flagging from that query.
I think it will work, but this is all a bit unnecessary.

[Edward Larrabee]

I still think it would be worth restating the expression without the "$" or "!$" operators.

[csda1]

Chris,

Simplify your query (with flattenquery) for test or add parenthesis like below. I also believe that

'Multiple'$Tbl_Transaction->Mediasub=.f.

is interpreted as

'Multiple'$(Tbl_Transaction->Mediasub=.f.)

not

('Multiple'$Tbl_Transaction->Mediasub)=.f.

' Simple version
query.filter = "flattenquery(('ECSTRA'$Tbl_Transaction->Mediagrp))"

' Version with full use of parenthesis
query.filter = "flattenquery((('ECSTRA'$Tbl_Transaction->Mediagrp).or.(Tbl_Transaction->Mediasub='SEED')).and.(Mps<>'N').and.(.not.('MULTIPLE'$Tbl_Transaction->Mediasub)).and.(del_flag=.f.))"

[Chris.Tanti]

Thanks Ira, Tried your example from a saved operation and get exactly the same error, NOT AN OPEN INDEX.

Simply, if this works successfully in A5V5 (I have now reinstalled it again) and doesnt in A5V7, either v5 was more forgiving of erroneous code or something is broken in v7

[Tom Cone Jr]

Maybe it's time to post the tables and the set, with a brief explanation of the query results you're seeking.

-- tom

[Chris.Tanti]

If I get a chance I will,
I am basically now going to use a flat 1 to 1 set with the transactions at the head, flag the records up and post the results back to the linking table.

Thanks for everyones input, atleast I now know about the !$ command, that was a new one on me!