4 Replies Latest reply on Mar 30, 2014 10:20 AM by chronister

    A little help from my friends


      Hello all,


      I am stuck on a simple task that I hope has a simple solution... I am attempting to script a found set (done) and take that found set (between 4 and 15 ID numbers) and insert that into another table...


      I have attached my script (pdf) for your review.


      I am trying to look up values in ProjectTasksList where projectType = XX and inserting the projectTypeID of each record found into ProjectTasks


      I hope that is clear enough.

        • 1. Re: A little help from my friends


          Please always use a meaningful subject for your question.


          The usual way to transfer data from one table to another is IMPORT.

          You seem to delete the found set before you can import it.

          Your script should:

          - perform the find in the source table

          - make sure its a reasonable number (not all and at least one)

          - import the required fields to the other table

          - delete the records in the source table


          I'm not sure why you move records around while you could simply change the Project type attribute in the existing records?



          • 2. Re: A little help from my friends

            Sorry bout the title.. will use better ones in future.


            Here is the full explanation on what I am trying to accomplish for your understanding.


            I have a table of project types, there are general catagories for each kind of job we are likely to do. This list may change over time. With each project type, there are a list of tasks that must be completed in order to finish that project type. The tables I am working with here are:


            Projects - holds all of the past, current and future projects


            Project Types - Holds list of project types (Screen print, paper, Vinyl etc). Links to Projects Table


            ProjectsTasksList - Holds master listing of the tasks that must be completed for each project type. Links to Project Types table


            ProjectTasks - holds PK's projectID, taskID, (also status and other info about this task) to create a list of tasks for the particular project with only the tasks for THIS project type.


            So that is how the data is stored. In my projects layout I have a dropdown for Project Type. When that is changed the script kicks off and asks the customer if they meant to change this.


            If they say ok, it does the following

            • proceeds to delete any current tasks assigned to this project from ProjectTasks (that is the delete your seeing)
            • then goes out and performs find in ProjectsTasksList to gather the list of tasks that need to be completed for this new project type
            • goes back to the ProjectTasks table
            • I am now trying to insert the found set of new tasks for the newly selected project type, this is where I am getting stuck.


            Any help is appreciated.





            • 3. Re: A little help from my friends

              In this case an import shouldn't be necessary. You can use a script along the following lines:


              Show Custom Dialog ( “Change type?" ; "Do you really … " etc. )

              If ( Get ( LastMessageChoice ) = numberOfTheCancelButton )

                Exit Script

              End if

              # go to layout and delete existing, or just go to layout if no related exist

              Set Variable ( $projectID ; Project::_pk_projectID ]

              Set Variable ( $listTaskIDs ; List ( ProjectsTasksList_byProjectType::_fk_taskID ) // or use ExecuteSQL, if you don't have / don't want this relationship

              If ( not isempty ( ProjectTasks::_pk_projectTaskID ) ]

                Go to Related Record ( Related only ; matching current only ; ProjectTasks ( ProjectTasks ) ]

                Delete all Records [ no dialog ]


                Go to Layout [ ProjectTasks ]

              End If

              # create one new record per taskID


                Exit Loop if ( Let $i = $i + 1 ; $i > ValueCount ( $listTaskIDs ) ]

                New Record/Request

                Set Field [ ProjectTasks::_fk_projectID ; $projectID ]

                Set Field [ ProjectTasks::_fk_taskID ; GetValue ( $listTaskIDs ; $i ) ]

              End Loop


              Go to Layout [ original layout ]

              • 4. Re: A little help from my friends

                That got me to what I wanted, Thank you.