Runfieldcode skips first record

Panorama X
1

I’ve written a test project having two databases (field info given below). Basically I’m testing updating a field in Target that uses a lookup( into the Source database. The user will be manually updating the “sourceData” field, and I want the “targetField” field updated using Field Properties>Code.

DB: Source, Fields: id (text), sourceData (integer)

id sourceData
a 4
b 3
c 7
d 8

DB: Target, Fields: id (text), data (integer), targetField (integer)

id data targetField
a 1 2 <code is defined for this field, see below>
b 2 5
c 3 10
d 7 16

Field Properties>Code (field=targetField). I broke down the steps and added a message for debug purposes.

------------ Code -------------

local d
d = lookup("Source", id, id, sourceData)
message "autocode d = " + d
targetField = d + data

The messages in the Update Target procedure (below) are for debugging.

Target:Update Target procedure:

--------------- Update Target ---------------

setactivedatabase "Target"
local numRec, n
n = 1
numRec = info("records")

firstrecord
runfieldcode «targetField»
save
message "new field value: " + targetField
stop
; traverse the records
loopwhile n <= numRec
    runfieldcode «targetField»
    message "new field value: " + targetField
    save
    downrecord
    n = n+1
endloop

Results.
Before putting runfieldcode, save, message before the loop, the code filled in targetField correctly except for the first record. I put in the code up to stop to test just the first record. Output:

" new field value: 2"
… and that’s it. I did not get message "autocode d = "+d executed.
I expect the new value for targetField to be 5 instead of 2, which was the “old” value.

When running the loop, I did get the autocode messages for all records except for the first one.

Why is the first record’s Code not executing?

2

Do you need n=1 prior to the loop?

3

I need to prime the loop. n is the loop number where the n-th record being processed.

4

Sorry! I meant to suggest you needed initialize ‘n’ as my eyes failed to see you had clearly done so!! Maybe time for a nap!

5

Yea, I’m ready for one too. No worries.

6

The instant you run the message statement your setactivedatabase is cancelled, and the active database is set back to whatever the topmost window is. Alerts (including the message statement) are a poor tool for debugging because they actually affect the state of Panorama. Best bet is to use the zlog statement (see Debug Instrumentation), though sometimes for quick and dirty I’ll use nsnotify.

In any case, here’s a simpler way to perform your task.

setactivedatabase "Target"
firstrecord
loop
    runfieldcode targetField
    downrecord
until info("stopped")

But really, I think the way to do this task is:

setactivedatabase "Target"
field targetField
formulafill lookup("Source", id, id, sourceData)+data

The code above will run much faster than any looping solution. But if these databases will be large, I think the best way to do this would be to set up a relation between the two databases. You could then do a join to bring over the sourceData into targetField, then do a formulafill to calculate the sum. This will be super fast even for tens of thousands of records.

One other point – for new entries, I would not use the Code panel. Your really just using the code to calculate a formula, so it would be simpler to use the Formula panel. Put this in the Formula panel for targetField.

lookup("Source", id, id, sourceData)+data

Then targetField will automatically recalculate when the id or data fields are edited. (Or, if you set up a relation, you could use the related( function instead of lookup(.)

7

When will I remember nsnotify and zlog!

Alternative:

setactivedatabase “Target”
field targetField.
opensheet
formulafill lookup(“Source”, id, id, sourceData)+data

If I farcall this from a procedure in Source, then I have to add opensheet otherwise I get an error that targetField is not visible in the current window.

This is just a mockup of what I’ll need in the future. Given this type of situation, it’s always a question as to who is requesting an update of targetField: the DB where the field values are changed thus affecting the another DB’s fields, or the DB whose fields need possible updating. It’s the “push” / “pull” situation.

Thanks, Jim. My past “world” always involved loop traversals to process many things, whereas Panorama X has all these clever one-line commands that replace loops.

8

There’s something else going on here. When you use setactivedatabase, Panorama switches to a “secret” window. A “secret” window allows access to any field in the database. I just did a test to confirm this.

In the code you provided, you have an extra period at the end of the line

field targetField. <-- extra period

Perhaps you only made a typo here in the forum post, but if that extra period is actually in your code, it would cause an error.

Also, the code you have posted is definitely not going to work. After the opensheet statement, you have no idea which field is active – your fill will dump into whatever field happens to be active. You would have to put the field targetField line after the opensheet line.

FYI – there is still a loop, but the loop is now in much faster compiled Objective-C code instead of in relatively slow interpreted Panorama code.

9

Hello Jim,
I do not have a comment on pvonk’s enquiry. But I just happened to see your response quoted above. I have found the point you make is the source of most (in my case anyway) of PanX issues.

Unlike with Pan 6, the active database is always uncertain in PanX. Since single-step debugging is no longer active, adding message statements to track variables, or to give intermediate information within a procedure, messes things up considerably. And using SECRET windows caused nearly all of my procedures to run into trouble after performing flawlessly for years in Pan 6, maybe because a secret window is never the top window.

In Pan 6, once a database was the active database, it would remain so until a new database opened, or Panorama encountered a WINDOW command. Would it be a difficult thing to have PanX behave similarly? I suspect it is Apple’s operating system changes which force the database to change every time a new window becomes the top window. But with your expertise and special facility for programming, is this something you could put into PanX? Perhaps using a SETACTIVEDATABASE command could cause PanX to create an internal variable, so it could keep track of the current database, and make sure it doesn’t change until the user actually and purposely changes the database.

Perhaps this is a lot harder to do than I imagine. Has this topic been covered in the forum before? I don’t follow the forum as diligently as I used to.

Regards,
Vic

10

Yes, I added the <— after pasting the code. Mac likes to add closing periods when typing two spaces.

“ Also, the code you have posted is definitely not going to work. After the opensheet statement, you have no idea which field is active – your fill will dump into whatever field happens to be active. You would have to put the field targetField line after the opensheet line.”

I’ll have to go back to my code, because my tests using the code worked. So you’re saying that if I make a field active, it loses that distinction when I use opensheet? If so, then it worked by sheer luck.

“ FYI – there is still a loop”

Yes. I was never sure whether our code was interpreted or compiled. It looked compiled when comparing speed of the (optimized) one-liners with a loop statement. The speed difference is like night and day!

11

I wrote this reply a couple of hours ago but misplaced the draft until now. So this is a reply to the 4 hour ago post. I’ll look at the newer replies soon.

This is not true. But there is one difference between Panorama X and Panorama. 6 when using “secret” windows. If you are using a “secret” window, that “secret” window will remain active until the procedure stops, or until some other window is made active. In Panorama X, the window displayed by the message window counts as “some other window”, in Panorama 6 it didn’t. This is due to a difference in how OS X handles alerts vs. how MacOS did.

This has been covered in detail here on the forum multiple times. TLDR; - if you are using secret windows, don’t use message statements (or any alert).

12

About the opensheet possibly changing the active field …

I need to bring the focus back on this issue. Given the following scenario:

If I’m working in some form or data sheet and I have a particular record active and a certain field active, then do I lose the active designations, if I make another database active, view or edit a field in that database and finally “come back” to the still open initial database window?

13

Yes. The active field is independent for each window. If you have both the data sheet and a form open, each could have a different active field.

NERD ALERT!

By “our code”, I assume that you mean code that you write in Panorama. This code is partially compiled in advance, but to tokens, not machine code. Then when the code is run the tokens are processed with an interpreter. So it’s faster than a pure interpreter, but significantly slower than a compiler that produces machine code.

Panorama statements like formulafill are written in Objective-C, which is compiled to machine code. The formulafill statement does use a loop to run through the records, but this machine code loop is very fast.

14

No. Panorama keeps track of the active field in each window. If you go to another window and then come back, the active field will be the same as it was before you went to another window.

It’s not the opensheet changes the active field. It’s that each window has it’s own, independent active field. So whenever you go to a particular window, the active field for that window becomes the active field. It doesn’t matter how you switch windows – it could be the window statement, opensheet, openform, openfile, topdatawindow, closewindow – whenever the active window changes, the active field in the previously active window is no longer in effect. This has always been the case, all the way back to Panorama 1.0 in 1988.

15

Jim,

Thanks for your patience, and for being redundant. Having the active database or field change, without the coder anticipating the change, is frustrating. Both for new coders and for old coders with outdated habits—such as using message statements for debugging—the solution is better understanding of why they change. Repetition helps.

For completeness should I suppose that not only datasheets and forms have separate active fields, but procedure windows also do? If a database has no windows, eg. after makesecret, is there a separate active field associated with that?

16

No, procedure windows do not have an active field. You cannot run any operation that deals with database data when a procedure window is active.

For your specific example, makesecret, that is just like closing the window. At that point some other database will become the active one, whatever database now has the topmost window.

I don’t think I’ve ever written a program that used makesecret and then went on to do other things. But if you did, you cannot make any assumptions about what window might be active. Any following code should explicitly switch to a different window or open some other file.

If you start a secret window, with either setactivedatabase or window “database:secret”, I believe that initially the first field in the database will be active. But I wouldn’t rely on that, you should explicitly set the active field.

There are only a few Panorama operations that care what the active field is: sort, formulafill, etc. I think the best practice is to set the field right before you perform these operations. If you perform two or three of these operations in a row you don’t need to set the field each time, but you should do it right before the operation. That will also make it easier to read your code at a later time. You may have to come back to your code 5 or 10 years from now – make it easy on your future self!

Any time the top window changes, Panorama’s context changes. Believe me, if it didn’t do that, you all would be complaining like crazy. There’s nothing unanticipated about that – it’s a basic feature of Panorama.

Imagine that Panorama windows are like buildings. If you go into a building and press 5 on the elevator, you’ll go to whatever is on that floor in that building – let’s say there is an office for Dr. Panorama. If you go into a different building, the 5th floor will contain different offices. You wouldn’t go into a second building, press 5 and expect to see Dr. Panorama’s office.

And in each building, the elevators are independent. The elevator might be on the 4th floor in one building and the 12th floor in another building. That’s how the active field works – each window has it’s own independent active field. In an elevator you press a button to go to the floor you want, but that doesn’t move the elevator in a different building. In the same way, the field statement only moves the active field in the current window.

17

And what about the active record in a database? Is that also dependent on the window. If so, I’ll have to be careful. In FileMaker, the active record, in fact the “found set” (selected/visible records) , is constant across any window/form. I count on that.

18

I’m thinking of a variation of your metaphor: the building is the database and each wing is a different window/form/data sheet of the database. If I’m on the 1st floor, east wing and take an elevator to the 5th floor, and then I walk over to the north wing and get in that wing’s elevator, I still expect that I’m on the 5th floor. If I take any elevator (one in each wing) to the 7th floor, I’ll be on the 7th floor, regardless of which elevator I take.

As I asked above, does your implementation of active field apply to the active record? The project I’m working on involves a number of databases each having several forms. For example, one database has many fields that fall into one of several groups (fields pertaining to name/location/etc, other fields pertaining to various ratings, and so on. I plan to have several forms that present the key field of the DB and the fields of one of the groups. When I pick a record in one form, I’d expect that same record to be active in the other forms of that database.

Here’s another question along these lines. I have one “main” form that has a tab panel. Each panel is associated with one of a collection of forms (subforms?). When I tab from one panel to another in the main form, am I going to a different form, even though I remain in the main form? In this example, I expect the active record to remain the same from tab to tab.

If the answer is each form has independent active records, that I’m guessing the solution is to save the active record pointer (in one form) to a variable and use that when going to another form in the same DB in order to activate the same record. Right?

19

Sounds like the tokenization used in the early days of the Java language.

20

No. The active record is the same in every window of that database.