Buggy portal behavior?

[ESpringer]

I thought I was dashing off a demo file, but instead have a riddle in the form of a misbehaving file...

The idea is to create a portal that shows which records are actually being browsed, keeping up with all sorts of additions/omissions/deletions, etc. I had no success with self-join relations on the promising Status(xxx) functions. But FM gives us ONE potential handle on the identity of the found set: a summary total field can be analyzed to figure out which records are being totaled, provided only that every total is unique. A sum of any set of unique powers of 2 is unique. So if each serial ID#n is assigned a 2^(n-1) binary ID (1,2,4,8,16,...) then any total (like 13) can only come from one collection (1,4,8). So a calculation that looks at the total and parses it back into return-delimited record numbers (1

[Fenton]

Please post the file. This is a very interesting idea, esp. to use non-binary saavy developers; sort of like a Smart Ranges technique for found sets.

It sounds like it must be some kind of storage or refresh issue, which seem to plague portals more than anywhere other place.

[ESpringer]

Here's the file. Fenton, you're right that it's just an occasional display issue; the more I played with it, the more I realized that there was no problem in "the data" ... For example, if I clicked in a field, the fields in the *correct* portal rows would show the active-record's-field-outline, while the other rows would go on showing the "ghost" data as inert. Flipping layouts, refresh-screen command, popping-up any dialogue and making a meaningless change, all these things tended to suddenly "wake" the portal up (refresh-window did not *always* work).

But the strangest thing is that the misbehaving portal isn't just *retaining* the old state, failing to update, but suddenly jumping to include totally irrelevant things. Furthermore, although it's a rare error, certain omit-omit-omit... sequences predictably reproduce it. I haven't troubleshot enough to generalize over the range of cases, though... Ah, looking further: I think it's that when a string of consecutive omits has gotten to the last record in order (in cases where some early records were *not* omitted) the portal display reverts to the state it was at when the latest string of exclusively-consecutive-omits started. Whew.

Anyway, here it is, and if y'all think it belongs in the sample files section, then I should post it separately where a search is more likely to find it than under "buggy portal behavior?"

[Fenton]

Most interesting. Too bad it depends on a multi-line calculation, 'cause there is a limit to the number of lines. I can't remember, but I thinks it's about 300? There's also a limit on the number of arguments.

As far as the portal refresh, I'd try the old "include a key field in the calculation" trick. Then reset the field and exit after each operation. Examples:

Case (RecID = RecID,

multi-line calcs

)

Omit Record

Set Field [ RecID, RecID ]

Exit Record

After doing that I was unable to reproduce your refresh issue (actually I didn't see it in the first place, so this is not very scientific, but I think the above will fix it :-)

[Fenton]

I forgot, a Freeze Window at the top makes it really quick!

[Ugo DI LUCA]

I couldn't reproduce the bug you're referring to...

Apart this limitation Fenton pointed out, this is a great file, and a good implementation of binary data.

I can't even think why the steps Fenton added would produce anything as these calcs are unstored and issued from a selfJoin, and should therefore update rather quickly. Fenton ?

BTW, is this case statement quicker than a go to field.

I use a update field in one of my layout too. It's a 1pix*1pix record_Id field at far high-Left side of screen with a script :

go to field [record_Id]

exit record

[Fenton]

I would agree that the calculation and portal should update; and it did on my machine. But ESpringer was seeing an occassional, and weird visual update problem. Maybe it's a machine difference, maybe he just did more (likely).

Visually non-updating portal calculations still happen with related calculations (they do update properly, but don't show it in the portal immediately). But I don't remember seeing one in a self-related calculation; and I've never seen one go haywire like his did. Like you say, it should work as is.

I should have explained better:

This was to be added to the calculation itself:

Case (RecID = RecID,

multi-line calcs

)

This was to be added at the end of each script resetting the portals:

Set Field [ RecID, RecID ]

Exit Record

It does nothing really, except kick the calculation and portal relationship lightly in the butt. If yours always works then there's no need; but it doesn't hurt or take any time (compared to the rest of the calculation, which I imagine might get kind of slow with the maximum records possible).

[ESpringer]

Your workaround is nice (works for me, could be tweaked to allow omit multiple)... still I have a deep aversion to making every operation take place via a script... I guess I always want things to "play nice" even when user can "omit" in ordinary way.

I'm curious about the built-in limits for calc size. 300 lines of calculation, you think? In the calc field def? Could a 600 multi-key be generated by concatenating two calc fields, or is it that FM can't parse 301+ lines' worth of relational key?

Hmm, if one's records naturally divide into sets (like, by year or class or type), then one could farm out the binary ID functions into separate calc fields, have multiple summary totals, and multiple adjacent portals ("Currently browsing these records: from 2002:... and from 2003...") Of course, with a file that big, the idea of getting a "glance" at the found set becomes silly anyway, no?

[ESpringer]

Ugo,

The calcs themselves update no problem. Actually, I'm surprised how responsive the file is (so far, with 40 lines' worth of parsing). The problem is just in the portal display.... Let me tell you one recipe for getting the problem with this file, at least on my machine:

Show all and go to first record.

Omit first record (here and henceforth, omit with keystroke; the button has already incorporated a refresh).

Browse forward (so skipping record 2)

Omit third,

Omit fourth,

Omit fifth... (etc.)

Pay attention as you omit the second-to-last. Suddenly, with just two records left, R2 and R17, the portal has reverted to way earlier state, until unless FileMaker experiences a kick somehow...

Perhaps this is a hardware and/or version-specific glitch? I'm on FMPD6, Mac OX 10.2...

[Fenton]

Sorry, I was wrong. It's not 300 lines. It's not lines at all, it's 30,000 characters. Here's something I saved (don't know where from). I ran into both these limits at different times while trying to build a huge multi-line key once.

limit of 30,000 characters in a calculation. A limit of 253 characters in a parameter of a function. A limit of 170 or so possible tests in a CASE or nested IF function.

Yes, you can concatenated two multi-line calcs to produce 1 multi-key. The only limit there is the 30,000 characters, and speed.

[Fenton]

Your calcs are not nested, so it would be 30,000; concatenating would not change that, so it's not useful.

[ESpringer]

You're talking about 30,000 characters *in* the calc definition (not in the output)? Because if that is so, it seems concatenating chunks of calc *would* help. No? However, if it's a limit on how many calc output characters can be kept-track-of and/or fed into another calc, that's another thing (I myself have bumped into the 253 characters within parameter issue, though I didn't check the exact count). At any rate, I'm relieved to realize that the technical horizon (apart from speed loss) is not so immanently close. Maybe I'll work up a version of this on a copy of one of my large files and see how it goes...

[Fenton]

Oops. I'm on drugs (caffeine anyway). You're right, it's in the calculation definition, so concatenating would help.

And, after searching my bits of info, I found a more reliable source that says: 32,767 characters.

I don't know why Filemaker hides this information. I could not find it on their web site, searching the Tech Info. The above is likely from 5.5 not 6, 'cause the import/export doesn't mention XML. If anyone has different specs for 6 please post.

BTW, to build such a large calc, you might want to use a small file, which produces 1 line of the calc as text, with the incrementation done by the current record number.

Then, after creating a bunch of records, you go to a layout with the completed lines, Copy All Records (Option-Copy), then paste into a text editor, to adjust, replace characters if needed. The last line may have an extra "

[ESpringer]

Yeah, the "R" was added when I was starting to troubleshoot the buggy portal, thinking that maybe different lines' numbers were somehow running together, and also that an alphanumeric serial would be easier to distinguish from the binary numbers themselves in my calcs and layouts while developing the thing... Indeed, though, to trim this down to manageable, that's worth eliminating. Corner-cutting on such a reiterated calc is a priority... I've already done some (especially, using 2^n notation rather than writing out the binary numbers); if you see any other inefficiencies, please do let me know.

Gee, if I use just one non-ascii characte -- the sigma sum sign (I'll try to post it here, probably no luck:"?" typed on my mac) -- for the field constantly referenced in the calc, would that mess things up for a Windows viewer?

Thanks for all your comments on this file.

[Ugo DI LUCA]

Yes,

I've used the external "code file" as well once. It was for indexing purpose though, even if I discovered I could have use repeating fields as well.

It really helps reduce text string in calcs, even if it adds one step.

[-Queue-]

Here's a modified version that uses repeating fields and value lists instead of an egregious number of Case( )s. The drawback is that the data is semi-static and requires relookups when the found set is changed. But if you are already scripting such navigation for your users, then it shouldn't be a problem.

Note: transferring between records should also be scripted, as a lookup will be required. Running a Set Field loop or a relookup takes too long to perform, which is why I've only scripted individual record Sets.

foundrecordsportalV2.zip

[Fenton]

I'm afraid I've run up against another limitation. I managed to get quite large calculations working, 840 lines for the found set (450 for the not found set). Strangely enough, both were well below 30,000 (not 32,767, which the documentation I had states; 30,000 is what the error dialog says).

But there's something else involved, because FileMaker would mistakenly stop at line 848 and say "not enough separators" or some such, though the line was just like any earlier line. Also, it doesn't like "If"s as well as "Case," even though If is shorter.

But none of much matters. At record 51 the binary number exceeds 15 digits. Once the file had more than 50 the calculations no longer worked. Less than that was OK, even with the large calculations.

[ESpringer]

The calcs broke after only 50 records? That's terrible. That's very discouraging. Someday I'll have time to look it all over and try again...

[Paolo]

Filemaker is not Mathematica

[ESpringer]

Paolo said:

Filemaker is not Mathematica

No, it's not, but what I'm trying to get portal to do (with this roundabout mathematical approach) is something that really *should* be possible within FileMaker: keep accessible tabs on which records are being browsed! Obviously the info is "in there" -- just can't get it out! Yet.

[Robert Kidd]

It's OK guys I have the answer for you:-

Create a list layout.Works a treat, even 50 records plus

[John Caballero]

Paolo said:

Filemaker is not Mathematica

And the Stating The Obvious award goes to...

[Fenton]

Status(CurrrentRecordIDs -)

It brings up the question about how exactly does FileMaker know what the Found set is. Is it an index? I somehow don't think so, at least not in the same way. Perhaps FileMaker doesn't really "know", i.e., the found set is just what's left after you omit others; it is the result of an operation, but it does not have a separate list of them. If it does, it would be nice if we could at least have "read only" access to it.

In any case, unless there's a mathematical way to limit those numbers to 15 digits and still get a valid result, I think we've hit a 50 record limit.

Import a hundred or so records and look at the numbers, over 120 digits, 15 you can see, the rest zeros. Yikes. Even if FM could deal with it, the calculation time would be astronomical.

Not being either a mathmatician or a computer science guy, I wouldn't know what else could be done.

(BTW, if you are one of the above, and on a Mac, take a look at the Satimage Scripting Addition; lots of math functions.)

There's always Copy All Records and managing the IDs by Substitution - Addition. Requires scripting, but it seems there's no avoiding it.

[Ugo DI LUCA]

Well,

Would a Text Conversion change anything here ?

ID = Auoentered calc --> 2^(Status(CurrentRecordNumber)-1)

TextID = Middle(ID,1,14)& " "&Middle(ID,15,29)& " "&Middle(ID,30,44)...

For sure I didn't tested it, but couldn't we involve a TextToNum in the calculation with a correct result ?

Ok, still stuck at 15...

And yet another one...

ID = 2^(Status(CurrentRecordNumber)-1)/10000

[-Queue-]

Eek @ 2^(Status(CurrentRecordNumber)-1). All records would have to be found and sorted exactly the same way every time, and no records could be deleted without screwing up the list.

BTW the repeating field calc to value list method doesn't have the 30,000 character limitation, but I haven't tested the over 50 record issue.

[Ugo DI LUCA]

Hi JT,

I did have a quick glance to your file, but then I didn't really understood what script to add to it.

When scrowling down, it surely doesn't work, so you'd need an update for each GoToNext/Previous, etc.

Is it what you were referring to. I'm quite sure repeating fields could be a way to go, as they so many times saved my life.

An in fact yes, the Binary logic would be buggy anyway if you delete records.

[-Queue-]

Yep, that's what I meant by 'transferring between records' and that the relookup took too long, so I resorted to using individual Set Fields, and only when moving to another record.

[CyborgSam]

In my case, a particular portal does not display a calculation field's contents. This misbehavior is deterministic.

Everything was working OK until I did a recovery of all the files (no problems found, what I thought was corruption was a brain fart).

After the recovery, this one portal in the main DB (call it db1) does not display a particular calculation field from db1's related DB (call it db2). This calculation field in db2 is just a copy of a text field in yet another related DB (call it db3) cName = Relation2db3::Name. If I look at db2, the calculation field shows the proper data.

All my relations are based on key fields that have auto-entered serial numbers that cannot be modified. db2 and db3 are showing all records.

Interesting factoid: if one of the portal rows is the current record in db2, it's calculation field is displayed in the portal.

Interestingly, other calculation fields from db2 in the portal which use related data from db3 are displayed OK.

I tried creating a second field with the same data from scratch, didn't work. So the problem is not a corrupted field. I also tried creating a second portal, didn't work. So the problem is not a corrupted portal.

Like ESpringer, I found that doing a [color:"blue"]Define Fields and only clicking [color:"blue"]Done made the portal display the field AOK.

One fix I found was to add three steps to the end of the script that sets up data & goes to the layout with the portal:

[color:"blue"]...

Go to Layout ["Layout with Bad Portal"]

Refresh Window []

Enter Find Mode []

Enter Browse Mode []

Just the [color:"blue"]Refresh Window [] alone doesn't work. [color:"blue"]Enter Find Mode [] and [color:"blue"]Enter Browse Mode [] without the [color:"blue"]Refresh Window [] doesn't work either.

I made simple test DBs with the same portal, relations, and fields relevant to this problem. Worked AOK. The problem shouldn't be (but could it be?) how I'm setting up portals, relationships, fields, etc.

I'm convinced that this is some kind of bug. Several times I've checked all my relationships, fields, portals, etc. looking for an error. Can't find one. Having it work right by opening Define Fields & clicking Done supports the bug theory. Why a Refresh, Enter Find Mode, and Enter Browse Mode fixes it is a mystery to me...

Ideas & insight?

[-Queue-]

Okay, here's another version using repeating fields and the value list idea again. The difference is that the repetitions are scripted and only the first related record's repetitions (via a constant relationship) are set. Then a loop is run to reset all the records with their current record position. This works for up to 1000 records and runs much more quickly than using calculated repetitions. It requires some modification for a multi-user scenario. But the concept could probably be expanded.

foundrecordsportalV3.zip

[-Queue-]

And here's a much simpler method, similar to a Copy All Records, without modification of the Clipboard. A placeholder record is used that is only accessed via scripts and therefore not a candidate for prolonged record locking. This method probably works best with the status area locked.

foundrecordsportalV4.zip

[Jim McKee]

Your calculation, by definition, is unstored because it's based on a relationship (it's actually 2 hops away from the main file). Because your calculation displays as expected under the following circumstances:

* opening and closing the Define Fields window (refreshes calcs)

* the calculation belongs to the active related record (refreshes calcs)

* the Enter Find Mode, Enter Browse Mode, Refresh Window series of steps is run (refreshes calcs)

* a test system with a limited number of records (refreshing of calcs will be faster with fewer records)

I'm wondering if you're hitting some limit in FileMaker's capacity to display unstored calcs without forcing a refresh.

How many records in the main and related files?

Have you considered another way to go instead of 2-hop unstored calculations? Ray Cologan's Matrix Data Modeling article might give you some ideas about alternatives you can try.

Good luck

[CyborgSam]

Jim->

My understanding (am I wrong?) is that FileMaker updates the calc "as necessary." Wouldn't FileMaker think it's necessary when a calc is referenced through a portal? Is the two hops an issue?

I'll try and take some time tomorrow to verify that the recovery caused the issue (I have the old files on backup).

Thanks for the tip, I'll check out the article.

Sam

[CyborgSam]

I searched FileMaker's tech notes and found the note below. Sure enough, I found a field I had deleted in the Sort for one of the relationships involved. Ah well, I needed to waste a day for a stupid oversight...

http://www.filemaker.com/ti/106162.html

Unstored Calculations Fail To Redraw Properly As Data Changes

ISSUE

Calculation fields that reference related data cannot be stored in the Storage Options in Define Fields. This causes the field to automatically re-evaluate its results each time it is displayed on screen.

Under certain circumstances, unstored calculation fields that reference related data will fail to redraw properly as expected.

RESOLUTION

Check to make sure that the relationship referenced in the calculation isn't sorted by an invalid field. This behavior is common if the sort field for the relationship is deleted from the database after the relationship is defined.

[Jim McKee]

Glad you sorted it out. I was gonna suggest that something had to have changed in your set-up prior to the related calc field failing to display in the portal, but it seemed from your post that you had gone through everything with a fine-tooth comb

Ain't it always the little things that take the most time to fix!

Good luck, Sam.

[ESpringer]

Queue, I like your solution... but I can't stop myself from cmd-T omitting! So, I've stubbornly persevered to get a self-join portal to show the *actual* set of found records being browsed, adjusting live to ordinary (non-scripted) omits, finds, deletes, creates, etc.

There's currently a 1040-record cap. I'm still not positive how much further the ceiling could be raised...

So, just for kicks, here it is. Feedback welcome.

-E Springer