6 Replies Latest reply on Sep 8, 2014 2:57 PM by millerb

    Script breaking only on Macs


      I'm trying to help a nonprofit on the other side of the country with a FM13 database. A script I created always works very, very consistently on my PC, but is sometimes breaking, apparently in a couple different ways, when folks are using Macs. Unfortunately I don't have access to FM Pro on a Mac, so I can't figure out how to debug this. I see nothing obviously wrong with the script. I'm not using any commands incompatible with Macs. One person who had trouble with the script had full access privileges, so that doesn't seem to be the issue.


      The database has a contacts module that includes both organizations and individuals. Since these need to be treated as different entities in this solution, there are two separate tables, ORGANIZATIONS and PERSONS. In order to allow searching and viewing of lists of both types of contacts, there is a ContactsJoin table. ContactsJoin has a field for its own auto-created ID, a field for _OrgID (foreign key to the ORGANIZATIONS table) and a field for _PersonID (foreign key to the PERSONS table). All the IDs are generated using the "UniqueID" function. Validation for both _OrgID and _PersonID fields is as follows:

      Validate Always

      Unique value

      Validated by calculation:

      for _PersonID: "IsEmpty(_OrgID)"

      for _OrgID: "IsEmpty(_PersonID)"


      This calculation validation just ensures that no join record gets accidentally created that links to both a person and an organization. This shouldn't ever happen anyway, but it did once when I had another faulty script, so I set it up in an abundance of caution. That's not the problem, I don't think.


      When someone creates a new contact, say an Organization, a new record is created in ORGANIZATIONS. After they finish filling in data, they have to hit a "Save" button. This triggers the troublesome script. Here it is:

      • Commit Records/Requests
      • Set Variable [$OrgID; Value: ORGANIZATIONS::_ID]
      • Set Variable [$Name; Value: ORGANIZATIONS::OrgName]
      • Freeze Window
      • Go to Layout ["@ContactsJoin" (ContactsJoin)]
      • New Record/Request
      • Set Field [ContactsJoin::_OrgID; $OrgID]
      • Set Field [ContactsJoin::Initial;Upper (Case (not IsEmpty ($Name); Left ( $Name; 1 ); " -- " ) )
      • Commit Records/Requests
      • Go to Layout ["ContactList" (ContactsJoin)]


      The "Set Field" for ContactsJoin::Initial just assigns the first letter of the Organization name to an initial field for sorting of Organizations and Persons together.


      One person told me they were taken to @ContactsJoin (which is not intended for user viewing) and received a message that _OrgID must be a unique value. However, it should have already been unique, having just been created as a new record in ORGANIZATIONS. This person had full access privileges.


      Another person said they were taken to @ContactsJoin and received the message: "Commit records/request has been canceled. Do you wish to continue with this script?" Then the database froze and they had to force quit. When they reentered, the records had been created (in both ORGANIZATIONS and ContactJoin), but the ContactsJoin::Initial value had not been set.


      Anyone see what could be causing this problem? It appears that in one case $OrgID may not have set to a new value correctly and in another case $Name may not have set correctly, but I can't imagine why, and why only on Macs? Any leads? I could try to upload a copy, but the solution is so big it would take me quite a while to pare it down to the relevant parts. I will if necessary.




        • 1. Re: Script breaking only on Macs

          I don't know offhand why your script would only fail on Macs, and only intermittently, but I can see one glaring problem with it.


          What prevents a user from triggering the script multiple times? A user accidentally clicking Save for a record with an existing join table record is probably the scenario where the validation on unique would be triggered.


          Since you didn't use Error Capture, the user is presented with a strange error message and left stranded on a utility layout, and/or the new record is left in an incomplete state.


          I suggest you either forego the Save button entirely, and instead script the creation of the parent record and the join table record to happen in one go; or add a check like this one to the script …


          If [ not IsEmpty ( ContactsJoin::_primaryID ) ]

            Exit Script

          End If


          … or do both. (And since this is v13, in addition you could use the same condition to hide the Save button.)


          The first approach obviously wouldn't let you set the Initial field, but you could as well create that as a calculation field in the parent tables and use that related field in searches/sorts. (And the way it is set up now, you'd have to monitor the name fields in the parent tables to transfer a change to the initial to the join table)

          1 of 1 people found this helpful
          • 2. Re: Script breaking only on Macs

            There doesn't seem to be any Mac specific things, so it is probably the

            way in which your field validation is acting to prevent records being

            committed.  You can side-step the issue by removing the need for the ID

            to be unique or you can improve error trapping in your script and when

            an error is raised on commit, delete the duplicate.


            The only advantage for being unique, in the use case that you describe,

            is that it prevents data duplication. Duplicates won't appear in Value

            Lists twice, they'll only appear once. If you ever use the join table

            for any sort of relational link, the first instance will always be used

            (and because the second, third, fourth, instance is a duplicate ID it

            doesn't matter which is used).



            • 3. Re: Script breaking only on Macs

              Thanks for your reply. I should have mentioned, the Save button is only visible when a new contact is being created, so the script is only triggered that one time. Otherwise, the child record can be edited/saved without triggering the script. This means the child and parent records are created in one go, at least in the same workflow, though not simultaneously (first the child record, then the parent when the user hits "save"). The user also has the option to cancel/delete the new child record before hitting the Save button and creating the parent. So I don't think that can be the problem.


              I did try an Initial field created by calculation, but it couldn't be used for something I needed, I can't remember now what it was--grouping or relationships? So I had to create the static Initial field using Set Value. I know it doesn't automatically update with a name change. I have yet to cross that bridge!


              I haven't been using Error Capture. I'm a newbie. Where am I supposed to use it? In every script?


              Thanks again.

              • 4. Re: Script breaking only on Macs

                Ah ha. Your answer indirectly helped me realize what the problem is. I was thinking through whether duplicates would be a problem or not, and realized that they would because I have additional fields in the ContactsJoin table that people can enter before they've hit the Save button and created the related parent record. So that's why in some instances a parent record is being created in advance of the script running. I wasn't triggering the problem myself because perhaps in all my tests I didn't happen to put data in those additional fields. So it wasn't a Mac thing.


                I'll just have to separate out those fields and move them to the PERSONS and ORGANIZATIONS tables respectively instead of keeping them in the ContactsJoin table, which I did for efficiency (I thought!). Sorry, I should have described the join table in more detail and then the answer would probably have been immediately obvious to you all.


                Thanks, everyone, for your help. This forum is so helpful. I will also try to learn how to make use of Error Capture.

                • 5. Re: Script breaking only on Macs

                  I did try an Initial field created by calculation, but it couldn't be

                  used for something I needed, I can't remember now what it

                  was--grouping or relationships? So I had to create the static Initial

                  field using Set Value. I know it doesn't automatically update with a

                  name change. I have yet to cross that bridge!

                  You are basing the calc for the initial on the related table, so it

                  cannot be indexed. If you had the calc field in the same table as the

                  name field the calc could be indexed.


                  I haven't been using Error Capture. I'm a newbie. Where am I supposed

                  to use it? In every script?



                  You use error capture whenever you need it. Is that in every script?

                  maybe. Consider having it off by default, that way you get to take

                  advantage of the built-in error reporting mechanisms. If an error occurs

                  you or your users will see a dialog saying "something went wrong" which

                  allows you to trace back to the cause of the problem. You'll quickly

                  learn where you want to capture errors (print dialogs, etc) and, when

                  you do, you use it in pairs, turn error capture on, perform script step

                  ( or several ), turn error capture off.



                  1 of 1 people found this helpful
                  • 6. Re: Script breaking only on Macs

                    Just to complete this discussion...


                    Yes, Malcolm, the initial field is in the parent/join table, because that's the table I need to display as a list of all contacts--whether individuals and organizations--grouped by initial. That's why I couldn't use a calc field in the child tables. When the name of a person or organization is changed, a script trigger updates the initial field in the join table. I also write the full name of an organization and last name of a person to a static namesort field in the join table, and use this to sort by name (not just initial). I guess I could make the initial field a calc based on this namesort field, which would be a little cleaner, though with the same result.


                    To solve the main bug here, instead of moving data out of the join table, I ended up making a popover with some global fields to gather the name data (differnet name fields visible depending on whether a new organization or new person is selected). Then I create records and copy the name data to the appropriate child table and to the parent table "in one go" (one script) as erolst suggested. Then the user gets to the data entry layout where all fields can be filled, wether in parent or child table, without causing any problems.


                    It seems like a lot of people must have the same issues with contact databases that include individuals and organizations and I must be recreating the wheel, but I never found another solution that met the requirements.


                    Thanks again.