9 Replies Latest reply on Oct 4, 2011 9:25 AM by philmodjunk

    Creating links automatically for related portal records



      Creating links automatically for related portal records



      I'm trying to do something with a portal that I think should be quite easy but can't for the life of me work out how to do it.

      I have a table called Donor. A donor can either be a Person or an Organisation. So I have created two other entities with primary keys organisation_id and person_id. I'm trying to link these to Donor so that if the is_organisation checkbox is selected only the portal to Organisation is active and if it isn't only the portal to Person is active.

      The Person table then contains fields such as first_name, last_name whilst Organisation contains organisation_name.

      I've created the fields organisation_id and person_id in Donor as well and created the relationship so that Donor.organisation_id = Organisation.organisation_id etc. I've also enabled Allow creation of records in this table via this relationship on the Person and Organisation side. Normally I'd link Organisation and Person back to donor_id but I may want to use Person and Organisation for other entities in the future.

      When I'm in the Donor layout however I can't save any modifications to the Organisation because the organisation_id has not yet been set. I suspect that I need some kind of Script trigger after editing the organisation_name that creates a new record in Organisation and updates the organisation_id in Donor with the correct value. Ideally, I'd also like to do a search on Organisation to see if organisation_name already exists; if it does use this record otherwise create a new record.

      How do I go about this?

      Thanks in advance,


        • 1. Re: Creating links automatically for related portal records

          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 ) ) ]
          #New Organization
             Go To layout [Organization]
             New Record/Request
             Set Field [Organization::OrganizationName ; Donor::gSelectedOrg //This only works if gSelectedOrg is a global field ]
             #Existing Organization
             Go To Layout [Organization]
             Enter Find Mode [] // clear the pause check box
             Set Field [ Organization::OrganizationName ; Donor::gSelectedOrg ]
          Perform Find []
          End If
          Set Variable [$OrgID ; Value: Organization::OrganizationID ]
          Go To layout [original layout]
          Set Field [Donor::OrganizationID ; $OrgID]

          Edit Note: removed duplicated line 9/28/2011

          • 2. Re: Creating links automatically for related portal records

            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.


            • 3. Re: Creating links automatically for related portal records

              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.

              • 4. Re: Creating links automatically for related portal records

                Hi Phil,

                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?



                • 5. Re: Creating links automatically for related portal records

                  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.

                  • 6. Re: Creating links automatically for related portal records

                    Hi Phil,

                    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) ]

                        New Record/Request

                        # 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

                    End If

                    # 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?

                    • 7. Re: Creating links automatically for related portal records

                      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?".)

                      • 8. Re: Creating links automatically for related portal records

                        Hi Phil,

                        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.



                        • 9. Re: Creating links automatically for related portal records

                          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]
                             Freeze Window
                             Go To layout [ People (people)]
                             New Record/Request
                             Set Variable [$PeopleID ; value: People::PeopleID]
                             Go to layout [original layout]
                             Set Field [Donor::PeopleID ; $PeopleID]
                          End If

                          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.