How to avoid many users to go on the same records ?

[mcamerar]

I have a big problem afflicting me since 6 months which I tried to solve in differnt ways but at the end never worked properly....

I hope someone can help me, because at the moment I am complete stuck and quite desperate....

This is my nightmare:

I have a database (sort of CRM) on filemaker server to which many users have access at once.

The problem is, I would like that when a user go on one record, this record become inaccesible to all the other users and then, when this user leaves the record, the record become again available for all.How can this be done?

Can anyone help me with some good advice please?

Thank you very much for your suggestions.

[bcooney]

You are asking for a record lock. FM handles this automatically for you. As soon as a user starts to edit a record, he "owns" the record, and another user is only allowed to view the record.

Why this built-in record lock isn't working for you is a mystery? Perhaps you can describe your structure or post a small sample copy?

[mcamerar]

Sorry probably I made myself not very clear.

I know FM has this function, but this is not enough for me.

In fact you said that "As soon as a user starts to edit a record, he "owns" the record, and another user is only allowed to view the record."

The big problem for me, is that I don't even want a user to view that record that someone else is editing.

How can I make them skip the "busy" records ?

This is driving me crazy...

[Fenton]

There are 2 ways I can think of, either use Record-level View restrictions, or use global fields for data entry. I think I would go for the global fields. Because View restrictions cause a big performance hit, for all operations involving multiple records, and this kind of temporary thing seems best done by globals anyway.

Each user has their own globals. So no one is going to see what another is typing. They would still need to enter and edit at least 1 regular field first, in order to lock the record, or use the Open Record step explicitly. And you'd need a script to "submit" the globals to the regular fields, as well as one to go to a dedicated layout, and pre-populate the globals with the regular fields.

Yes, a fair amount of work. But what you want is unusual. I cannot see what real difference it makes whether someone can see what another is typing, since they cannot edit the same record, and can see the data as soon as the record is submitted. But ours is not to reason why, ours is to answer questions -]

Also, the globals will be lost if someone quits FileMaker before they submit them to regular fields.

[Actually there is another method, known as "Transactional" data entry. Which opens the record on a layout which does not have [x] Save record changes automatically. This may be better, at least less work than the globals method; but trickier to do; I've never done it, but someone has.]

Edited February 22, 2009 by Guest

[comment]

Another option, perhaps easier to implement, would be to open the record explicitly by creating a new record in a table of OpenRecords (in addition to locking the record itself, of course). This table could be then used to allow/deny viewing privileges to other users.

BTW, I don't see why viewing records being edited should be an issue, either.

[bcooney]

What do you mean by "skip"? Can they see the record in a list view? Perhaps you're looking for a nav script that skips over records that are owned by another user. However, that would be a bit odd. If I found 5 records and one is in the process of being edited by another user--what behavior do you want? I'd expect to see 5 records as I click from one to the other using a Next button.

Maybe just using Get(RecordOpenState) and some conditional formatting, would be enough to indicate to a user that someone else "owns" the record now.

[Fenton]

Yes, that's a good idea. That avoids the mess of putting extra fields (globals) in the same table, and also avoids the record-locking problems involved in putting the globals in a 1-record table. Just delete the new record after "Submit" (and possibly after any action that leaves the "edit" layout, using a modal window, or script trigger in FileMaker 10; otherwise you could have partial edits for the original record in the table, but no longer a lock on the original).

[comment]

Actually, come to think of it, I see no reason why a local flag field couldn't be used for this.

[Vaughan]

I'm in a funny* mood today, so I'll suggest this:

the moment a record is edited, copy the data to global fields and DELETE it. That's the only way that nobody else can see it. Then when the user finishes editing the record, create a new record with the updated data.

Yes, I know there night be a few issues to work-around, like related records, and the fact that records might go missing if people forget to save them, etc. But hey, it's do-able. And it'll be sooo cool!**

It might be helpful if the original posted explained why FMP's default behaviour does not meet their needs.

What, for instance, should happen if one user is viewing a record, when another person edits it... should it just disappear from the viewing-user's screen? What should the viewing user see when it's gone? Should such edited records also disappear from other internal functions like export or print, or any other processes that work with found sets or multiple records?

*Funny, as in I buy a new iMac 24 inch a couple of weeks ago and the keyboard is acting a bit "funny" as in keys seem to get stuck down so I go to the big impressive Apple store in George Street this morning to get it exchanged only get told that I have to make a frikkin appointment with a Genius (and the earliest is tomorrow, sir) before they'll even look at the keyboard, even though the place is frikkin empty... that kind of funny***.

**IMHO this is probably another "wouldn't it be cool if my database could do this..." ideas.

*** The act of expressing my anguish above has restored me to my usual humor. You can ignor all that "funny" stuff now. :D

[LaRetta]

Maybe just using Get(RecordOpenState) and some conditional formatting, would be enough to indicate to a user that someone else "owns" the record now.

Surprisingly, Get ( RecordOpenState ) will only tell you if YOU possess the record and not whether someone else does (see this thread for further discussion [color:red](correct thread is actually this one). I agree that logic would say it would work but nada.

Conditional formatting can't refresh while the cursor is still in the field (which IS locked then) and the conditional format can only update and determine record lock once the field is exited (thus releasing record lock) - by YOU. If you apply conditional formatting to the RecordOpen file in the referenced link, it'll probably be clearer. In other words, I don't think conditional formatting, using Get(RecordOpenState), would be dependable in a record lock issue (at least pre-10 script triggering) but I could be wrong.

I think this type of restriction (not even allowing another User to view a record) will be difficult to enforce in all situations and transactional models are NOT safe.

Corrected link ... see red above.

Edited February 24, 2009 by Guest
Corrected link

[LaRetta]

... and transactional models are NOT safe.

I spoke in error ... transactional modeling involves more than record-lock issues. It's used to write static data and handle summary fields and even write all data before committing. I was speaking more of the viewer table which some use to allow multiple editing of a single record (and fit along the lines of the discussion) and that's where my mind went.

FileMaker is the best at handling record-locking, in my opinion. And, as a User, it would irritate me a great deal to NOT find a record I'm hunting for, just because someone else was editing it.

[Vaughan]

Yeah... I can only see pain and suffering in implementing a system that "hides" edited records. Lots of work for the db admin, too.

[comment]

Let's not get carried away: there's enough REAL pain and suffering out there. At most, you're talking about a minor inconvenience. I would wait for OP's clarifications.

[bcooney]

Thanks, LaRetta for the clarification (you're way better than Help). I've never used this function, and shouldn't have suggested without testing.

[Fenton]

I was trying to use that Get (RecordOpenState) on IWP, to toggle a button to Open or Submit the record. I was the only one accessing the record. I believe what I found was that it worked in an unstored calculation, but not in the script; or visa versa -|

[Vaughan]

I've been thinking about the problem, or at least a solution.

I've never liked the "flag" method for things like this because (1) it modifies the records, which is a bummer if you're using the modification information to track important things like who made the last (real) change to the record; and (2) the process would have to go something along the lines of open record - set flag - commit record so everybody else knows the flag has been set - open record again and edit, which is 2 edit processes, plus possibly another at the end to clear the flag.

So I got to thinking that maybe the record id could be put into a global field, but that will only work for the current user. Put the record id into a prefs table field... this will lead to record locking issues with many users. So: create a table in which each record holds the id of a record that is being edited and the user account. When the record editing process is finished, delete the record. Also delete the records for each user when they quit FMP, to help keep things clean.

Whatcha think?

[LaRetta]

Well, for goodness sakes! I had included the wrong link above about Get ( RecordOpenState ) and not a person even went there so nobody corrected me.

Here is the thread with the sample file and discussion.