4 Replies Latest reply on Jan 11, 2016 5:49 PM by bigtom

    How to add new records based on value list in script


      Hi all - I need some guidance on how to script adding n records based on values returned from a checkbox set.


      Background: I have a student who's taking three courses: course_1, course_2, course_3. Each course has materials that the student can request. The courses are in the layout 'Courses' and are related via the student's (unique) Student ID. When staff get contacted for materials request, they click a button which takes them to a new layout, 'New Request'.


      In this layout, I have a checkbox set that has its value list pulling from the related layout 'Courses'. This allows the staff to choose from which course the student needs materials (by checking the appropriate boxes).


      Goal: When they click the 'Submit' button, the database will then add new records in another layout, 'Shipments', BUT it adds a separate record for each checked box in the value set, e.g. the student is requested material for course_1 and course_3 --> staff fill out their request form and check the checkboxes for course_1 and course_3 and click 'Submit' --> the script goes into the 'Shipments' layout and adds a new request for course_1 then adds a separate request for course_3.


      What I've done: I created a trash_field with the following calculation: GetAsText ( Courses ) which replaces existing values and always evaluates.


      Thought: I was thinking I could tell the script to look at the lines available in the trash_field and add one record for line 1 and loop to the next line, adding the record for that, and so on and so forth. But I'm not quite sure of the syntax. The other thing I did was I created trash_field_3 with the calculation ValueCount ( Courses ), again replacing existing values and always evaluating. I was thinking I could tell the script to duplicate the newly created record x times (based on that Value Count) but sets the field of those new records with the checked courses.


      Any breadcrumbs would be really helpful.

        • 1. Re: How to add new records based on value list in script

          I am not quite clear on if the materials come in a set for each course or if they can be individually selected. For example Course1 has three material items and the student can request only #1 and #3. Are any of the materials duplicated between courses? Is this system only accessed by staff?


          The easiest way to do this would be along the lines you are thinking. I would use a portal on a special layout and your list of values to loop through portal rows adding new entries. How this gets done depends a little on if the materials are in a kit or individually requested. Doing this with PSOS would insulate the user from waiting or seeing the process. Although the wait should not be long.


          Prepare yourself for parsing text. You will likely be using Pattern Count, Position, LeftWords and RightWords to pull the correct information from your list depending on how it is built and create shipping orders. Possibly ExecuteSQL for certain things depending on the whole setup. You might consider a return delimited list that looks like this in the end:


          C1 M1

          C2 M3

          C3 M1


          This will take some work using a calculated field or global variable. You will need to build the initial list with the purpose of parsing it. This means that you will have to consider you checkbox will add "C3+M1" and using the "+" as a means to navigate. You can keep a separate unrelated table for looking up specific values related to C3 and M1 for creating your shipment requests. For Example to get an item SKU ExecuteSQL( "SELECT sku FROM yourtable WHERE course = ? AND material = ?"; ""; ""; C3; M1)


          There are all different kinds of ways to do this though.

          • 2. Re: How to add new records based on value list in script

            Hey Tom - The actual materials are actually not itemized. It's the courses that seem to be my stumbling block.


            The Student, say John Doe, is taking multiple courses, course_1, course_2, and course_3. The checkboxes for these can be individually checked:


            Student ID




            -Submit-    -Cancel-

            Now, say John Doe calls and says I need stuff for just course_1 and course_3. I would look up John via his ID number and I would check the boxes next to courses 1 and three:

            John Doe


            <sup>x</sup> course_1


            <sup>x</sup> course_3


            -Submit-     -Cancel-


            When I click "Submit", the script goes in and says "Ok, I need to add a shipment record for John Doe for course_1. I can ignore course_2. I need to add a shipment record for John Doe for course_3."

            • 3. Re: How to add new records based on value list in script

              I see. I'm driving right now. Will get back to you in a few minutes.

              • 4. Re: How to add new records based on value list in script

                You need a layout with your temp field and a portal to add new shipment records. Go to this layout after submit button. You will also need the student ID or info but you can pass that though a variable or have the fields in the layout.


                Basically you need one temp field that is used for all the courses. Maybe a global field.


                Each instance of the field will have a different value list.  This way when each check box is checked it will give you a return delimited list in the field. You can use pattern count to determine how many total so you can use a loop in the script n number of times. PatternCount (field; "Course") for example. Set this in a variable.


                Then you need to simply go to last portal row and enter the needed info for a new shipment record. Changing the variables with each iteration through the values in the list. Use Position for this. With the occurrence being your iteration in the loop. You could go to the shipment record layout directly but I have found this method with a portal can be faster.


                I am sure you might want to add some way to track if the materials were already shipped if they are requested more than once.


                If you need more detail let me know.