2 Replies Latest reply on Mar 1, 2014 6:31 PM by tomchandler

    Script to populate a join table with all the records from one parent


      I need to know how to populate a join table with all the possible records from one of its parents. I assume I need a script.



      I am creating a restaurant inventory database. I have multiple restaurant locations in a Restaurant table. I have multiple products in a Products table. I have a join table to record the inventory level of each product at each restaurant. I have a layout for the restaurant with a portal that points to the join table. I would like to create a button on the layout so users can quickly add all the products to the join table, and then they can easily enter the inventory quantites in the portal. Any suggestions?

        • 1. Re: Script to populate a join table with all the records from one parent

          tomchandler wrote:

          I assume I need a script.

          You could ask the intern to do it manually …


          Anyways, the method I favor captures all productIDs in one variable (ExecuteSQL comes in handy here), and – if records need to be created – the restaurantID in another, goes to the join table and processes the list


          Here's a code example for the first approach, which also prevents creating duplicate entries in the join table. After the initial creation, further invocations of that script will simply do nothing, since $toDo will be empty – unless you have added to the product palette in the meantime. But if you want to update all restaurants to a new product, you should create and call a similar script from the product side.


          Set Variable [ $already ; List ( JoinTable::productFK ) ]

          Set Variable [ $toDo ; ExecuteSQL ( " SELECT productID from Products WHERE productID NOT IN (" & Substitute ( $already ; ¶ ; "," ) & ")" ; "" ; "" ) ]

          # assuming your IDs are numeric; string values need to be wrapped into single quotes within the test array

          If [ IsEmpty ( $toDo ) ]

            # all existing product IDs already have join table entries for this restaurant

            Exit Script

          End If

          Set Variable [ $restaurantID ; Restaurant::restaurantID ]
          Go to Layout [ joinTable (joinTable ) ]


            Set Variable ( $i ; $i + 1 ]

            New Record/Request

            Set Field [ joinTable::productFK ; GetValue ( $toDo ; $i ) ]
            Set Field [ joinTable::restaurantFK ; $restaurantID ]

            Exit Loop if [ $i = ValueCount ( $toDo ) ] 

          End Loop

          Go to Layout [ original Layout ]


          You can write this script without using ExecuteSQL(), but then it's a tad more convoluted.