For info. I’ve had a dblookup view working fine for 3 years now, and since upgrading to V6.5.3 server its been playing up. Stopped working the other day, resaving the view seemed to fix it. Stopped again today and that didn’t work, the ‘fix’ was to change the “” in the formula indicating ‘server:db’ to “”:“” which I’ve never had to do before.
Hope this doesn’t mean I’ll have to go through every db for lookups and fix them.
Dave
Subject: @Dblookup - Intermittent fails after working for 3 years
For current db and server you were specifying the dbname and server all the time?
You should get into the habit of using “”:“” for currentdb and server. its much more friendly for copy/pasting later on.
As far as I know there is no known bug in 6.5.x on @Dblookup but then again they probably didnt expect currentdb and currentserver lookups to be specified explicitly and therefore probably didnt get tested throughly which is why you’re experiencing this prob now. So chances are, you will have to change all of them to “”:“” if the problem persists.
Good luck.
ST
Subject: RE: @Dblookup - Intermittent fails after working for 3 years
“” is what I always use for the current database. “”:“” just makes your formula longer. There’s no functional difference, so there’s no reason changing between them would make the formula start working. There’s something else going on. There is no need whatsoever to go thru all your applications making this change.
There’s no known problem with @DbLookup introduced in 6.5.3. Possibly the issue is that the amount of data in your database has grown so that the formula that calls @DbLookup occasionally ends up returning more then 64K of data, and the timing with the server upgrade is coincidental.
If you would give some details, e.g. what is your formula, how does it fail when it fails, how is the view sorted and how much data is in it, that might help to figure out what’s up.
Subject: RE: @Dblookup - Intermittent fails after working for 3 years
Hi Andre, thanks for your response.Yeah, I’ve never had an issue using “” for both the db and server, going back to V3, but that is honestly what I did to get it going again. Strange.
Not sure the 64k is an issue. The @Dblookup just grabs a copyright name field from the form, in this case holding the value ‘Berit Kruger-Johnsen’, and uses a lookup view which is sorted in the first column on that field, to get one unique document with this value, and get the value of one other text field, column 2, copyright details. In this case the actual details were:
'Full Notice © year Berit Kruger-Johnsen. Washington Green licensing
Abbreviated notice for back stamp
© year BKJ WGL with Enesco Ltd’
The lookup is activated by a button on a form:
FIELD PDBCopyrightDetails:=PDBCopyrightDetails;
tmp:= @If(
@IsError(
@DbLookup( "" : "NoCache" ;"" : "" ;"(Config-Copyright Notices)" ; PDBCopyright ; 2)
);
"Error finding details";
@DbLookup( "" : "NoCache" ;"" : "" ;"(Config-Copyright Notices)" ; PDBCopyright ; 2)
);
@SetField(“PDBCopyrightDetails”;tmp)
All I was getting was “Error finding details”, which meant the lookup failed, but for no reason.
Thanks for any help
Dave
Subject: RE: @Dblookup - Intermittent fails after working for 3 years
This lookup is failing for a reason, and to discover what the reason is you’re going to have to see the actual message. The reason you’re not seeing it now is that you’re trapping it and replacing it with your own text.
Try this formula (which in any case will execute in half the time of your original):
tmp := @DbLookup( “” : “NoCache” ; “” ;“(Config-Copyright Notices)” ; PDBCopyright ; 2);
FIELD PDBCopyrightDetails := @IfError(tmp; "Error finding details: " + @Text(tmp); tmp);
0
The NoCache is really unnecessary here, since the wording of copyright notices doesn’t change all that often. Removing it would further speed up the formula.
Since you have already said the problem is intermittent, I suggest that any apparent change in behavior from changing “” to “”:“” is due to chance rather than any operational difference. “” means the current database. “”:“” means the current database.
Subject: RE: @Dblookup - Intermittent fails after working for 3 years
First, this code has the classic performance problem of two NoCached lookups. You’re doing the lookup twice everytime this gets evaled.
Try…
FIELD PDBCopyrightDetails:=PDBCopyrightDetails;
tmp:= @DbLookup( “” : “NoCache” ;“” : “” ;“(Config-Copyright Notices)” ; PDBCopyright ; 2);
@SetField(“PDBCopyrightDetails”;@If(@IsError(tmp); @Prompt([OK]; “Warning”; @Text(tmp); tmp)
It’ll be faster and will actually tell you what’s wrong with the lookup.
Subject: RE: @Dblookup - Intermittent fails after working for 3 years
Seems to be working again for no reason.Thanks for the code reminder chaps. Its not that I didn’t know, I just copied/pasted from somewhere in the mists of time and being lazy, forgot to mod it. (As you do).
Suitably chastised…
Dave
Subject: RE: @Dblookup - Intermittent fails after working for 3 years
You didn’t quite understand what I was saying.
I said I was using “” to specify both the current db and server, NOT that I specified the current db and server by name. This is the norm, specifying “”:“” was optional.