Portal Filter and Highlight Row

[Jon Crain]

Hello,

I'm trying to do two things which on there own are simple enough to do, but they don't seem to work together. The first is to filter a portal (in my case, I'm using ExplodedKey and ExplodedString as well as a checkbox set) and the second is to highlight the current portal row selected in my viewer table (see Viewer Table Demo from Foundation Database Systems).

The relationship between my pref table and the portal_list table doesn't seem to allow for the filters to work with the highlight. Any thoughts?

People.fp7.zip

[aldipalo]

Take a look at this solution by Ray Cologon of NightWing Enterprises.

http://www.nightwing.com.au/FileMaker/demos8/demo808.html

[Jon Crain]

I'm pretty sure that's not a Viewer Table, and I can't seem to get the highlight field to work in my situation because that solution uses some sort of GTRR in it.

[aldipalo]

Here's what it looks like in my app.

SO I'm not sure what you mean by:

I'm pretty sure that's not a Viewer Table

I view each contact individually, edit, view and add data as needed and when I click on the name in the portal it takes me to the next record. If I didn't want the user to do anything but read the data I can turn that off in layout mode. But, you know what you need. BTW, this app uses a gtrr as well. It also uses a CF to set things up.

[Jon Crain]

I could be wrong on this, but the issue with the way you're doing this is that when two records are edited at the same time you get one user locked out. (see the white papers at http://www.foundationdbs.com/downloads.html )

There is an example of a viewer table on that page as well.

Like I said, maybe I misunderstood some of the reasoning behind the viewer table setup, but that's the way I read that.

jon

Edited October 28, 2006 by Guest

[aldipalo]

I checked the short documentation on Nightwing and it does not indicate that this solution is only good in a single user environment. I've written to Ray and asked him to comment. If he doesn't respond on the forum, but does respond to me I'll let you know what he says.

Al

[CobaltSky]

Let me see if I can clear up some of the confusion that seems to have emerged above.

1. The concept of a so called "Viewer Table" is not the same thing as a portal navigation system. You can have either without the other.

2. The locking of a given record whilst it is being edited by another user (or in another window) is in fact a native FileMaker feature which *enables* databases to operate effectively and safely in multi-user mode.

3. Record locking does not interfere with the operation of a portal navigation system (as implemented in the NightWing demo mentioned earier in this thread). The technique is fully operable in a multi-user solution.

4. The highlighting of a portal row need not be dependent on the relationship between the current table occurrence and the one on which the portal is based (eg a global field or a variable can be used, as shown in the NightWing example). Global fields and variables are both independently operable in multi-user environments.

5. An inadequate attempt to circumvent FileMaker's native record-locking is dangerous and may be worse than no attempt at all. For instance, the FDS "Viewer Table" technique referred to above may fail (with consequent data loss) whenever two or more users click on the 'Commit Record' button more-or-less simultaneously after making edits to the same contact record. That's a situation which may occur infrequently - or not depending on the usage patterns of a given solution.

Whilst it is possible to create a solution of this kind which would be somewhat closer to being "multi-user ready" (eg the particular issue mentioned above could be addressed with the addition of appropriate error handling), the approach nevertheless has a number of inherent limitations and carries a number of other risks which should be considered carefully before adopting it - or anything resembling it - as the deployment model for a given solution.

[Jon Crain]

Sorry to create any confusion there. Thanks for the explanation of things. I'm beginning to think I need to rethink the idea of a viewer table and go with a simpler straight forward approach to things which I believe is what you are hinting at. Simpler sometimes is better.

Jon

[CobaltSky]

Sorry to create any confusion there. Thanks for the explanation of things. I'm beginning to think I need to rethink the idea of a viewer table and go with a simpler straight forward approach to things which I believe is what you are hinting at. Simpler sometimes is better.

Jon

Hi Jon,

Yes, simpler is sometimes better. But that is not the only issue. I tried to express myself somewhat delicately and concisely in the previous post, but perhaps it would be best if I were to enumerate my concerns more fully. Here are some of the things I didn't say.

First, let me comment briefly on some of the implications (and what I called 'inherent limitations') of the "Viewer Table".

In the example under discussion, auto-enter lookups are used to pass data back and forth between the contacts table and a set of fields in the preferences table in which the user is to view and edit the data one record at a time. This requires that all the data fields in the contacts table - and their counterparts in the preferences table - be defined as auto-enter lookups. That's fine in a demo, but in a real-world application it is likely that a number of those fields would require auto-entry options for other purposes and the requirement for multiple auto-enters on some of the fields (and setting them up so that only the appropriate auto-enter option fires at any given time is not necessarily straightforward). Moreover it is quite possible that some of the auto-enter operations that would be required in a real contacts table would be lookups, so you then have lookup spaghetti and some very interesting work-arounds to get more than one lookup operating on the same field. So this approach to implementation looks a lot more attractive in theory (or in a demo) than in practice.

That's one potential issue that can be expected to emerge during implementation, here's another. In any real-world contacts table, it's highly likely that there will be multiple relationships to other tables which will enable the user to see what each contact is linked to (other contacts, jobs, purchases, locations, projects etc etc). In order for related information to be available from the context of the viewer table (where it is likely going to be needed - eg to get lookups to work in the edit screen as they do elsewhere), a probably outcome is that most or all of the relationships that connect to the contacts table will have to be duplicated and a second complete model of the graph appended to the preferences table. At which point you have "two graphs on your graph" and they must both be maintained and kept in sync. Now consider that unless you're very careful, you may have to do this again for every table throughout your solution that requires a "Viewer".

These are not show-stoppers, however. You can wrestle your way through them if you want - or you can develop work arounds and alternative implementations that ameliorate these limitations. But there are some deeper and more alarming issues.

Any situation which allows more than one copy of a given set of data (eg contact record) to be edited simultaneously, is a recipe for data integrity and discontinuity problems (including data loss). In fact allowing simultaneous editing of multiple copies of the data breaks a basic rule of good data management. The "Viewer Table" idea introduces exactly that - the ability for multiple users to edit the same record simultaneously - and promotes it as a magic pill.

With a "Viewer Table" type of implementation, users will never be locked out of a record and can therefore view and edit the same record at the same time. On the face of it, this seems like it might be a good idea and might liberate users from the annoyance of finding that a record they would like to edit is locked becuase it is currently being edited by (for example) Jeff in accounts, who has gone to lunch and left the cursor in a field.

However there is a question as to whether circumventing record locking is a good idea at all. "Solving" this "problem" may not be the clever move that some would have you believe. Quite the contrary.

Consider this hypothetical scenario; a not-too-improbable sequence of events (and only slightly adapted from real-life)

1. User "Andy" navigates to record number 17.

2. A few minutes later, user "Jane" navigates to record number 17 too.

3. Then Andy notices what he regards as a minor typo in the street address field and makes a correction to it - but the 'phone rings and he takes the call before committing the change.

4. Jane has received a form notifying the company of a change of address for a VIP whose details are on record 17, so she proceeds to enter the new address, commits the change, marks the change of address notice as having been entered into the company database, and sends it to be filed.

At this point all appears well and it seems that perhaps the programmer is to be congratulated for implementing a clever system that allowed Jane to enter the change without being stopped from doing so by a record lock placed during this time by Andy. But let's consider what happens next:

5. Andy finishes his phone call and turns his attention back to what he was doing. He commits the record and all the data on his screen is written back to the contacts table, overwriting the new address Jane had entered.

6. The VIP subsequently expresses her displeasure to the company president that although she has submitted a change of address form weeks earlier, her mail is still being sent to her old address. The president is embarrassed and makes a fuss, so Jane is questioned.

7. Jane is able to persuade the company that she followed procedure and after questioning Andy as well, the company calls in a consultant to investigate the causes of data corruption and loss in their expensive new database system.

8. On receiving the consultant's report, the company concludes that the system is flawed and terminates their relationship with the programmer who created their contacts database.

As you can see, even if the issues such as error trapping are addressed so that the technique reliably works the way it is supposed to, there is still a fundamental problem with the underlying logic. In fact it's debatable whether the problems it creates are worse than the problem it purports to solve.

In my travels, I've chanced to encounter a number of situations very like the one described above. Several of them relatively recently - and at least one of those appeared to have been inspired by the FDS demo mentioned on this thread.

I am not about to say you should never use such a technique. In fact there may be some solutions where avoiding the inconvenience of record locking is more important than preserving the integrity of the data. All solutions are different. But bearing in mind all the issues outlined above, it is not safe to assume that a "Viewer Table" has broad applicability, nor to recommend that anyone implement such a technique without careful consideration of all its implications.