1 Reply Latest reply on Nov 17, 2014 2:03 PM by ezellkendrick

    Creating Unique List in one Table from a Non Unique List in another Table

    ezellkendrick

      Title

      Creating Unique List in one Table from a Non Unique List in another Table

      Post

      Having difficulty getting a script to work completely.

      I am attempting to create a table of names (List) that corresponds to another table of names. The List of names is intended to be unique while the table of names may have more that one record with the same first and last name; however each has a unique Id. When adding a name to the “List” if the first and last name match, in each table, it should compare the Id in each table. If Id is different a list of matching names is shown in new window and the new id is added to the Id field (may contain more than one Id) via a button, which corresponds to the appropriate name in the list. If first name, last names and Id match nothing is added. Any other combination of first or last name matching and id not matching would be considered a new name and Id to be added to the list.

      Set Variable [ $FirstName; Value:PrimaryName::Name_First ]

      Set Variable [ $LastName; Value:PrimaryName::Name_Last ]

      Set Variable [ $$NameId; Value:PrimaryName::Id_Name ]

      If [PatternCount ( NamesList::Id_Names_Fk ; $$NameId ) = 1]

      Exit Script [ ]

      Else If [ Case ( PatternCount ( NamesList::Name_First ; $FirstName ) =0 & PatternCount (NamesList::Name_Last;$LastName) =0; 0; PatternCount

      ( NamesList::Name_First ; $FirstName ) = 1 & PatternCount (NamesList::Name_Last;$LastName) =0 ; 0 ; PatternCount ( NamesList::Name_First ; $FirstName ) =0 & PatternCount (NamesList::Name_Last;$LastName) =1 ; 0 ;0) ]

      Perform Script [ “Add New Profile Names To List” ]

      Exit Script [ ]

      Else If [ Case ( PatternCount ( NamesList::Name_First ; $FirstName ) =1 & PatternCount (NamesList::Name_Last;$LastName) =1; 1; PatternCount ( NamesList::Id_Names_Fk ; $$NameId ; 0)) ]

      Show Custom Dialog [ Title: "Name Exists"; Message: "The Profile Name already exists. Verify this is not a duplicate from the following list and add Id by clicking the corresponding button."; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ] *Additional button is listed on layout to insert Id into Id Field.

      Go to Related Record [ From table: “NamesList”; Using layout: “Profile Names List” (NamesList) ]

      [ Show only related records; New window ] *List of matching names First and Last are shown in order to add missing Id to Id Field list for that name.

      Show/Hide Toolbars

      [ Toggle ]

      Pause/Resume Script [ Indefinitely ] *Button on new window closes or resumes script.

      End If

        • 1. Re: Creating Unique List in one Table from a Non Unique List in another Table
          ezellkendrick

          The solution to get the script to work completely was a result of inaccurate If and Else If script steps and are corrected as follows:

          If [PatternCount ( List ( NamesList::Id_Names_Fk) ; $$NameId)]

                    Exit Script [ ]

          Else If [Case (PatternCount ( List ( NamesList::Name_First) ;$FirstName) ; 1; PatternCount ( List ( NamesList::Name_Last) ;$LastName) ; 1; PatternCount ( List ( NamesList::Id_Names_Fk) ; $$NameId) ;0)]

                    Show Custom Dialog [ Title: "Name Exists"; Message: "The Profile Name already exists. Verify this is not a duplicate from the following list and add Id by clicking the corresponding button."; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ] *Additional button is listed on layout to insert Id into Id Field.

                    Go to Related Record [ From table: “NamesList”; Using layout: “Profile Names List” (NamesList) ]

          [ Show only related records; New window ] *List of matching names First and Last are shown in order to add missing Id to Id Field list for that name.

                    Show/Hide Toolbars [ Toggle ]

                    Pause/Resume Script [ Indefinitely ] *Button on new window closes or resumes script.

          Else

                    Perform Script [ “Add New Profile Names To List” ]

          End If