Schizophrenic field value causing problem with portal script

[daehl]

I'm starting to use FMP again after a long absence, so I'm a not a newbie, but I am rusty, and new to FMP 8.5. Anyway, I have a problem with a script I'm writing to automatically enter a value into a field in a portal. It's a bit bizarre because the script works as long as I click outside the portal after each use. But if I continue to click (and create) new related records in the portal one after another, the script fails (and some strange FMP behavioir occurs). It's as if the calculations I'm using are only appearing to update, but aren't.

The weirdest thing is that one of the fields I'm using as a counter is in the main layout, and it displays the correct value ntil you click into the field, and then it instantly reverts back to the original (wrong) value. It's almost like that field is schizophrenic. It displays one value, but equals a different value when used in calculations (or when you enter into it).

Specifically, I have two tables. The first is called "Bags". It has two fields:

BagID (auto-entered unique serial number)

ItemCount (an unstored calculation which counts all the records in "items" with matching BagID)

The second table is named "Items":

BagID (serial number from above, used as key field in relationship)

ItemNumber (this is the field I'm trying to auto enter via a script)

So I've related the two tables by BagID, and created a portal for Items in the Bags layout. The ItemCount calculation is simply set to count(Items:BagID).

My goal is for the item's itemnumber field to automatically populate as new item records are added to the portal. I want the first item record to be numbered 1 and increment by 1 for each new item, and the item numbering should start back at 1 for each new bag record. So if there were three Bag records (numbered 1 thru 3) and each three items then my Bags table would look like this:

BagID: 1, ItemNumber: 1.

BagID: 1, ItemNumber: 2

BagID: 1, ItemNumber: 3

BagID: 2, ItemNumber: 1

BagID: 2, ItemNumber: 2

BagID: 2, ItemNumber: 3

BagID: 3, ItemNumber: 1

BagID: 3, ItemNumber: 2

BagID: 3, ItemNumber: 3

So I've written a scriptmaker script to make this automatic. To paraphrase:

if isEmpty( Items:ItemNumber) then

set field (Items:ItemNumber) to Bags:ItemCount + 1

end if

I've attached this script to the portal field for ItemNumber and prevented entry into that field in Browse mode

So When I create a new Bag all I should have to do is click on the empty ItemNumber field and it should start adding related records and increment the ItemNumber field by one each time. But it doesn't. Well, it usually works for the first two clicks (it will create item 1 & 2) but then gets stuck at 2. Also if I click outside the portal, it starts working again. It's almost like the Bags:Itemcount field is not refreshing in the Item Portal.

But the bizarre thing is that Bags:ItemCount is on the layout and I can see it updating. After clicking on four rows it will display an itemcount of 4 but still the script will only enter 2 (ad 4 + 1 doesn't equal 2). Weirdest of all, if I enter into the itemcount field by clicking into it, the value will temporarily rever to 1 ?!?! and then if I click out of the field it will go back to displaying 4. So how can the same single field be both 1 and 4 at the same time???

I've attached my test database to demonstrate the behavior.

I'm stumped, but continuing to work on it, just making no progress so far.... If there's an expert out there who can explain to me why this is correct, knows a workaround, or can confirm this is a known bug in FileMaker Pro 8.5v1 I'd appreciate it greatly. Thanks

Also, I've searched but can't seem to find a similar thread in this forum, please reditect me if I missed it.

test.fp7.zip

[LaRetta]

My goal is for the item's itemnumber field to automatically populate as new item records are added to the portal. I want the first item record to be numbered 1 and increment by 1 for each new item, and the item numbering should start back at 1 for each new bag record.

Nah, don't do it, it will break in multi-user. And what if you delete an item? Are you going to reserialize the bags items? Serializing of this type is man's attempt to force order where it shouldn't be … good grief, I'm sounding like Soren!

You will indeed end up with schizophrenia if you pursue this path - and that is why 26 people have read this post but nobody has responded; you are heading down a lose-lose path. Let your Items have their OWN unique serial just as Bags does (and every table should, in my opinion). In this way, if you later need to add a relationship to the Items table, the proper serials will already be in place. And if you later need to write a calculation such as this one, you’ll again already have the proper structure in place, ie, proper serials.

How to solve your current display issue so that they display properly?

If all you have is portal, just type @@ in the portal row. This will give you a sequential serialized number of your related records. But if you need it to display in reports where ALL Items are together for many bags then you need to display this new ‘serial number’ within your Items table (see attached). Calculation learned from Comment.

By using this unstored calculation for DISPLAY only, you can have what you require without writing to records themselves.

testREV.zip

[LaRetta]

Or, ummm, you can even count the children thus ...

The point is - what you do behind the scenes vs what you show to your users/owners do NOT have to be the same thing. You need to do what's right for your solution and use other tactics at times.

testREV2.zip

[daehl]

Thanks, LaRetta.

I appreciate your advice and taking the time to demonstrate a workaround.

To explain a bit further: I've simplified the details of the database I'm working on to focus on the exact problem I've encountered. So I'm not actually tracking "items"--which may be added or moved to another "bag". I'm working on a data-collection database, and the "items" will not be allowed to be deleted or moved to other "bags" so there will be no need to renumber them in the future.

The thing that really has me stumped, and I see the same effect in the database you uploaded, is that FileMaker seems to display one value for the cItemCount field as you are adding items, but that same field (at the exact same time) will equal another value in calulations. So the cItemNum field in the portal displays wrong, until you click outside the portal and then the calculations are updated. That really seems like a bug in FileMaker to me as if the unstored calculations are not recalculated until you exit the portal (or the record) and even worse, I can't for the life of me imagine how a single (non-repeating) field can have two different values simultaneously like that. I had no idea that FileMaker had a quantum uncertainty principle like that!

Anyway, my goal is to assist the end-user by making the "item" entry automatic, and if the numbers do not populate correctly during the data entry (if even for a short time) it will be disorienting for them. Needless to say, I'd like to find a way to auto-enter a serial (number) for each new "item" starting with 1 for each unique "bag". It seems to me that I must use the count() function for this, but all three of these examples seem to demonstrate that either count() has a flaw in it, or the way calculations are referenced within portals is flawed in some way with regards to their being the current values.

I'm still thinking there's got to be some way to force FileMaker to update it's calculations procedurally...

[daehl]

Yay!

I finally got it to work, by adding two lines to my script. I tried a seemingly endless number of things that I hoped would force the calculation to update within the portal's scope (refreshing the screen, flushing the cache to disc, switching to another field or record, etc.). Finally, I tried switching to another layout and then back to the original. That works!!! So just two extra lines at the end of my original script fixes it.

Still, I'm totally amazed the way the field displays the correct value in Browse mode, but a different value in calculations. That is so bizarre. It's like the same field has different scopes until the data entry is determined to be complete by FileMaker.

test_works.fp7.zip

[comment]

You should read about committing records.

[daehl]

Thanks, Comment!

I don't know how I missed that command. That's exactly what I was looking for and must have overlooked it over and over.

I don't recall using that command in the past, but it's been a long time since I've used FileMaker (version 5) and I'm starting to get the hang of it again.

Thanks again!

[Søren Dyhr]

Thats obvious then, since it was with fm7 the entire record ownership business recieved a radical change to previous concepts - Read (reread??) Ilyse Kazar's chaper on the topic in the Migration Foundations Whitepaper!

--sd

[daehl]

Thanks for bringing that white paper to my attention, Søren.

That looks like a very interesting read!

[Søren Dyhr]

I would say manditory - not just interresting!

--sd