Changing data in a field if another field changes

[LaRetta]

Hi everyone!

FM6, WinXP: I have a text field (custom Value List) called Status. If a User changes the status of a Client from "Active" to "Terminated", I would like another field (ClientShortcut) to change to blank.

Well, I've written a script (see below) but when I attach it to the Status field, the pop-up list no longer functions and a User can't even CHANGE the Status. I tried changing the Status field to a validation by calculation (only if modified) to Status = Case(Status="Terminated", ClientShortcut=""), but that causes more problems ... when User tries to change Status from "Active" to "Terminated" message says 'field can only accept specific values.'

This script works but I don't know how to 'activate it' when the data changes.

Remove Shortcut script:

Allow User Abort [ Off ]

Set Error Capture [ On ]

If [ Status = "Terminated" ]

Set Field [ ClientShortcut, "" ]

Any ideas on forcing the script to activate? Or is there better way of handling this?

LaRetta

[Lee Smith]

Hi LaRetta,

I don't use a lot of scripts for things like this. So, I would make the second field a calculation based on the first field. That way, no one has to remember to click a button.

However, if I were to use a script here, I would make the entire entry a script. Set field (Status "Terminated") Set Field( ClientShortcut, "")

HTH

Lee

[LaRetta]

Hi Lee!

Thanks for the suggestion. I don't want a User to have to click a button - they'll forget! Just like they will forget to clear the ClientShortcut field. I'm not crazy about adding (possibly) unnessary calcs, either

And since this is a Value List pop-up, I'm unsure of its sensitivity and reliability, when being manipulated via script (?). I considered somehow using the Status ValueList with a self-join but I have no idea how I could make that work.

But you've certainly given me some things to consider and I thank you for your ideas!!

LaRetta

[andygaunt]

LaRetta,

IF you have access to a plugin such as TROI activator, oAzium Events or SCRIPTit, then you can script the exit of that field to check and if the status is terminated get it to clear the field for you.

I can't remember if you said you have one of these plugins in our previous conversations.

If you do, it saves all those extra calculation display fields we usually have to create.

[LaRetta]

Hi Andy

Thanks for the information. I have TroiFile, but I will certainly check into TroiActivator. I appreciate your suggestion!

LaRetta

[LaRetta]

I just tried this script and it appears to work!?

Set Error Capture [ Off ]

Go to Field [ Status ]

[ Select/perform ]

Pause/Resume Script [ "0:00:02" ]

If [ Status = "Terminated" ]

Set Field [ ClientShortcut, "" ]

End If

The pop-up list (or menu (?) must not allow entry (on field format behavior) and I attached the script to the field. Does anyone see any unforseen problems with using this?

Update 1 hour later: Only one problem ... when I perform a manual find on Status, this script activates, then it *sticks* in find mode even when I hit enter (or [return] for MACs?). With Status Area displayed to try to watch the process, the find button remains active. Poop! Suggestions?

LaRetta

[Lee Smith]

Hi LaRetta,

What's in your field "ClientShortcut" if you haven't Terminated the client?

Lee

[LaRetta]

Hi Lee

The ClientShortcut field is text, field defs validation set to max 7 letters, must be unique and its indexing is on. It's also field-formatted (text-all caps).

What were you thinking? Oh please share!

LaRetta

[Lee Smith]

Hi LaRetta,

Too Bad. I was hoping that it was anything other than a unique identifier.

So here is another stupid question, why are you clearing it out then?

Lee

[LaRetta]

Hi Lee,

This is a shortcut to a Client ID, for data-entry staff. They don't have to lift their fingers off the keys to click the mouse on pop-ups etc. This shortcut code is then reassigned as Clients come and go (are Terminated). The text field must be unique, max 7.

Automatically resetting the shortcut (which is replaced with a ClientID relationships), allows us to keep the shortcut key, well ... short

Regardless of whether this is the best solution to this particular dilemma, I want to understand this one ... *IF* it can work, I'd love to use it with many fields to auto-update and validate data-entry.

LaRetta

[CobaltSky]

Hi LaRetta,

In FileMaker there are always several ways to achieve a given outcome. Andy's suggestion (eg using Activator et al) will work. Your paused script will also work. However there is a quite different approach you might like to consider which might have a few advantages. Here it is:

1. Create a text field called 'DefaultShortcut' and use the 'Replace COntents' command to set it to hold a copy of the contents of the shortcut field.

2. Change the shortcut field to a stored calculation with the formula set to:

Case(not Status = "terminated", DefaultShortcut)

You'll find that this method is considerably simpler to implement than either of the other two. You'll also notice that it uses far less code and less processor cycles and operates more smoothly than the paused script. Beyond that, it has the advantage that the shortcut is not lost when a person is terminated, so that if the record is reinstated later (either because the person returns or because the change of status was a mistake), the same shortcut is automatically 'remembered' and restored.

[LaRetta]

Hi Ray!

Thank you for the ideas. I'm always looking for the best way to handle everything. I'm interested in your suggestion (and Lee's) to use a calc but there is one little hold-up ...

I WANT the ClientShortcut to be reused once a Client is terminated. Example of shortcut: ClientName - Daniel Huntington, Shortcut HUNTDAN. This is very easy for the User because the shortcut doesn't need to be looked up (data-entry KNOWS the ClientName). These shortcuts are not stored only used to quick-select and insert a ClientID. If I use your Case calc, the Shortcut isn't blanked and, since it MUST be unique, I won't be able to re-use it. With 500 active Clients at any given time, these shortcuts are in demand. If Danielle Hunt becomes active, I won't be able to assign the BEST code. I hope my reasoning is making sense.

Is there any way I can get the best of both worlds? Set the ClientShortcut to "" AND handle it easier than scripting or plug-ins? Thank you so much for helping me!

UPDATE: Maybe my thinking is wrong on the Unique part. A shortcut must be unique but only when assigning to "Active" Clients. Sooo, if I could change the ClientShortcut field to validate by calc that, if Client Status is ACTIVE, shortcut must be Unique amongst the active Clients. Then I could leave the old shortcuts in the terminated clients and it wouldn't matter. Can we work with this? Of course that brings up the problem during data-entry in Services. Because the User could then enter a Service on a Terminated Client. And then I'll need to handle validating a Client Status while entering Services.

I hate to say it because I love FM, but even Lotus Approach allowed attaching scripts to fields that activated upon field exit or upon changes applied to other fields on the fly.

LaRetta

[CobaltSky]

Not a problem, LaRetta. Since you don't want a shortcut to be automatically reinstated if it has since been allocated to another client, simply create a value list of shortcuts (if you don't already have one) which uses values from the shortcut field, then use a formula along the lines of:

Case(Status = "Terminated", "", PatternCount("

[CobaltSky]

For the sake of clarity, attached is a mini test file which shows how the suggested calc scenario would work. It includes custom validation messages to stop the user from proceeding unless the (active) shortcut is unique.

However the 'default' shortcuts for terminated clients are not required to be unique.

HTH

Shortcuts.zip

[LaRetta]

Oh Hi Ray!!

This is truly incredible!! This solves ALL my problems at once! And I really didn't want to have to use a script. I don't like even adding a calc, but if the calc solves so many problems for me, it's worth it. Many times, I think I create calcs because it's the only solution I know ... when in reality, another solution would be even better.

But in this situation, there's no doubt that this is the best way to go! You have again saved my buns

LaRetta