I’ve have a database that looks up and pulls info from another database. It’s been working great (thanks to help from everyone at this forum) but all of a sudden today the scripts stopped working, and give the error:
As far as I can tell, nothing has changed in the main database (field names all seem to match.) I’ve run these scripts around 20 times over the past weeks with no issues - so I’m afraid somehow, somewhere, I messed something up.
Can anyone point me in a good direction so I can better diagnose this?
If you don’t specify a default value for the lookup, this error value is what the function will return if there is no match. So what is happening is that for one or more of the records in the PASearchDatabase2020 database, there is no corresponding record number in the PATrackerDatabase2020 database. Apparently that never happened for you before. Perhaps there is a data entry error so a record number was entered incorrectly, or a record was deleted. Basically this is not a code error, but a data error. You want to change your code to anticipate this possibility.
For an application like a formulafill, you don’t ever want to encounter an error value. So I would suggest adding a default value to your two lookup( functions. Then after the formulafill is done you can look to see if the default value appears, and you’ll know which records didn’t have a matching record.
Older versions of Panorama used to always require a default value. In Panorama X this parameter became optional, and if the default is left off the parameter list, the function will return an error if there is no match. So in this case, by making Panorama X more flexible than earlier versions, a new possibility of errors was opened up. However, there are situations where forcing an error is the right way to go, so I think in general this flexibility is a good thing. But not when combined with the formulafill statement.
Thank you so much @admin for your continued amazing support. I really appreciate it. I’m on the road, but tonight or tomorrow I will add a default value to all the scripts, and look to see which records are causing trouble. Thanks so much for the insight - I wasn’t aware (or had forgotten!) there was such a thing as a “default” value.
Does the default value have to be a fixed value or could it be a field, variable or formula that evaluates to the the correct kind of data for that lookup. I was able to do the latter in Pan6, but don’t recall getting it to work in PanX. I presume the same answer would apply to the carried forward variants of Lookup() but the answer might be different for the new to PanX Superlookup()'s default value option.
I haven’t had occasion to try such recently and was able to code around the lack in the past, so I can’t presently point to an example. The Relational Workshop only provides error, “”, zero or Today as default value options, although something else could certainly be inserted into what it outputs.
If you convert to using a formula instead of just the field name it will work. So instead of simply A as the default you could use fieldvalue(A) or even A+"" (or A+0 if it is a numeric field) and it will function correctly.
Jim, adding a default value lead me to the answer quickly - some of the record numbers in the search db had gone out of whack. Once I fixed that, everything went back to normal. Thanks for the tips and solution - I thought maybe I did something irreversible. Whew!
This is exactly the same as in earlier versions, any formula is allowed. Even if you are using a fixed value, like "", that is actually a formula, though a very simple one.
One thing to keep in mind though is that if you use a field name in the formula, it must be a field in the current database, not the database that is being looked up from. Again, this is no different than it ever was. This can be very handy if you are assigning the lookup result to a field, and if the lookup( doesn’t match you want the result to be whatever the current field value is (i.e. no change if the lookup fails). Again, this is no different in Panorama X than in earlier versions.
Thanks Gary, I have verified this. This was not the case in Panorama 6, so this is a bug, which I have written up. @JohnB maybe this is the problem you ran into, if so, for now you can use the workaround Gary has suggested.
