Empty Select selects all

I’ve been working with an understanding about Handling Empty Selections regarding the results of an empty SelectWithin and have resorted to using CheckEmptySelection with every SelectWithin.

Today while troubleshooting a procedure carried over from Panorama 6, I found that an empty Select was also selecting all records, which was unexpected. It can be handled by the same process as an empty SelectWithin but I had expected whatever was already selected would remain so and an info(“Empty”) would handle things as desired.

The Help file for Select backs up my belief: "Note: If no records match the Boolean formula, the selection will remain unchanged. "

And under Handling Empty Selections: “If none of the records in the database match the formula, Panorama does nothing. It’s as if the select never happened. Whatever records were visible before remain visible after.”

If I select records with a Select statement in a procedure and find some records, then do another select statement in a procedure that does not find any records, then all records will be selected.

BUT, if I use the Find/Select dialog to perform the searches, with the first search finding records and the second search not finding records, the result is the set of records found in the first search. This is true whether the second search is a New Selection or a Subset of current selection. In other words, performing the search in a procedure does not act the same as the search conducted in the Find/Select dialog.

The code for the Find/Select Dialog (_FindSelectLib.FINDSELECTDIALOG, lines 182-193 and 226-232) includes code that preserves the existing selection when the second selection is empty, i.e. startdatabasechange… followed by a CheckEmptySelection.

At first glance, the help page statements that Jim cites ("Note: If no records match the Boolean formula, the selection will remain unchanged. ") do not seem correct to me when using a procedure. On the positive side, I thought that an empty selection will always result in all records being selected; good thing I didn’t know what the Help page said on this point.

I think the documentation was correct for earlier versions of Panorama, but it is wrong for Panorama X. If no records match, Panorama X will always select ALL records.

You may recall that Panorama 6 had a special multi-undo facility for selects - you could undo the last 16 selects. No other operation offered more than one level of undo. There were extra bytes reserved in each record to make this possible. These extra bytes were also used to allow various select statements to retain the previous selection if no records matched.

Panorama X now has a general purpose system for multi-level undo, so the extra bytes for undoing selections are no longer included in each record. This makes databases smaller.

There is one way that Panorama could continue to work as it did before – it could do each selection twice. Once as a dry run to see if any records matched, and then a second time to actually do the selection. This would allow empty selects to work as they did before, but they would make every selection take twice as long. I don’t think that is a worthwhile tradeoff, especially since there are probably not very many people using selectwithin in a procedure, but this overhead would impact all users every time a select was done.

Going forward, I have revised the help pages Jim cited. Not only did I reword it, but I also made note of the fact that this is a change from Panorama 6.

Clearly good practice calls for adjusting every use of SelectWithin or SelectAdditional to something like:

startdatabasechange "allrecords","Uh Oh"
selectwithin somefield = somevalue

That would now include any Select statement that subsequently relies on the pre-existing selection. In at least one scenario, I’m counting on an empty selection if everything is okay but need to take steps if it’s not empty. Since the desired result will be with all records selected and always need to be undone, possibly an array could be built as a faster test for those records.

Under startdatabasechange, the Help file states:

You can use the startdatabasechange statement to fix that in stand-alone databases. (It does not work in shared databases.).

Since that’s exactly what I’m working on, how should those scenarios be handled? A selection isn’t changing data, and selections are local. So does this only apply if a data change is involved versus selecting? Will the use of
serverupdate "off" resolve anything?

Once I have the desired selections made, I do want to change data on those records and would expect that serverupdate "on" would permit that.

It would be ok to use startdatabasechange with a shared database if all you are doing is selections. That particular statement is referring to operations that modify the database, like sorting or fills. So the checkemptyselection approach should work with a shared database.

However, I think I might consider a different approach.

if aggregate({1},{count},{info("visible") and (somefield = somevalue)})
    selectwithin somefield = somevalue
    ... selection would have failed, but we didn't do it so previous selection is still in place

Note that I haven’t tested this code so there could be typos, but the general approach will definitely work.

Actually, here’s another variation that I think might be significantly faster in most circumstances (assuming that the number of records already selected is low).

if arrayselectedbuild(cr(),"",{+},{somefield = somevalue}) <> ""
    selectwithin somfield = somevalue
    ... selection would have failed, but we didn't do it so previous selection is still in place

This change from Pan6 to PanX behavior may account for some of my occasional surprise results. My old Pan6 code included many Select SelectWithin sets and even Select SelectWithin SelectWithin sets. With complex boolean criteria the code was easier to read that way. In rewriting my code for PanX I didn’t consider this change in behavior. Cases where data infrequently produces empty selections at the first Select may not have shown up in testing.

I’d forgotten about the checkemptyselection statement, so checked its help and then its code via View Search. I did fairly often use if info("empty") (which starts it) or ifselect tests so may be ok, but should scan my code with this in mind.

checkemptyselection code is short and straightforward so I, or others, could easily tweak it if desired. One possible change I throw out to Jim for consideration would be adding an optional parameter to checkemptyselection, which if present would be appended to its nsnotify notification. In a lengthy, unmonitored, procedure one might want multiple checkemptyselection statements and want to know which one had been triggered.

A footnote of sorts… every time checkemptyselection is invoked by an empty selection, the computer chimes. In one procedure, if all goes as intended, there are five chimes in close succession. It’s certainly no disaster, but if the sound could be suppressed that would be nice.

checkemptyselection invokes nsnotify to notify you there was an empty selection. By default that produces a sound. Help for nsnotify provides options how to change or silence that sound. In principle you could so modify PanX’s code for checkemptyselection. Or you could write your own custom statement for a silenced version. Turning off system sound volume might work, but IIRC might substitute screen flashes. Turning off notifications at the system might silence this, but you might miss them.

All valid suggestions, but as the procedure will be running on a number of others’ computers it would ideally be silenced by default.

You can run a little code that will turn off the sound for the nsnotify statement used in the checkemptyselection procedure. I don’t know how feasible this would be to implement across shared datbases but here it is in case it might be usable. This would have to be executed every time there is a new version of Panorama X unless Panorama X changes this code in the update. I have also included a trap so that the “SOUND” addition will not be added again if this code is run a second time. This way the code could be in an .Initialize procedure and not cause issues if run multiple time.

let procText=getproceduretext("_FindSelectLib", "CHECKEMPTYSELECTION")
If procText notcontains {"SOUND"}
    setprocedureoptions "_FindSelectLib", "CHECKEMPTYSELECTION", "SOURCE", procText

You can adjust Notifications and their behaviour on a per-app basis in System Settings > Notifications > App Notifications.

Trying adding this to your procedure:

    if info("empty")
        nsnotify "No records selected!","TEXT","Reverted to previously selected records.","Sound",""
        sendaction "undo:"

And then instead of using PanX’s built in custom statement, checkemptyselection use


If Callwithin doesn’t fit the structure of your code use a different variety of Call or Farcall and stash its code appropriately within your other code. It’s identical to the code for checkemptyselection except for a different name, silencing the nsnotify and the final return required for a procedure being called.

I suspect the same could be accomplished with a custom statement, but I have little experience with them and none with shared databases so may be overlooking some limitation. If they’re running your code, I’d think adding this to it should work. When you want an audio notification just use checkemptyselection normally.

The next version of Panorama will have two new statements:

Note that these statements will be slightly slower than select and selectwithin, but the difference will be negligible for small to medium size databases.

If you can’t wait for the next release, here’s how I did it. This is for selectwithin.

let _qformula = {somefield = somevalue} <-- put your formula here
if superlookup("",_qformula,{true()},"default",false(),"selected",true())
    executelocal {selectwithin }+_qformula
    // selection failed, but previous selection is untouched

I used superlookup( instead of aggregate( or arraybuild( because it will stop immediately if it finds a match, so it will be faster. I didn’t think of that yesterday.

For select, make these slight tweaks to the 2nd and 3rd line:

if superlookup("",_qformula,{true()},"default",false())
    executelocal {select }+_qformula

When you get the next release, some of you may look at the code for these new statements and notice that it is much more complicated than what I listed above:

let _qformula = _RawParameters
letglobal _qformulaVariables = formulavariables(_qformula)
if _qformulaVariables<>""
    _qformulaVariables = arrayboth(_qformulaVariables,info("localvariables"),cr())
    letglobal _activeVariableValues = dictionaryfromvariables(_qformulaVariables)
    setlocalsfromdictionary _activeVariableValues   
    undefine _activeVariableValues
undefine _qformulaVariables 
if superlookup("",_qformula,{true()},"default",false(),"selected",true())
    executelocal {selectwithin }+_qformula
    setdatabaseoptions "","empty",true()

All this extra complexity is just so that the custom statement will work if your formula uses local variables!

I was going to post a thanks to all the suggestions that were leading to a useful custom statement. That’s one of the great things about this forum.

But now I’ll hold off for the next version of Pan X since it’s under control at the moment and the client’s use isn’t imminent.

So an extra thanks to Jim Rea. That’s another of the great things about this forum.

1 Like

The plot thickens…

On the same procedure that lead to my original post in this thread, I have a SelectWithin that takes place to see if any records within an already selected set fit a situation. I’ve got programming in place to handle the result whether the SelectWithin succeeds or fails. In spite of that, subsequent FormulaFills are skipped if the procedure continues.

I’ve tried workarounds, such as info(“Empty”) = -1 but my FormulaFills fail to run until a few lines later when a GroupUp resumes actual activity.

Field "ufText"
FormulaFill lookup("Contacts",ID,CustID,Paid,"",0)
startdatabasechange "allrecords","reset"
SelectWithin ufText = ""
If info("Empty") = 0    ;issues found
    blah, blah, blah...

Field xyzzyx FormulaFill "Some Formula"

That’s exactly how it should work. It will continue to skip fills until another selection is done. Panorama isn’t smart enough to realize that the undo operation (used by checkemptyselection) is effectively doing another selection. (Note that checkemptyselection was not designed for use with a following info(“empty”) test, you’ll see that the documentation does not show this usage. I never anticipated the use case you are trying to implement.)

What you really need is the new safeselect statements that will be in the next release. Even then, you would still need to tell Panorama that the selection is no longer empty, which you would do with this code:

setdatabaseoptions "","empty",false()

But guess what – in the version of Panorama you currently have, the setdatabaseoptions statement doesn’t have an “empty” option - I just added this when creating the safeselect statements. So you really will have to wait. (Because I’ve been making major changes to Panorama X over the past 3 months, I cannot quickly release a new version without significant additional testing. I don’t think I’ve broken anything significant, but simply releasing today’s development version would be ill advised. It won’t be months and months, but it may be a few weeks before the next release comes out.)

While you are thinking about and testing these concepts consider whether a


statement might be useful for similar issues. I presume its “safe-less stem” could select none and cause such. You might think of yet more statements whose logic could unexpectedly select no records then automatically select all. Making more “safe” statements more may or may not be straightforward or even possible, but you could at least document their limitations.

Dreaming of wrapped boxes under the PanX future versIon tree…

To be clear, the change I mentioned earlier in this thread was done and dusted. I was no longer thinking about or testing it. Once an item is done, I almost always put it completely out of my mind to make room for the next thing!

It’s not really clear to me that there ever would be a practical use for a safe version of the selectrelated or selectrecordsrelatedto statements, but I pondered this and I think came up with a nice solution that allows this functionality to be provided without requiring additional statements, and also now allows these statements to select within or select additional. I also changed the way Undo works with the selectrelated statement. If you’re interested you can click on the links to read the revised documentation.

Oh, I hope I can come up with something a lot nicer than that!

Very nice. Thank you.

Feeling like a bit of a thorn here, and not positive this post is necessarily tied to this thread, but…

In the procedure that started all of this I have a few selectwithin statements, each now updated per discussions here. It’s a fairly long but important procedure used to verify some critical data.

The procedure now runs as desired and properly handles the results whether an empty select or selectwithin is encountered or not. That is, it runs as desired as long as I keep my hands off the computer while it runs.

For instance, if I click on the Terminal window in order to watch the procedure’s progress, the very first selectwithin has errors. NSNotify activates although it shouldn’t and then a SendAction error is announced.

Screenshot 2023-08-14 at 12.11.11 PM

If I let it get past this point, before I do something else, the next selectwithin generates the same error.

As long as I leave it alone for the couple of minutes this procedure requires, all is well and I can run it over and over without any problems. I’ve encountered similar issues with other procedures. My advice to a couple of clients has become, “when a procedure is running - don’t touch the computer.”