relational operators not supported?
Guys, I have a big problem.
I have written an application which I am to demostrate to the company out our annual conference tomorrow afternoon.
I have created a local replica on my laptop to demonstrate with as the conference center will not have sufficient connectivity with out HO.
As part of the demonstration I will be showcasing the search functions of Notes. When using the server copy and index, I am able to search for docs if the contents of a number field exceeds a therotical value, however my local copy will not behave the same. I have created a local replica and local full text index, but now search function doesn't recognise the field is a number field, i.e. it wont give me usual options (is equal to, greater than etc etc) instead just offers text feild options (contains, does not contain)
Also using a previous saved (shared) search which uses this number field to find a value greater than x, notes reports the error "relational operators not supported in text fields"
What have I done wrong? is this something that cannot be done with a local replica's index?
Any help would be great, even if it is bad news. What a bombshell the night before my presentation!!!!
~scribla.
I have written an application which I am to demostrate to the company out our annual conference tomorrow afternoon.
I have created a local replica on my laptop to demonstrate with as the conference center will not have sufficient connectivity with out HO.
As part of the demonstration I will be showcasing the search functions of Notes. When using the server copy and index, I am able to search for docs if the contents of a number field exceeds a therotical value, however my local copy will not behave the same. I have created a local replica and local full text index, but now search function doesn't recognise the field is a number field, i.e. it wont give me usual options (is equal to, greater than etc etc) instead just offers text feild options (contains, does not contain)
Also using a previous saved (shared) search which uses this number field to find a value greater than x, notes reports the error "relational operators not supported in text fields"
What have I done wrong? is this something that cannot be done with a local replica's index?
Any help would be great, even if it is bad news. What a bombshell the night before my presentation!!!!
~scribla.
Sjef Bosman
Can you give the essential parts of the code? And what are the Notes and Domino versions of PC and server?
Somewhere in the back of my mind is something that says to me that if the field you refer to isn't numeric in ALL documents it appears in, search won't work. Do you have an EXACT replica of the database on uour local system?
This can be difficult.
Somewhere in your application (Design and/or Data) the field you search for is defined as text. The first time the Indexer finds a field, it assigns a datatype to be shown with the FT-search-editor.
Getting rid of this can be outmost difficult. Bach with V3/R4 it involved agents to reasign fields, design editing of forms, compactions, new replicas etc.
Have you tried to use the [ ] FT-notation. E.g.
[PostalCode] > 20000 & [PostalCode] < 30000
entered directly in the search field?
It's the same as
FIELD PostalCode > 20000 & FIELD PostalCode < 30000
Somewhere in your application (Design and/or Data) the field you search for is defined as text. The first time the Indexer finds a field, it assigns a datatype to be shown with the FT-search-editor.
Getting rid of this can be outmost difficult. Bach with V3/R4 it involved agents to reasign fields, design editing of forms, compactions, new replicas etc.
Have you tried to use the [ ] FT-notation. E.g.
[PostalCode] > 20000 & [PostalCode] < 30000
entered directly in the search field?
It's the same as
FIELD PostalCode > 20000 & FIELD PostalCode < 30000
ASKER
FYI:The field is called QuoteValue and is a numeric currency field, no decimals. It is also set to puncuate at 1000's.
If I search for:
[QuoteValue] > 1000
It causes the same error on the local replica as the saved search, but works fine on the server copy. In an effort test the theory of an incorrect datatype stored during the FT index, tried the following on the server copy of the database:
Compacted (created new copy, discarding built index views)
Deleted the FT index
Compacted (created new copy, discarding built index views)
Fixedup
Created a new FT index
The server copy still works correctly but replicas will not. No change there.
Maybe the above is not an effective method? Maybe create a new copy elsewhere on the server be a better test?
If I search for:
[QuoteValue] > 1000
It causes the same error on the local replica as the saved search, but works fine on the server copy. In an effort test the theory of an incorrect datatype stored during the FT index, tried the following on the server copy of the database:
Compacted (created new copy, discarding built index views)
Deleted the FT index
Compacted (created new copy, discarding built index views)
Fixedup
Created a new FT index
The server copy still works correctly but replicas will not. No change there.
Maybe the above is not an effective method? Maybe create a new copy elsewhere on the server be a better test?
That doesn't help. I think there is one document (or maybe there are some of them) that still has a textvalue in that field, or some other value that is incompatible with the search. The best you can do is to put your current replica aside, and make a new replica from the server on your local pc. So:
- shut down Notes
- rename yourdb.nsf to yourdb.old
- start Notes
- create a new local replica
Then try again.
- shut down Notes
- rename yourdb.nsf to yourdb.old
- start Notes
- create a new local replica
Then try again.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thats seems to be the case. I created a new copy on the server but excluded the documents. Created a few test docs, created an index, then created a local replica & index = all is well.
There are only about 100 or so docs in the database, it is all real data captured from employees and I would like to include in my presentation. Whats the easiest way of cleaning these documents, or finding the rouge? My only idea was to create a view that contained every single field and the export to CSV and then import into the new copy. Would that work? or is there some way I could do this with an agent?
There are only about 100 or so docs in the database, it is all real data captured from employees and I would like to include in my presentation. Whats the easiest way of cleaning these documents, or finding the rouge? My only idea was to create a view that contained every single field and the export to CSV and then import into the new copy. Would that work? or is there some way I could do this with an agent?
Just help me out here: you say the server replica works, you local one doesn't, right? Then why didn't you make a new local replica form the server??
In any case, you could do the following (or something equivalent):
- make a new view, with selection formula
SELECT !@SIsNull(QuoteValue)
- make a formula agent, that does
x:= @TextToNumber(QuoteValue);
Field QuoteValue:= @if(@IsNumber(QuoteValue);
@IsError(x); 0;
x);
""
In any case, you could do the following (or something equivalent):
- make a new view, with selection formula
SELECT !@SIsNull(QuoteValue)
- make a formula agent, that does
x:= @TextToNumber(QuoteValue);
Field QuoteValue:= @if(@IsNumber(QuoteValue);
@IsError(x); 0;
x);
""
Remember Design elements and documents are assigned NoteID's which are unique to the local database only, not accross replicas. I.E. a Note might have one NoteID in one database and another in the replica copy. But a lot of indexing functionality runs on NoteID sorting and hence your milage might vary from replica to replica.
And for g... sake don't mess with the server copy of the database. You don't want it to break as well, do you?
You have to doublecheck all occurances of the field in all documents and all design elements.
I also remember deleting Deletion Stubs was an additional task to perform back then.
This is not a list that allways works. It might even matter in which sequence you perform the different actions. E.G. experiment with deleting the FT-index at odd times (not to start with as I remember).
Oh, I also remember that the FT-index is bound to the UNK table.
Maybe doing gymnastics around the UNK issue will help you.
And for g... sake don't mess with the server copy of the database. You don't want it to break as well, do you?
You have to doublecheck all occurances of the field in all documents and all design elements.
I also remember deleting Deletion Stubs was an additional task to perform back then.
This is not a list that allways works. It might even matter in which sequence you perform the different actions. E.G. experiment with deleting the FT-index at odd times (not to start with as I remember).
Oh, I also remember that the FT-index is bound to the UNK table.
Maybe doing gymnastics around the UNK issue will help you.
Typo:
SELECT !@IsNull(QuoteValue)
Oh, and run that agent in the view you just created, with all documents selected. You could start with a few to see if nothing goes wrong. Hopefully, after that, it will be okay. If not, you might need to compact again, to get rid of the superfluous field definition.
Rouge? Rogue...
SELECT !@IsNull(QuoteValue)
Oh, and run that agent in the view you just created, with all documents selected. You could start with a few to see if nothing goes wrong. Hopefully, after that, it will be okay. If not, you might need to compact again, to get rid of the superfluous field definition.
Rouge? Rogue...
Before you compact, remove the FT index.
btw, I'd never use the currency data type. How do you think your application looks outside your country?
If I remember right, currency is Type Number, just with a visual twist to show the number according to curerncy settings, but the value stored is still an ordinary number.
Try to use
SELECT QuoteValue != "" & @TextToNumber(@Text( QuoteValue )) != QuoteValue
This way you would not run the agent against documents with values that are OK and keep replicationconflicts at minimum.
If I remember right, currency is Type Number, just with a visual twist to show the number according to curerncy settings, but the value stored is still an ordinary number.
Try to use
SELECT QuoteValue != "" & @TextToNumber(@Text( QuoteValue )) != QuoteValue
This way you would not run the agent against documents with values that are OK and keep replicationconflicts at minimum.
1) Who cares, for 100 documents in a demo database?
2) True, but...
3) if QuoteValue doesnt' contains a number-string, then @TextToNumber would generate an error and the Select would fail.
2) True, but...
3) if QuoteValue doesnt' contains a number-string, then @TextToNumber would generate an error and the Select would fail.
Maybe
Select !@IsNull(QuoteValue) & !@IsNumber(QuoteValue)
Select !@IsNull(QuoteValue) & !@IsNumber(QuoteValue)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
With only a hour to go before I set off, my quick solutuion has been:
I have created a new (design only) copy on the server and then created a local replica on my laptop from this new copy.
I then created one document on the local replica and created a local FT index. then I have copied and pasted the other 100 or so docs from the original database and updated the index.
For now I have a local FT index built on healthy field definitions.
ASKER
lol jogii2 I did just that freaky!
sorry I did not see you post beforehand.
Sorry guys none of those selection formulas return any docs, I can't work it out.
sorry I did not see you post beforehand.
Sorry guys none of those selection formulas return any docs, I can't work it out.
It works, so don't worry. Now it's presentation time, go for it!
ASKER
Thanks guys, it's time to go. I will close this and award points but may ask how best to deal with this problem when I return next week for some more points ;)
Thank you both a bunch, I really appreciate it!
scribla.
Thank you both a bunch, I really appreciate it!
scribla.