3 Replies Latest reply on Jun 1, 2017 12:33 PM by philmodjunk

    Trying to filter down information twice, then insert ID numbers into a separate field.


      Need some help wrapping my head about the best way to create a solution for this:


      I have 9 blue lighting fixtures on three different positions:


      Position 1) Light IDs 1,2,3

      Position 2) Light IDs 4,5,6

      Position 3) Light IDs 7,8,9


      I'm creating "group" records of these lights in a separate table where a user can select two filters to create multiple groups. So for instance, Filter 1 would be something like "Color=Blue" which narrows down 100+ lighting fixtures into the 9 in my above example.


      Then, they should be able to select a Filter2 to narrow them down further. "Position" for instance, which would then break them up into the three groups that I listed above.


      Easy enough to just display this information in a portal, that's not the problem. Problem is that I need to gather the ID numbers of each light and put them into a "contents" text field in the "groups" table so that they can be sent to another program as individual group records.


      For an overall group (let's say Lights 1 through 9) I do this easily by looping through the found fixtures (using filter 1,) grabbing their IDs, and then inserting into my "contents" field separated by commas. That's working properly and isn't a problem.


      The confusion for me comes in when I'm trying to create the further refined groups using both of my filters.


      The above example needs to be able to create the following records in my "Groups" table.

      Group 1 would be IDs 1-9 (Can do this already)

      Group 1.1 would be IDs 1-3

      Group 1.2 would be IDs 4-6

      Group 1.3 would be IDs 7-9


      Any ideas?

        • 1. Re: Trying to filter down information twice, then insert ID numbers into a separate field.

          You really need to describe the data model that you have set up for this. A screen shot of the relationships would be helpful. Even more helpful would be to include a description of how the different parts of that screen shot work with regards to your layout.

          1 of 1 people found this helpful
          • 2. Re: Trying to filter down information twice, then insert ID numbers into a separate field.

            For the purposes of this, assume that these are two unrelated tables. There are more fields in these, but I've simplified down to the ones that are relevant to this problem:


            Table 1 (Lighting Instruments)






            Table 2 (Groups)


            Group #

            Group Name

            Group Content Type

            Group Contents


            Here's what my groups list looks like:



            So to create a new group, you can choose a content type, and then a label. Then the "get contents" button will run the following script, which searches through the Lighting Instruments table, gathers ID numbers, and dumps them into the contents field.

            single group script.PNG


            The reason that I need plaintext ID numbers in this format (1+2+3+4) is because this data gets sent to a theatrical lighting console and that's the only syntax that it will accept for this. That's why this field exists and is formatted this way and isn't just a portal with a relationship between the tables.


            Now, what I'd like to be able to do is to have more levels of automatic group creation:




            On this screen, the relevant boxes are "filter" and "by." In this example, I first want to find all of the lights that are in a particular color, and then create x number of groups based on the "by" field (position). (Note: these search boxes are global records in a separate "Tools" table)


            So going back to my original post, let's say that I have 100 lights, 9 of which are blue, in three different positions of three lights each. Creating the group of the 9 of them is no problem. It's then finding how many unique "Positions" there are in that group of 9. Then, cycling through those unique positions and inserting the ID numbers into new records on the "Groups" table.

            • 3. Re: Trying to filter down information twice, then insert ID numbers into a separate field.

              Not linking this data in a relationship makes no sense.  The list of ID's you describe can be produced via a relationship so that is not a reason, by itself, to not set up a relationship. Such a relationship might make this process simpler and easier to manage.


              But I'm using my phone to respond and that's making it too difficult for me to provide a more complete response to the info that you posted.