Quotes in lookupall Function


#1

Hello Everybody,

I’m trying to get a form that works perfectly in Pan 6 to work in Pan X. Here’s what my problem(s) appear to be:

1- The Pan X Help says that quotes are not necessary for the keyfield in the lookupall( function. But the function gives an error if I do not use {} quotes around the field name. The field name has chevrons surrounding it.
2- In Pan 6, when lookupall( finds a field with no entry, it returns a “no entry”, so the resulting array item is there, but empty. But Pan X eliminates the item and shortens the resulting array. That isn’t what is expected (by me), and results in a different order array than other arrays which are returning different fields. The arrays no longer line up correctly.

Is there a way to force lookupall( to return an empty item in the result array, like Pan 6 does?

Best regards,
Vic


#2
  1. I was not able to duplicate this problem. The lookupall functioned fine whether quotes were used around the key field name or not.

  2. I was able to confirm this problem, it is a bug. I’ve filed an issue:


#3

Hmm. Here is the formula in the TextDisplayObject.

lookupall("",{«Payee Name»},g_Payee_Name,«Location»,¶)

If I remove the { and } from the second parameter, it returns “Expression contains an operand when an operator was expected”. Adding back the {} returns the array.

By the way, Jim, as an experiment I replaced the above formula with:

arrayselectedbuild(¶,"",{«Location»},{«Payee Name» = g_Payee_Name})

This returned an array as expected, but when I change the value of the variable g_Payee_Name (which is done by a pop-up button on the same form), the TextDisplayObject DID NOT UPDATE. It remained from the last array. By going to Graphics Mode, clicking on the object, and hitting Return in the Formula window, the correct array displays. But changing the g_Payee_Name variable again does not change the object output. They are not synchronized somehow.

It must be frustrating for you to get these Emails seeming to complain about things not working. But I assure you we are all pulling for you. These aren’t complaints.

Best regards,
Vic


#4

I don’t understand how your example works at all. It should NOT work if you both quote it and include chevrons. That is telling Panorama that the field name itself contains chevrons – it doesn’t, does it?

What happens if you do this?

lookupall("",{Payee Name},g_Payee_Name,«Location»,¶)

To me, that is exactly as expected. Let me see if I can explain to you why that is.

When checking to see if a form object needs to be updated, it looks at the formula for the object. In this case, the formula it sees is:

arrayselectedbuild(¶,"",{«Location»},{some text value})

It doesn’t know about g_Payee_Name, because that is “hidden” inside the text value. The entire formula «Payee Name» = g_Payee_Name is hidden as far as the form update scanner is concerned. It doesn’t know this is a formula, it just considers it to be some text that doesn’t concern it.

All is not lost, however, you just have to put this variable into the formula in a way that it is not hidden from the scanner, but doesn’t display. Here’s how to do it.

arrayselectedbuild(¶,"",{«Location»},{«Payee Name» = g_Payee_Name})+ignore("", g_Payee_Name)

With this revised formula, the Text Display object will update when g_Payee_Name changes. You have to use this technique whenever you have a formula that is embedded inside another formula as a text constant. I hope that makes sense.

Obviously it would be great if Panorama X had no more bugs. But that’s not realistic, and I certainly am not going to shoot the messenger when one is reported. In fact, I’m generally delighted when a clear bug report comes in that I can duplicate and take action on.


#5

Wow. I was not aware of the ignore( function. But I have to confess, I don’t really understand the formula you show. You have appended a second function to the arrayselected( function. It obviously works, but …?

Jim, for people like yourself, Dave, Gary, et al, this must seem trivial. But for Sunday coders like myself, this is tough. I’ve come up with some quite sophisticated algorithms, e.g. to calculate thermodynamic properties or physical properties of materials. But coding those algorithms is something else entirely. The above ignore method would be awkward at best for complicated conditionals.

Does the following sound like a hare-brained idea?

1- Panorama must already have a list of variables somewhere in memory, with their current values. Extend that list to have current and previous values.

2- When the “form update scanner” checks an object, have it strip the quotes (i.e. look inside the quoted term), see if there is a variable, and compare that variable value with the previous value. If the value has changed, “flag” the object.

3- When done checking all objects, activate and invoke the formula of all “flagged” objects.

I suppose this must be too simplistic, or you would have already thought of it. And there must be a very compelling need to enclose a formula in quotes or would not have done it. But this is a major problem, for me anyway.


#6

Vic, the appended function is outside the quoted formula and thus is visible to the update scanner. The ignore( function does not do anything in this use except to make the variable g_Payee_Name available to the scanner. I believe you could accomplish the same thing by adding the text funnel g_Payee_Name[1,0] in place of the ignore( function. the text funnel g_Payee_Name[1,0] will return nothing but shold be recognized by the update scanner and thus any other occurrences of the variable it finds on the form will be updated.

The point is that you are in fact not actually adding anything to your quoted formula except finding a way to harmlessly include your variable outside the actual quotes.


#7

I got it. Thanks, Jim.

Best regards,
Vic


#8

This would require the update scanner to know the purpose of every parameter of every function Panorama has. It would need to know if the quotes contained the text of a formula, or if it was just some ordinary text. Even then it could miss some, because there are other ways of generating the text of that formula, besides just putting it in quotes.

As a general rule, only the function that uses a parameter knows what it is for, and that function doesn’t know what use will be made of the result it returns. A program that tries to make everything aware of what everything else is doing would rapidly devolve into a hopeless tangle.


#9

Dave & Gary, you did a better job of explaining this than I would have. Thanks!


#10

Hello Jim (and Dave & Gary),

Allow me to report further on my testing of lookupall(.

1- the formula

lookupall("",{«Payee Name»},g_Payee_Name,«Location»,¶)

will NOT work if the { and } are missing from the second term. Also, your suggestion of using {Payee Name} for the second term also does NOT work. It must be written as above to work properly.

2 - With the above formula in the TestDisplayObject, the object updates properly when g_Payee_Name is changed (of course), AND updates if a new record is added with the same g_Payee_Name value. That is what I would expect.

But, if the formula instead is

arrayselectedbuild(¶,"",{«Description»},{«Payee Name» = g_Payee_Name})+ignore("",g_Payee_Name)

then, the object updates properly when g_Payee_Name is changed (note your clever use of the ignore( function), but it does NOT update if a new record is added with the same g_Payee_Name. You have to re-set the variable to get the second instance to update. Is that what you would expect?

Best regards,
Vic


#11

I can confirm this. With the space in the name of the field, both the chevrons and the braces, (or other quoting characters) are needed. If I change the name of the field to Payee, then quotes, chevrons, both, or neither all work.

That is what I would expect. None of the field names appear outside the quotes in this formula, and adding a record only adds values to fields. In the other formula, the Location field appears outside the quotes, and that is what is triggering the update when a record is added. You could include the name of one of your fields in the ignore( function.

arrayselectedbuild(¶,"",{«Description»},{«Payee Name» = g_Payee_Name})+ignore("",g_Payee_Name+Description)

#12

Hello Dave,

That ignore( function really comes in handy, doesn’t it? I hadn’t realized that field names, as well as variables, could be included in the function.

But doesn’t this raise an obvious point. The average user might not be aware of the “non-update” format of his/her formula. Or might not know or have forgotten about the ignore( function.

Would it be so difficult for the object scanner to look inside all formula quotes, see what field names and variables are included, and automatically add an ignore( function for those items to its internal checking procedure. Yes, there might be instances where an ignore( is added where not really needed. But having several unneeded object updates is still better than NOT having a NEEDED update.

Dave, take a Sunday off now and then! But what would the forum do without you and others like you?

Best regards,
Vic


#13

The whole point is that the function ignores the second parameter. The value it returns is the value of the first parameter.The second parameter is still evaluated, because all function parameters are evaluated before the function is called, and only the function itself knows that it will be ignored.

The function was actually created to be used with the assign( function, which has the side effect of assigning its value to a field or variable. Often the formula that contains the assign( function has no use for the value that function returns. It’s just in there for the side effect. The ignore( function was created to keep that function from affecting the value of the formula. The assign( function does its thing when the second parameter is evaluated, but then the parameter is ignored.

That second parameter can do anything, it can even result in an error, and it will have no effect on the value returned by ignore(.

Yes. It would be incredibly difficult. Quotes are basically a signal to a formula parser that their content is of no concern to it. Everything is to be taken as text. If it looks like a field, variable, operator, or function, that’s just coincidence.