auto-enter external username on Modify

[cwilcox]

We're (finally) migrating from FileMaker account to using Windows-based external authentication. Many of our tables have "Created By" and "Modified By" fields that are auto-entered using the Creation and Modification Account Name options, respectively. I was aware of the fact that the account name can appear in multiple forms (username, DOMAINusername, [email protected], etc.) so I wrote a custom function to remove everything except the username. Instead of the built-in options, I set the fields to auto-enter a Calculated value containing my custom function. This works in both "Created By" and "Modified By" fields when I create a new record but when I change a record the "Modified By" field is not updated.

 

If the "Modified By" field's auto-entered value is from a native function, it works so I'm assuming something is wrong with my custom function. Here's the function:

Let (
[
    $getAccount = Get (AccountName);
    $backslashTest = Position($getAccount; "";1;1);
    $atTest = Position($getAccount; "@";1;1)
];
    If ($backslashTest > 0;
        Right($getAccount; Length($getAccount) - $backslashTest);
        If ($atTest > 0;
            Left($getAccount; $atTest - 1);
            $getAccount
        )
    )
)

If the problem isn't with the custom function code, where should I look next?

 

[mr_vodka]

Perhaps confirm to see if the "Do not replace existing value..." is Unchecked.

 

Also, you would need something to trigger the field to update the value. You probably want to reference an actual modification field using the native modification feature such as a timestamp etc.

 

For example....

 

Let ( trigger = zz_timestamp_modify; the rest of your calc )

[cwilcox]

Yes, "Do not replace existing value of field (if any)" is unchecked. Thank you, your suggestion worked and I changed it a bit. Instead of adding a Let() to the Calculated value, I changed the custom function and pass the timestamp field as a parameter. I went this way because I also decided to pass the account name as a parameter instead of calling Get (AccountName) within the custom function. This way I can use the function if the account name ended up somewhere else for some reason.

[HUIT]

cwilcox and I are colleagues, and this is a followup post for the general public...

 

I have further modified the custom function to be:

GetAccountName ( )

Let ([
  name = Get ( AccountName ) ;
  bsPos = Position ( name ; "" ; 1 ; 1 ) ;
  atPos = Position ( name ; "@" ; 1 ; 1 )
];
  Case ( 
    bsPos > 0 ; Right ( name ; Length ( name ) - bsPos ) ; /* begins with domain name, e.g. "fas_domain" */
    atPos > 0 ; Left ( name ; atPos - 1 ) ; /* ends with FQDN, e.g. "@ad.fas.harvard.edu" */
    name
  )
)

The calculated value for the "modified by" field is:

Let ( trigger = <insert name of modified field> ; GetAccountName ( ) )

The trigger field above can be any field that, when modified, will trigger the account name update.  If a solution requires that the "modified by" field be updated anytime any field in the record is modified, simply make the trigger field a modification timestamp field (which gets updated every time any field in the record does).

 

 

If anyone prefers cwilcox's original sollution (not shown above: passing modified account and timestamp fields into the function), they should be careful to wrap their fields with GetFieldName ( ) wherever the custom function is called.  This way, things dont break if the name of either of the fields has to change down the road.

 

[LaRetta]

I don't use Account Name at all after the immediate sign in. 

 

The first time someone changed their account name cinched it for me (I know they shouldn't but we cannot control those issues and we MUST control them).

 

Upon login, I immediately find the User's StaffID (I store the account name in their staff record; never their password).  Then I auto-enter their StaffID for creation and modification information.  It produces smaller footprint (number over full name text through thousands of records) but it also protects from name change.

 

Three times over the past 5 years, this has bitten someone I've worked with and it sadly is not uncommon, true example:  Someone has NOT found critical records to pay a staff person according to their invoices because the person changed their account name from Patricia to Patty partway through the year.  They only found 3/4 of the records.

 

Using an Account Name to find someone's records is untrustworthy just as it is using a customer name as a key field and for same reasons.  Just something to consider for future.

[comment]

Upon login, I immediately find the User's StaffID (I store the account name in their staff record; never their password).  Then I auto-enter their StaffID for creation and modification information.

 

That's a good point.

 

Another option is to store multiple account names in the user's record, say as a return-separated list. This reduces the complexity of day-to-day operations (you can use the native 'Auto-Enter: Account Name' mechanism as is) at the expense of increasing the complexity of those cases where you actually need to associate the auto-enter with a real person.

[LaRetta]

In thinking it through, if someone wants to find Invoices for a staff person, they will want to search the Invoices table they are in for who created the record.  That, in itself, is not an issue - we decide what fields they see.  It would mean establishing a relationship to Staff from LineItems and searching through to the staff table ( if I understand correctly which isn't always true ).  Since the data is stored it would be a fast find ( same is not true with sorting related ).  

 

However, it would mean adding a Staff table occurrence to whichever tables you wish to search through which isn't necessary if you store the ID and have a Staff_all value list where you can then display their name in a locked pop-up.  You can then use the popup for the search in the native table, searching a number field.

 

But I still think that is a viable option, thank you!  I should mention that I set a global field with staffID ( set it in Data file if separation ) to use for auto-enter and set global variable with staff name for display as merge variable throughout UI.  I am going to think through your suggestion further, Michael, because I might be missing other considerations. 

[comment]

It would mean establishing a relationship to Staff from LineItems and searching through to the staff table

 

Not necessarily - you could search the table with multiple requests, one for each account name in the user's history. However, a simple search for "similar", say by invoking Find Matching Records in the LineItems table, would fail to produce the records created/modified by the "other" names of that user.  That's what I meant by "increased complexity".