Special rules & restrictions for writing code that is triggered by data entry

[aehaas]

I use Panorama for my payroll. All was fine (and is fine on the older program version 10.2.0b23 (3857)). Now on 10.2.0.b28 (4125) my macro does not work on the last column.

Normally I enter the wage rate, then the hours worked and tab across and it does the rest but the last column does not work. Suggestion?

Thanks,

Ali

Here is the code for the last column that totals the previous column and does the math for the last column.

NET=zeroblank(«TOTAL GROSS » - (FICA + FICH + «FED W/H»))
Field "REG GROSS "
total
Field “401”
total
Field "TOTAL GROSS "
total
Field “FICA”
total
Field “FICH”
total
Field “FED W/H”
total
Field “NET”
total

How can I send in the actual file for you to look at?

[JamesCook]

What do you mean that it doesn’t work? Is there an error message? A wrong result for the total? Or does it fail to go to the NET field?

Is NET a numeric column?

What is the NET at the outset? Is that the field or is it a variable by the same name? If it’s the field, is that result working?

[aehaas]

Here is the database for the payroll of an employee. Normally you enter the hourly rate and the number of hours worked then tab across. The rest is calculated as you tab to the right. You can see by the formula the last column (NET) is your paycheck amount. when you enter the withholding amount and tab again and again the last column will populate with your paycheck amount and all the other columns are updated at the bottom of each one. It works with the b23 but not with the later betas. It is only the last column that does not work. If I do the payroll on a computer with beta 23 it works fine.

Ali

[aehaas]

Is there a way to upload the database/file?

Ali

[KJM]

You could upload the file (a sample without sensitive data) to Dropbox and post a download link here. Then we could look if we see the same behaviour on our Macs.

[admin]

Your code will need to be changed. I’ll give the changes first, then explain why these changes are needed.

The best solution would be to split the code into two separate sections. The first line, NET=... is ok in the code for the field. Everything else should be in a separate procedure, triggered by a menu or button (easiest would be to put this in the Action menu). Once you’ve entered the last column, you would need to manually choose this item from the Action menu.

If you really want all this to trigger from the tab key, I think adding a wait 0 after the first line will allow it to work, like this:

NET=zeroblank(«TOTAL GROSS » - (FICA + FICH + «FED W/H»))
wait 0
Field "REG GROSS "
total
...

(As an aside, why do the field names REG GROSS and TOTAL GROSS have a space at the end? That seems like a recipe for confusion and mistakes.)

---

I’m sure you are wondering why these changes are needed now and weren’t needed before. Well, actually, they were always needed, but you got lucky.

When a procedure is triggered by the tab key, Panorama is in a special internal mode. Usually when a procedure runs, it has the full attention of Panorama and can do anything. But in this case, Panorama is in the middle of performing the tab operation – when the procedure is finished, it will automatically start editing the next data cell according to the tab order. If your procedure does something like switch to another field, switch to another window or make calculations beyond the current record, that will mess up the pending tab operation. Your code is doing two things that aren’t allowed in this type of procedure:

• using the Field statement
• calculations beyond the current record (the total statement)

In the past you could sometimes get away with these bad practices, as you have. But recently a bug was discovered that could cause valid code triggered by data entry to incorrectly store the results of calculations in the code. Of course this was a very serious problem. The fix for this serious problem required Panorama to be more strict with code that is triggered by data entry.

As I mentioned, I think the best solution is to split your code so that the field and total statements are run in their own separate procedure, triggered by a menu item. I think the wait 0 solution will also work, but will probably behave weirdly if you press the tab key. It should be ok when pressing the Return or Enter key though.

[BruceDeB]

I can suggest a few things that will make this work easier.

The first is to decide which data you need to input, and leave those fields as editable. The fields which are calculated should not be editable unless there is some reason for overriding the calculation.

Then write a procedure that will do all the calculations, which I have done and call «Pay Calculations». Then in the Code for the editable fields, I wrote Call «Pay Calculations». This does the calculations each time I make an entry in an editable field.

You can have the fields totaled in «Pay Calculations» as the last step. I think this would solve your problem, but I do not do this, because my file is for all the people I am paying all at once, and that is what gets totaled. The totals that you are using are in my calculations for the fields.

The reason for this is that it is useful to have all your calculations in one place, so you are not forgetting to change them in one place when you change them in another.

[admin]

@BruceDeB

Then write a procedure that will do all the calculations, which I have done and call «Pay Calculations». Then in the Code for the editable fields, I wrote Call «Pay Calculations».

I agree that creating a subroutine is usually a good idea, I almost always do that if there is going to be more than a couple of lines of code.

However, the name you are using is pretty inconvenient, and in fact the call statement you suggest won’t even work. The « and » have special significance when used for a field or variable in a formula, but they don’t have any special significance when used for a subroutine call. So instead of

call «Pay Calculations» <-- WRONG!!

You would have to write:

call "«Pay Calculations»"

But why not simply call the procedure PayCalculations, then the code can be:

call PayCalculations

@BruceDeB

You can have the fields totaled in «Pay Calculations» as the last step.

Sorry, that is not true. Please reread my answer above. You cannot use the field or total statements in this procedure, even if it is the last step (unless you use a wait statement, as I described).

@BruceDeB

The reason for this is that it is useful to have all your calculations in one place, so you are not forgetting to change them in one place when you change them in another.

Yes, exactly. Also, I find it easier to work with code in a procedure window rather than an inspector panel. You can leave the procedure window open as you debug, rather than having to show and hide the panel over and over.

[BruceDeB]

In fact, I do use call «Pay Calculations» and it calls the procedure Pay Calculations. I guess I work with fields so often that I get into the habit, and maybe your code is a little more intelligent than you realize! The chevrons around the name are not part of the name. That was my mistake.

My payroll file is different from aehaas’s in that it calculates for all my musicians in one file. It has evolved from an earlier version which would run “Pay Calculations” on each record, one record per musician, and then total all of the relevant fields. I guess that still works. However, if I were to get fancier, I could put those into a form and use aggregate(.

The problem with aehaas’s method is that it requires a separate file for each employee. That could get unwieldy if there are a lot of employees. There are other ways of dealing with this, using relations, which might be a master file of employees with the individual employee files related to it.

Here is a neat trick to make calculations cleaner: If you have a base amount, with fields that are additions and subtractions, you can name the addition fields something like A1, A2,… and the subtraction fields S1, S2,… Then you can change the titles of those fields so they make sense. In my case, I am doing it for the dates of my concerts, so for instance, I changed them using this syntax: setfieldproperties “C5”, initializedictionary(“TITLE”,“July 31”), which changed the title of field C5 to “July 31”. In the case of additions and subtractions, they can be done by line item functions, like Net = Base +sum(AΩ)-sum(SΩ). In my case, the musicians get a bonus after playing a certain number of concerts, so count(CΩ) comes into play. I think I will use this more and more. Thanks for putting in Titles along with Names!

[admin]

@BruceDeB

maybe your code is a little more intelligent than you realize! The chevrons around the name are not part of the name. That was my mistake.

To be honest, when you made that post last night I wasn’t sure how it worked, so I tried it, and verified that it does not work with chevrons included in the procedure name itself. I didn’t try using chevrons as quotes in a call statement for a procedure name that didn’t itself use chevrons.

Here is a neat trick to make calculations cleaner:
Then you can change the titles of those fields so they make sense

That really IS a neat trick, and not something I had thought of when adding the field title feature. Very cool!

[aehaas]

OK, I went with all the suggestions. Cleaned up the column names, eliminated spaces and improper characters. Used only the main formula for each field and added a new field at the end for all the field totals to be calculated. I tried using the “wait 0” maneuver Jim mentioned to try and make the field totals run while tabbing off each field. It worker properly to total that column but did not move to the next field (on the Mac anyway). I want to thank you all for your help, very much.

Ali