If you want to type in an organisation name into a field and then have it either create a new record or find the existing record, linking the current donor record to that found or newly created organisation record, I suggest first that you set up a text field with global storage for this purpose (can be global) and format it with a drop down list of your existing organization names with auto-complete enabled. That way you can check for existing organisation names by just checking to see if they appear in this list. An OnObjectSave trigger on this field can then perform the following script:
If [ IsEmpty ( Filtervalues ( ValueListItems ( Get ( FileName ) ; "YourValuelistOfOrgNamesGoesHereInQuotes" ) ; Donor::gSelectedOrg ) ) ]
Go To layout [Organization]
Set Field [Organization::OrganizationName ; Donor::gSelectedOrg //This only works if gSelectedOrg is a global field ]
Go To Layout [Organization]
Enter Find Mode  // clear the pause check box
Set Field [ Organization::OrganizationName ; Donor::gSelectedOrg ]
Perform Find 
Set Variable [$OrgID ; Value: Organization::OrganizationID ]
Go To layout [original layout]
Set Field [Donor::OrganizationID ; $OrgID]
Edit Note: removed duplicated line 9/28/2011
Thanks Phil for your very detailed and comprehensive answer. It seems to work *okay* and I definitely couldn't have come up with that on my own so I greatly appreciate your answer but I'm wondering if a further trigger is needed on Donor::gSelectedOrg. What I've noticed is that it will create a record in Organisation okay but when I go to the next record in Donor then it also updates that Donor record to the same organisation_id as the gSelectedOrg.
Since gSelectedOrg is stored globally and the value is therefore the same on the next record layout this makes sense. I think that I need a secondary trigger before that field is entered that updates the gSelectedOrg with the value of organisation_id if that is already set on that Donor record or at the very least sets gSelectedOrg to empty when moving to a new record.
Am I on the right lines here?
Also (newbie question) - how do I insert comments into the script? Because everything is generated automatically I can't see a means to edit it in pure text and I wasn't able to put the #New record comments in.
What you describe is one option. My personal preference would be to have two fields on your layout. gSelectedOrg would be clearly labeled as a search field and it's triggered script can clear this field as it's final step. The second field would be the actual name field from the related Organizations table occurence...
On your last question, if you scroll your list of script steps down to the miscellaneous section, you'll find one labeled "Comment" add it to your script to include scripted comments. Such entries can literally save you hours of effort when you re-visit a script a month later and find yourself asking "what the heck did I do here and why did I set it up this way?". I always include a block of several comments at the top of each script identifying it's purpose, any starting layout or table occurrence that might be required in order for it to run correctly and listing any script parameters that need to be passed to it.
Thanks once again. I still can't get it to work 100% correctly.
In the closing stages of the script you Go to Layout [original layout] and then Set Field [Donor::organisation_id; $OrgID ]
This still seems to be seting the value of all organisation ids in the table to $OrgID and not just the record that was originally open. Is there a further step needed in the script to reopen the original record we were editing?
Perhaps I need to store the current Donor Id at the top of the script save that to a variable and enter Find Mode for the original layout too?
Set Field only modifies a single field in a single record. It is impossible for it to do otherwise unless it is inside a loop that loops through multiple records. Either there is an issue with your layout that makes it look like all records in Donor got the same value or some other process, such as a script trigger controlled script set on the layout, kicked in to modify the other records with the same value.
First, confrim whether or not all the records got the same organization ID value or not. Switch to a layout based on Donor. Do a show all records and examine this field there. Switching your view to table view can make this easier.
I have no idea what I was doing wrong last week but I've just done it again and it all seems to be working fine. I'm still struggling with part of the original problem though which was disabling certain controls. I've enclosed a screenshot of my layout.
Basically I still have 2 problems which I'll attempt to describe in detail:
1. How do I disable the data-entry of a field based on the contents of another field?
2. How can I automatically create new Person records that are linked to the Donor record? (The script I tried didn't work).
The Organisation section (with a yellow background) now works fantastically well using your script. I would, however, still like to disable the gSelectedOrg drop-down if the is_organisation checkbox (in the top blue section) is not selected.
Likewise if the is_organisation checkbox is not selected I would like to be able to enter details in the green section for the person's name. I can't really do this as a search because there are multiple fields and most times this will be a new Person. I tried the script below and adding this to each of the name fields on the onObjectEnter but I this isn't working because these are all fields in another table (Person) so I haven't "entered" them yet (I think).
Here was my attempt at a script:
If [ not ( IsEmpty (Donor::is_organisation) ) and IsEmpty (Donor::person_id) ) ]
# New Person
Go to Layout ["Person" (Person) ]
# I've tried added a value to one of the fields in Person and doing a Commit Records too but to no avail
Set Variable [$PersonID; Value:Person::person_id]
Go to Layout [original layout]
Set Field [Donor::person_id; $PersonID]
#Also tried refreshing the window here for cached join results but also no change
# At the end of this there is no new Person record to link to.
The organisation_id and person_id fields are both in the Donor table but I've disabled modification of these fields because I want these to be updated through scripts. Eventually I'll remove them from the layout but I think they're useful at the moment because it helps with debugging.
Does this make sense? And what am I doing wrong?
1) In what field do you want to disable data entry and what value in what field is the trigger to prohibit/allow data entry?
Do you want to prohibit data entry in "find organization" if the Organization check box is not selected? You can set an OnObjectEnter trigger to run a script that uses Go To Field or Go To Object to move the cursor back out of the field if the check box is not selected.
2) I need a clearer picture of how you intend to use the green section of your layout. How will you determine if a donor is already listed or not in the People table?
Names are not unique, a person can change their name and can also have several variations of their name. (I go by Phil or Phillip pretty interchangeably to give an example of how one person can have more than one version of the same name.) Thus checking to see if a person is already recorded in your People table usually requires more checking and human control of the decision whether or not to create a new people record is often needed. Sometimes a filtered portal is useful for bringing up people with similar names. usually such a portal will list contact info as well as a name to help distinguish similar people records from each other. (and sometimes, if you are doing this by phone, you just ask "have you made a donation before?".)
Thanks once again. I'm very, very happy with the organisation now. It works exactly as I wanted.
For the second issue I don't want to search for a person. I'm going to assume that the person entering the data checked whether a person existed as a Donor before they started entering details. (I may change this in the future once I've added the linked contact details etc).
I just want a record to be automatically created in Person when I click on one of the fields in the green section, if the is_organisation checkbox is not checked and there is no existing linked Person record.
The script would function something like this:
If [ Not Donor::PeopleID and Not Donor::OrganizationID //no need for IsEmpty if both these fields are of type number]
Go To layout [ People (people)]
Set Variable [$PeopleID ; value: People::PeopleID]
Go to layout [original layout]
Set Field [Donor::PeopleID ; $PeopleID]
You should use a button for this script instead of a script trigger on the fields. If the fields above shown in green are from people like they should be, you won't be able to enter one until you've created a link to an existing record in people.