Limit user access to specific fields

[stevelloyd]

Hello,

I'm a small business owner and I've had the insane idea to attempt to develop a DB for my own needs. I've been using a MS-Access DB for the past 15 yrs (which has worked fine, but the consultant who developed it passed away recently) and so I've got a fairly good grasp of the structure and the elements (including all tables and relationships). I haven't got a clue however about how to put in place all the scripts that I'll require and none of the books I've read (FMP11 The Missing Manual, FMP10 Bible, and even FM9 Developer Reference) have been much help in learning where to begin.

Two questions:

1) Any suggestions on where someone with zero experience in scripting can get something useful to work with ?

2) I've got a good part of the layout structure already built, however I'd like to replicate a function that has worked well over the years on my Access DB. On the Customer information layout, there is a field for the "short name" of the customer (a different field from the full name). This "short name" field is useful in two ways:

a) it allows users to find records based on a simple version of the full name (making it possible to keep records of similarly named companies distinguishable).

once the "short name" is taken for one company, it cannot be used as the "short name" for any other company (so it must have the "unique" attribute). It must also be impossible for users to modify the "short name" once it has been established for a record, but it must be possible for users to enter a NEW "short name" when registering a new record.

I suspect that I'll need to use a script to enable the above, but I read something on this forum with a comment suggesting this should be done with RLA (record level access) and privilege set ?

Any help or suggestions would be immensely appreciated.

I'm afraid I'm going to have to make regular use of this forum for other needs, so I'm hoping to find some good advice here.

Thanks

Steve L.

[comment]

once the "short name" is taken for one company, it cannot be used as the "short name" for any other company

Set the field to validate as unique.

It must also be impossible for users to modify the "short name" once it has been established

Why is that?

[stevelloyd]

Thanks for reply.

1) I've already set the field to validate as unique but when I enter the field with the "unique" name and make a change, I can do it which vanifies the sense of establishing a "short name" which is the key identifier for the associated records.

2) I've attempted to set the field to disallow user to override during data entry (on validation tab) but that doesn't work. And if check the "prohibit modification of value during data entry" (auto-enter tab) then it's impossible to enter new names in the field.

[comment]

a "short name" which is the key identifier for the associated records.

The "key identifier" for linking to records in other tables should be CompanyID - an auto-entered serial number, which no user is allowed to modify (and doesn't even need to see).

As you have discovered, Filemaker does not have the granularity required by your second request. You could either place the field in another table (where you can set the restriction to apply to ALL fields), or use a global field for the user entry and a script (running with full access privileges) to set the "real' field. But I still don't see why they shouldn't be allowed to change the name, if they are the ones to come up with it in the first place.

[stevelloyd]

There is already a primary key named "customerID" which is auto-enter serial and non-modifiable (and which is not visible to the users).

The reason that the "short name" key is useful on this DB is because the customer's short name appears in other fields on other layouts, fields in which the space available does not allow for putting the entire company name (GM is more effective in this respect than General Motors, when there is a dozen other companies which begin with "General" and space enough for 8 characters).

Problem is we've been using this type of arrangement for nearly two decades and it is hard to teach a dog to "unlearn" a trick.

[comment]

the customer's short name appears in other fields on other layouts

It should make no difference. You can display fields from related tables on a layout. You can (and should) make them non-enterable, so that users do not modify them accidentally.

You can also make it difficult for users to modify the short name from a layout of Customers; for example you could force them to switch to another layout or tab panel. But this is only a user-interface measure - not a security one.