FormulaFill Failure

Hello Everybody,

PanX and I do not get along. I’m sure it’s my fault, but still …

A procedure which works flawlessly in Pan 6 gives the wrong result in PanX. After much investigation, I found the failure point in the code following a SELECT command. Here it is:

    If info("Empty")
        FormulaFill ?(«Actual Distribution» > 0, «», 0)
        Total
        message "A"   STOP
    else
        etc.

The field «Actual Distribution» has positive and negative values. But the FormulaFill function doesn’t change any values. At the “message” line, all values (positive and negative) are unchanged. In Pan6, all negative values are replaced with zero, which is what should happen.

Field Actual Distribution is numeric floating in each Pan version, and formatted identically. I am running OS 10.13.6, with PanX 10.2.0.b32.

Does anyone know what might be the problem? The Pan6 code was “copied” and “pasted” into PanX. There were some syntax corrections necessary in other areas of code, but not is this section of code.

Regards,
Vic

You said this is extracted, so it may be covered earlier in the procedure, but this much doesn’t show that you’ve activated «Actual Distribution» as the target field of the FormulaFill.

You also didn’t indicate it Total is working. Or, for that matter, although it’s implied in your post is the message coming up to indicate the a selection was empty?

One other thought, have you tried just applying the formula manually to see what result you get?

message “A”

results in a message displaying the letter A.

«» 

is the current field whatever it is.

So I would suggest some changes to your code:

    If info("Empty")
    FormulaFill ?(«Actual Distribution» > 0, «Actual Distribution», 0)
    Total
    message «Actual Distribution»   STOP
else
    etc.

Thank you for your suggestions.

Further research on my part shows the culprit is the SELECT command. Try this out on a database of your own which has a numeric floating field with positive and negative values:

Field «yourfield»   //with positive/negative values

Select «Acct No» = "Merry Christmas" //make the select empty

If info("Empty")
    formulafill ?(«yourfield» > 0,«yourfield», 0)
endif

RTN

PanX will not execute the FormulaFill command.

Regards,
Vic

My example has a typo (your field instead of yourfield).

But the result is the same.

In both Panorama 6, and Panorama X, if a selection fails, then statements that make massive changes to the database are suppressed. In Panorama 6, a statement that contained info("Empty") would cancel that feature, because it would then be assumed that the procedure knows that the selection failed, and it is doing it with eyes wide open. That doesn’t seem to be true in Panorama X, but I haven’t found any documentation that it is deliberate.

Another difference is that Panorama X would keep the current selection, when a select fails, but Panorama X will effectively select all when the selection fails. If you expect all of the records to be visible when the selection fails, you could write it this way.

Field «yourfield»   //with positive/negative values

Select «Acct No» = "Merry Christmas" //make the select empty

If info("Empty")
    selectall
    formulafill ?(«yourfield» > 0,«yourfield», 0)
endif

RTN

After the successful SelectAll, the FormulaFill won’t be suppressed.

Excellent, Dave. The extra (and hopefully unnecessary) selectall fixed the problem.

I knew I could count on you. This feature should be documented.

Best regards,

This post illustrates that when you post an excerpt of code, you need to be very careful in choosing the excerpt.

I think the excerpt you have chosen makes no sense on it’s own. Why on earth do you want to remove negative numbers only when the selection is empty? It seems like the one should not be connected to the other. I would suggest that your code should probably be:

selectall
field yourfield
formulafill ?(yourfield>0,yourfield,0)
select ...
... do more stuff

Also note that there is a simpler way to change negative numbers to zero.

field yourfield
formulafill max(yourfield,0)

Here is the documentation from the Panorama 6 Formulas & Programming PDF file. The second paragraph describes the behavior Dave is talking about. It does appear that this paragraph didn’t make it into the Panorama X documentation.

Did you actually try this? That’s not what the documentation says. I think that Panorama 6 and X work exactly the same in this regard – only another select or selectall cancels the empty selection.

My guess is that Vic’s code never worked correctly in Panorama 6, it just wasn’t ever noticed. Just because code is old doesn’t mean it is correct. If I were @vicelly I would carefully review this entire procedure, perhaps rewrite it from scratch.

I hadn’t tried it. I simply remembered it from a discussion on the QNA. I just now tried this, and the FormulaFill was not suppressed.

Select false()

if info("Empty")
    FormulaFill ?(«» < 0, 0, «»)
endif

If I remove the if and endif statements, the FormulaFill is suppressed.

Impressive! :clap:

Sorry, Jim. That is absolutely incorrect!

There is an important reason that I need to change negative numbers to zero, but keep positive values unaltered when the select is empty.

The entire procedure summarizes distributions “from” an IRA account, and repeats that record for the “to” account. So initially there are two records in the database for each distribution. A separate check makes sure the amounts in the “From” account (positive) match the amounts in the “To” account (negative). A Total command showing zero in the summary record ensures that. That was the reason for the SELECT command of summary records different from zero.

It isn’t helpful to say the excerpt makes no sense. The snippet of code I included “makes sense”. It just isn’t obvious without knowing what the entire procedure is doing.

PanX and Pan6 DO NOT work the same way in this instance. Try it and see.

That’s what I meant by “doesn’t make sense”

Since I don’t know what your selection is, no one on the forum has any way of understanding that reason.

In any case, if that is really the case then I would think it would be much clearer programming practice to code it like this:

select something or other
if info("empty")
    select «Actual Distribution» < 0
    formulafill 0
    selectall
else
    ... etc.

I think this code makes it much clearer exactly what is going on, and it makes it very explicit what records are being changed by the formulafill.

I made that comment before Dave explained the undocumented behavior of the info(“empty”) function in Panorama 6. (I did check further, and as far as I can see there is no mention of this in the Panorama 6 documentation. Maybe it was in the Panorama 2 documentation, I don’t have a copy of that handy.)

It’s difficult enough to try to make the documented features of Panorama X work the same as Panorama 6. I make no claim that every undocumented behavior of Panorama X works the same as Panorama 6, and that is not a goal.

I’ve added a new help page to cover this topic, and I even did some rewriting and added more examples:

This new page is now explicitly mentioned and linked to on the select and selectwithin help pages.