How to run a procedure on all records for a field

Panorama X
1

This is so straight-forward that it’s embarassing that I’ve not figured this out, but I’m new to PanoramaX.

Here’s a simplified case of what I’m doing. I want to fill in “field3” based on other fields (“type” and “field2”). Since I want to fill in “field3” for all records, I think I want to use Morph. But, it’s a bit complicated so I put the calculation in a procedure. Here is my procedure “calc_3”:

local result
if «type»="a"
    result=«field2»*200
elseif «type»="b"
    result=«field2»*17
else
    result=0
endif
functionvalue result

When I try to Morph to fill in “field3” I’m doing:

click “field3” -> click Morph -> Morph Current Field…
use “Start with Formula” and fill in formula:

call("","calc_3")

But the After result never fills in. By altering the procedure, I can get it to fill in “field3” one record at a time (not using Morph), but that’s not much help in this case (over 250,000 records).

Suggestions?

2

You have to enter a formula there; you can’t call a procedure. Formulas contain functions only, no procedure statements.
Try this formula:

?(type="a",field2*200,?(type="b",field2*17,0))
3

This isn’t really true. The exact point of the call( function is to allow procedure code to be used in a formula. I think @danbolling has done this correctly, so the question remains, why didn’t it work?

I think the answer is the first parameter of the call( function, which specifies the database. Dan has left this empty, so that it specifies the current database. I think the problem is that while the Morph dialog is open and your looking at the preview of the formula (the After column in the dialog), the current database is not Dan’s database, but an internal database that Panorama uses to implement the Morph dialog. So the call( function fails because it can’t find the calc_3 procedure in Panorama’s _MorphFieldLib database. This is really a bit of a bug on Panorama’s part, it should use the user’s database as the default when previewing a formula. I’ve gone ahead and added this to the issue tracker.

If Dan had gone ahead and pressed the Morph button, I think it would have worked correctly, it’s just the preview that doesn’t work. However, I don’t blame him for not proceeding to press the Morph button – in his shoes I probably wouldn’t have either.

So, I think if Dan’s formula had been modified to explicitly specify the database, it would have worked:

call("dan's database","calc_3")

Or, it also would have worked if he had written a short procedure instead of using the Morph Current Field dialog, like this:

formulafill call("","calc_3")

All this being said, I think @KJM has the best answer here, using the ?( function allows this to be done without using any procedure code and will be significantly faster. If the database is small that won’t matter, but for a large database this could make a difference. If you are not familiar with the ?( function, it is similar to the if statement but within a formula instead of within procedure code.

Or, another way to do this particular example is with the switch( function, like this:

switch(type,"a",field2*200,"b",field2*17,0)

Some of you may not be aware of this function since it is new to Panorama X.

4

The help file states this regarding the call( function:

The call( function (and execute( function) allow you to perform procedure statements anywhere a formula is allowed, including in form objects, formulafill, etc. There are some significant restrictions that the procedure called by this function must follow:

  • The procedure cannot have any database-related side effects. In other words, it cannot modify any database fields (variables are ok).
5

Gary, are you suggesting that the documentation says Dan’s code won’t work? Dan’s code doesn’t modify a database field, it only modifies a local variable. The modification of the database is happening outside of the procedure code, so that is A-OK.

6

I was only suggesting that may have been where Kurt got the idea for his original comment. I know I was confused by how this restriction might play into the situation when I first looked at the help documentation. But then I often misinterpret things.

7

Thanks for the replies Kurt, Gary, and Jim! I’m learning a lot. It worked, well, sort of.

When I filled in the Morph Current Field -> Start with Formula … with

call("example_db1","calc_3")

It still did not fill in the After result, but it did fill in all the records on “field3” when I clicked the “Morph” button. Since this worked in my example, I tried it on my client database and it worked there too.

It’s great to know about the switch( and ?( (ifelse) functions, but I did not try them since the Morph worked to fill in the field. The switch( probably would have been better, i.e., more readable (to me), since I actually had more than two cases in my list from the “type” field.

Again thanks!

8

Just to clarify, ?( and switch( are functions that can be used in conjunction with the Morph dialog, not instead of the Morph dialog.

9

In fact, I had overlooked that it was a call( function — not the call statement —, and I did not even know that a call( function exists.

But I stand with my proposal. My one line formula does the same as this call to a nine line procedure.

10

It’s actually been around since Panorama 5.5. Panorama X added the callwithin( function.

I agree, but not so much because of the nine lines. There is additional overhead in running a procedure statement vs. a formula operator or function. So generally if you can do a job using just a formula, that will be faster.

11

I just thought I’d mention, Panorama X supports another format for this formula, where there are multiple conditions.

?(type="a",field2*200,type="b",field2*17,0)

I think this version is more readable, and you are less likely to misplace a parenthesis when there are fewer of them.

1 Like