Defaulting lookups to original value


#1

In Panorama 6 I could code the following:
Field myField
FormulaFill lookupselected(database, keyfield, keydata, datafield, myField, level)
If there were no match it would default back to the pre-lookup value.
The exact code that worked in Pan6 doesn’t quite in PanX. Matches are lookedup up properly but non-matches become empty rather than their preceding value. In my particular case I was looking up a date field into a field named “Last Seen”. In Pan6 putting «Last Seen» into the default slot worked as intended. In PanX that syntax failed as above. I tried “Last Seen”, «», {«Last Seen»} and ««Last Seen»» with either the same failure or a field/variable not found error. I also tried Superlookup with appropriate options without success.

I wonder whether this is another manifestation of the partially fixed grabdata( problem, #703, or whether I just haven’t tried the proper syntax. If the former I can code around it by making a duplicate of «Last Seen» and overwriting a filled fixed default date from that.


#2

The default parameter IMHO is supposed to return a fixed value or string in case the lookup finds no match. So I would use 0 or “” as default.

You can easily combine that with your goal to get the original contents of “myField” if the lookup fails by using a formula like this:

Field myField
FormulaFill ?(lookupselected(database, keyfield, keydata, datafield, 0, level)=0;myField;lookupselected(database, keyfield, keydata, datafield, 0, level))

#3

I don’t know if it’s another manifestation, but the problems do look similar. It appears to be evaluating that parameter in the context of the other database, rather than the active database. When I tried it in an assignment statement, instead of a FormulaFill, I got an error that the field or variable did not exist. It worked if I used a fieldvalue( function for that parameter.

FormulaFill lookupselected(database, keyfield, keydata, datafield, fieldvalue("active database","myField"), level)

#4

I think this is not related to the #703 issue, so I have opened a new ticket for this problem.