4 Replies Latest reply on Jul 31, 2013 2:01 PM by Kaps_1

    Only add non existing records

    Kaps_1

      Title

      Only add non existing records

      Post

           I am trying to add records into a table called "Chapter Top Professions".  A record consists of three fields:-

           * chapter id

           * profession

           * yes no

           However, I only want to add the record if it does not exist already. The two fields chapter id and profession come from related tables chapter and categories. There are relationships between the tables :-

           Categories:category = Chapter top profession: category

           chapter:chapter id = chapter top profession:chapter id

           but it seems to stall. Any ideas ? I attach my code sample.thanks

            

      bni_crm3.png

        • 1. Re: Only add non existing records
          philmodjunk

               A more complete view of your script could be helpful.

               From what layout and what current record are these script steps being executed?

          • 2. Re: Only add non existing records
            Kaps_1

                 Phil - see more of  the script.

                 current layout is Chapter top Professions (chapter top professions).

                 thanks

                 regards

                 Kaps

            • 3. Re: Only add non existing records
              philmodjunk

                   I don't see how that script can possibly work, but the more I see, the more questions I have about how it is supposed to work. I see 8 different set variable steps before you get to the Loop step. All load script variables (scrfipt variables are named with just one $) from data in other script variables. Script variables do not retain their values when a script exits/halts/completes and I do not see any scripting that shows how these variables such as $Categories receive data.

                   Then you refer to related data in other tables, Chapters::Chapter ID and Categories::Category ID

                   but there is nothing in the script that selects/finds a record or modifies values in match fields to control how the current record in Chapter Top Professions will or will not match to records in those related tables.

                   And if I am understanding correctly, that you want to create a new record Chapter Top Professions for a specified Chapter and Category ID if it does not already exist, then you really shouldn't be on a layout for Chapter Top Professions at all if you intend to use relationships to manage this task.

                   Instead, you could use one of the following options:

                   Perform a find on Chapter Top Professions with set Error Capture turned on specifying the desired values for chapter and category ID. If no records are found (get ( FoundCount ) = 0 ), create the new record. You may find this link on scripte finds useful: Scripted Find Examples

                   Add some global fields to temporarily match by chapter and category ID to the Chapter Top Proffessions table. In your script, you then set your two fields to the desired values and then check to see if there is a matching record. If you enable "allow creation..." in this record a single set field step setting any field in Chapter Top Professions to a value will create a new record.

                   The relationships might look like this:

                   LayoutsTableOccurrence-------Chapter Top Professions

                   LayoutsTableOccurrence::gChapterID = Chapter Top Professions::Chapter ID AND
                   LayoutsTutorial: What are Table Occurrences?::gCategoryID = Chapter Top Professions::Category ID

              • 4. Re: Only add non existing records
                Kaps_1

                     Thanks (as ever). I like the global field approach - as there are 600 chapters and 40 categories and doing 24,000 finds will be incredibly slow. will investigate and get back to you if any problems.