Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

relational operators not supported?

Posted on 2005-04-20
19
Medium Priority
?
1,333 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:scribla
  • 10
  • 5
  • 4
19 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13831655
Can you give the essential parts of the code? And what are the Notes and Domino versions of PC and server?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13831673
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?
0
 

Expert Comment

by:jogii2
ID: 13831688
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:scribla
ID: 13831765
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?

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13831799
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.
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 1000 total points
ID: 13831810
All field values for the field QuoteValue have to have the same definition. If, some years ago, QuoteValue used to have a different definition in the form, and one document was created with that form, the type of that QuoteValue item is different from the current items. FtSearch doesn't accept taht.
0
 
LVL 2

Author Comment

by:scribla
ID: 13831897
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?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13831940
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); QuoteValue;
                                               @IsError(x); 0;
                                                x);
    ""
0
 

Expert Comment

by:jogii2
ID: 13831951
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.

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13831960
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...
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13831998
Before you compact, remove the FT index.
0
 

Expert Comment

by:jogii2
ID: 13832012
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.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13832049
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.

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13832056
Maybe
    Select !@IsNull(QuoteValue) & !@IsNumber(QuoteValue)
0
 

Accepted Solution

by:
jogii2 earned 1000 total points
ID: 13832090
>sjef: Right, @Error migth disturb the selection. Your last SELECTION is better :-)

if it's a demo db do whatever neccesary, but then why create a replica? DBCopy would be ok then.
Leaves me to another idea:
Try to create a dbcopy without any documents.
Create 1 document.
FT-Index the database
Examine the result
If ok, copy/paste the rest of the documents
0
 
LVL 2

Author Comment

by:scribla
ID: 13832098

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.
0
 
LVL 2

Author Comment

by:scribla
ID: 13832106
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.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13832124
It works, so don't worry. Now it's presentation time, go for it!
0
 
LVL 2

Author Comment

by:scribla
ID: 13832139
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month10 days, 2 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question