6 Replies Latest reply on Jun 20, 2016 5:10 PM by BillR_1

    Script for pulling in related records


      I have a database with store locations across many states, and they are related to elected officials in each community through a portal. On each official's page, the user selects from a drop-down list of all stores that are in the district of that official. When they have a governor, there are a lot of drop-down selections they need to make (to cover all the store locations across the entire state). Is there some way to set up state choices that will pre-fill the portal rows with stores from that state automatically? Each store's record has a separate state field as part of its address.


      Thank you,


        • 1. Re: Script for pulling in related records

          Try something like the following:

          1.     Add a Status field to the officials record, if you don't already have one.

          2.     Create a script that runs by script trigger when you enter data into that field

          3.     Set up the script with an If condition, so that If ( Status = "governor" ) stuff happens, otherwise exit the script.

          4.     The stuff that happens would be create a link to each store location in the relevant State (I assume you have a join table between locations and officials).

          • 2. Re: Script for pulling in related records

            Dropdown is a good way of specifying info for a field, when:


            you have less than - say - 20 choices,

            these choices are always the same (user memory of the list leads to faster selection because of predictability)


            dropdowns need a specific action from the user: invoking them by click. So it goes like this


            click to see choices

            evaluate choices

            click to select choice

            (scroll if wanted choice not visible)


            OTOH a portal, already present on the layout, reacting to a change in a field by changing its portal rows, gives immediate feedback about the choice palette having changed, can have a custom sort (which dropdowns can't have) and can include n different pieces of info while a dropdown is best-case limited to 2.


            Before popovers you could argue that portals take space while dropdowns do not, now you can't use that argument anymore. Think user interface before anything else, then find the best way to make it happen.

            • 3. Re: Script for pulling in related records

              Thanks for the suggestions. Keywords - I do have a join table, but I'm not sure how to create the links. That's the where I'm stuck.



              • 4. Re: Script for pulling in related records

                Something like this. I assume that officials—locations is a many to many relationship (any official could be connected to many locations, and any location could be connected to many officials), so you need an officials–JOIN–locations table to break this down. Records in this table need only officialID and locationID in order to make the joins—officialTable::officialID = joinTable::officialID links the official, while joinTableID::locationID = locationTableID::locationID links the location. You should have both relationships set to Allow creation of records via the relationship, and Delete records when the related record is deleted.


                So a script could go something like this:

                Go to Layout [ "the officials layout" ]
                [ "statusField" = "governor" ]  // this condition controls whether the script runs or not

                  Set Variable [ $theState; Value:"get the governor's State from the official record" ]

                  Set Variable [ $theOfficial; Value:"get the officialID from the official record" ]
                Go to Layout [ "the locations layout" ]
                Enter Find Mode [ ]

                  Set Field [ locationTable::stateField; $theState ]

                  Perform Find [ ]

                  Go to Record/Request/Page [ First ]

                  Set Variable [ $counter; Value:1 ] // this is to control the Exit Loop condition


                      Set Variable [ $statesCount; Value:Get ( FoundCount ) ]
                Set Variable [ $locationID; Value:"get the locationID from the location record" ]

                      Go to Layout [ "join table layout" ]
                New Record/Request
                      Set Field
                [ joinTable::officialID; $officialID ]
                Set Field [ joinTable::locationID; $locationID ]
                Go to Layout [ "the locations layout" ]
                Set Variable [ $counter; Value:$counter + 1 ] // update the value in the counter as you move through the records

                      Exit Loop If [ $counter > $statesCount ]

                     Go to Record/Request/Page [ $counter ]

                  End Loop

                  Go to Layout [ "the officials layout" ]

                  End If

                Exit Script



                NOTE: I haven't tested this, but that should give you the general idea—use SetVariable to collect relevant data, use those variables to create the series of join records.

                • 5. Re: Script for pulling in related records

                  Thank you — this looks great. I was getting hung up on the Enter Find Mode - set field - Perform Find portion and couldn't figure out how to find the related records. This is extremely helplul, I really appreciate it.


                  • 6. Re: Script for pulling in related records

                    @keywords The script you provided works perfectly, but I now realize the user may have some instances where they need to select multiple states (e.g. division managers who cross state lines). I tried looking up option, and saw some references to creating a global field, but when I did that on data entry, it made all of the records have those states checked.


                    I've never used a global field before. Thank you in advance for your patience, as well as your help.