Unexpected Lookup Behavior


#1

First of all, as a long-time Panorama (and OverVue) user, I wanted to thank Jim for the thousands of hours of effort that have gone into this new version. I am slowly wrapping my head around the new ways of doing things, and haven’t really begun using PanX as the main version, but have gotten much closer. This program has been a key part of my productivity for a long time, and it would be painful to give up my data processor. Surely, I am missing some key features, but expected a developing product, rather than a finished one. (I don’t know if I will ever get over the loss of the headphone jack, though…)

The questions: First, I have made something work, but fear that my ignorance may have caused me to do this the hard way, and wanted to ask if I was going about this correctly. I made a matrix object in a database of the most common payees I make payments to. This matrix happily displays info about the entries using the pass-through and DB Navigator. I have been trying to implement some lookups that reference the checkbook database holding the actual transactions. I want it to look up the last payment and payment date for the payee. I couldn’t get lookuplast to work (more later), so tried lookup, to see if lookuplast was just not implemented. When I use the name of the Payee field for the keydata (Payee, or «Payee» or exportcell(“Payee”), I get the expected value for the selected record, but repeated in all cells of the matrix. When I set up a formula for the matrix (just exportcell(Payee)), then reference it using the info function, suddenly the search works correctly and gives the correct value for each payee. So, the new (working) formula is:

pattern(lookup(“Chckbk”,“Pay To”,tabarray(info(“MatrixCellData”),1),“Debit”,0,0),"#.##")

Am I doing this wrong, or is this what you expect?

Part 2: When I change “lookup” to “lookuplast” in the formula above, it fails, saying “Expression contains an operand when an operator was expected”. The keyfield in the checkbook database being queried contains a space, and changing the “Pay To” to “PayTo” in the database being queried does fix lookuplast.
I really don’t need the space in the field name (I think this might be a legacy from OverVue), but will have to go through quite a few formulas to fix it. Looks like it’s time to learn about the new search functions…

I’ve just installed version 0.1.028. OS 10.10.5.
Thanks-
Rich


#2

I’m sorry. This was a bug reported in the lookup( function back in July.

At that time, I didn’t notice that the same fix was needed for the lookuplast(, lookupselected(, lookuplastselected( and table( functions. I have just made the necessary fix, so look for that to be in 0.1.029.

I think for now if you don’t want to rename the field you could probably use the superlookup( function

with the priority set to “last”.


#3

By the way, thank you for the nice comments, and you’re welcome :slight_smile:


#4

Thanks for the tip. Superlookup works great. I have to put chevrons around the target field name («Pay To»), but that was easy to do.
I’m still left with the question about why the formula

superlookup(“ChckbkFSB”,{«Pay To» = tabarray(info(“Matrixcelldata”),1)},“Debit”,“priority”,“last”

returns the value associated with that cell of the matrix, but

superlookup(“ChckbkFSB”,{«Pay To» = ««Payee»»},“Debit”,“priority”,“last”

returns only the value for the selected record (repeated for each cell). The matrix cell data is pulling from a matrix formula that has just one entry–the contents of the Payee field. In each case, the formula is feeding a text display object in the matrix frame (and thus the cells of the matrix).
If I’m thinking about this incorrectly, I’d be glad to learn more.
Thank you-


#5

Has the issue with lookuplast( been fixed yet? I use it within a given database in Pan 6 and it works, no problem. The same procedure does not work in Pan X for me, returning 0 instead of the last value of the requested record field. I am tracking stocks and input weekly prices and then use the lookuplast to find the value of the previous week’s number of shares.


#6

Yes, the bug reported with lookuplast( was fixed last November, it appears that this fix was done shortly after the earlier post. So if you are having a problem, it would appear to be a previously unreported problem, and I’ll need additional detail to resolve the issue. Ideally that would probably be sending me a copy of both the Panorama 6 and Panorama X databases that illustrate what is happening.


#7

Sent it along to you. Thanks for looking into this.


#8

Pete, I looked at your file, and this is a previously unreported bug. I’ve submitted a new bug report.


#9

I think lookuplast( still has a problem. I was doing the tutorial that creates a checkbook and I could not get it to work.


#10

All of the bugs mentioned on this thread have been fixed.

Using the lookuplast(( function as described on this tutorial page should work, as you can see from the screen recordings on the page.


#11

Oh yea, if only I had put the code in the right box. Sorry.