1 2 Previous Next 19 Replies Latest reply on May 31, 2017 8:35 PM by philmodjunk

    Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?

    ezeitgeist

      I have integrated Checkboxes w/Scrollbars into projects. I am in a situation where my goal is to have my scrollbar that can check and uncheck to keep track of items (in this case, props used in a given scene for a theater/film project). I would like the user to be able to check the items used in that scene, AND, if an item is not present, ADD that item to the list and then be able to check it. (And, delete an item added in error.)

       

      It seems like it could be done with adding a couple of global fields, and a button to "add" a new record in the Questions Table. The thing is, I'd like the "props list" to be particular to a given Project (show). The "Respondent" table would be linked to a specific scene in a project, how would I additionally make it project-specific (I do already have a Projects table with Project_ID)?

       

      So, to summarize:

       

      (1) Can I utilize globals & an "add" button that refreshes the portal in its script, to add items live to a checkbox list?

       

      (2) What is the best way to be able to "delete" an item entered in error (that is user-friendly)?

       

      (3) How do I make the items list (Questions table) project-specific (ie. be saved for one project, but be blank at the start up of another project)?

        • 1. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
          philmodjunk

          1) yes

           

          for 2 and 3, do you have a "master list" or "Props inventory" table that lists all available props for all projects or do you need a completely separate list of props for every project?

           

          Or maybe a mix of the two? Such as an inventory of available props kept in storage, but then you sometimes "scrounge up" some special ones needed just for a given project?

           

          These details make a difference in how you would go about building a list of props for a given project and whether you can search an inventory of available props or just have to "start from scratch" with every new project.

          • 2. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
            ezeitgeist

            No master list. Because the wide range of possible items, to have a master list would end up in the many hundreds, if not more, so a completely separate list for each project is the goal (especially if one project only has 2 or 3 props, to have to select them from a list of hundreds might get tiring).

             

            One thought I had is that I can associate a Project_ID with each prop (in the "Questions" table), and then in the portal, filter by the Project_ID. I know this would likely create duplicate props (though they would have different item_IDs), but it would allow me to filter the portal and keep things separated and organized.

            • 3. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
              ezeitgeist

              ALSO: Is it possible to combine Checkboxes with Scrollbars I & Checkboxes with Scrollbars II?

               

              As in, can I have a portal that I can check each item for individual scenes, and then have a "Master List" for full show? (Ie. combines all items (discounting overlaps) into one list field?) -- Essentially listing every "checked" item in the "Questions" table for that show. ... Which, should be, really every item on the list associated with that Project/Show_ID as if it is on the list then it would be used.

              • 4. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                philmodjunk

                Because the wide range of possible items, to have a master list would end up in the many hundreds, if not more, so a completely separate list for each project is the goal,

                I don't think that you really understood the reason behind my question. A list of hundreds, even thousands of items Could be managed--though not with just a simple scrolling check boxes type portal, you'd have to add more search tools to help narrow down the list, such items with specific key words, or items in categories etc. But the  main point here is whether you want/need to manage a props inventory as part of your system or not. I'll assume that the answer is "no" for the moment.

                 

                We have to start with your data model--what tables and relationships are needed to properly organize and work with your data. The CheckBoxes with ScrollBars II example is built around a minimum of three tables: The layout's table, the portal's table and a third table that functions as the "join" table between the other two.

                 

                But I'm having trouble coming up with three tables that will work with that approach from what you describe.

                 

                We clearly have Projects and Props, and we can certainly link props to different projects in a many to many relationship like this:

                 

                Projects----<Project_Prop>----Props

                 

                which for the example in the Adventure #2 file, is reconfigured to look like this:

                 

                Projects----x-----Props-----Project_Prop

                 

                Projects::anyField X Props::anyField

                 

                Props::__pkPropID = Project_Prop::_fkPropID AND
                Props::gProjectID = Project_Prop::_fkProjectID

                 

                But this assumes a pre-existing list of props to list in the portal and select for a given project. If you are building a list of props for a given project "from scratch" each time, then you do not have that list of props from which to select props for a specific project by clicking them on a list in the portal. It would seem just as easy to just have this relationship:

                 

                Projects::__pkProjectID = Props::_fkPojectID

                 

                and use a normal portal where you fill in the list of props to be used for a given project.

                 

                But maybe you plan on using the "check box" method in a different context that I am not understanding?

                1 of 1 people found this helpful
                • 5. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                  ezeitgeist

                  So, yes, to clarify, NO props inventory (each project, props will be based off the needs of a given play/film script).

                   

                  How I have it setup at present is:

                   

                  Projects ---- Scenes List ---- Props List ---- Props List Resp

                   

                  The Scenes List::Scene_ID X Props List:: _pkQuestionID

                   

                  Props List::_pkQuestionID = Props List Resp::_fkQuestionID

                  Props List::gCurrentRespondentID = Props List Resp::_fkRespondentID

                   

                  (I essentially used the Adventure #2 field names to make sure I didn't mess anything up by renaming, at least to start.)

                   

                  Where the specific SCENE is the Respondent, and we are assigning props by Scene, and the Props List is essentially the master props list for that specific Project.

                   

                  I have now setup globals so that if a prop for a scene is not in the Project prop list, it can be added, and then refreshes that portal.

                   

                  The goal is to assign props to each scene (and if it shows up in another scene, to assign it there as well). With the additional ability to print out a Master Props list for all props needed in the entire project (for the Prop master/Art Director), and to eventually add the ability to note what scene numbers (scenes numbered in order of appearance in the script) have a given prop.

                   

                  To make a new Master Props list per Project (ie. start with a carte blanche of props with each project), can I just assign the Project_ID to each prop when it is created as a record and then just filter the portal with the addition of the Project_ID? I have also realized that I cannot be in the Project table, but must be in the Scenes List table for this to function best. Yes?

                  • 6. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                    philmodjunk

                    Please rename your fields. There is nothing magical about the field names and using names like you have just causes confusion.

                     

                    I would start with this relationship:

                     

                    Projects---<Props List

                     

                    Projects::__pkProjectID = Props List::_fkProjectID

                     

                    Enable "allow creation..." for props and you can simply put a portal to props on the Projects layout and enter in a list of every prop that you know in advance that you will need. You'll also be able to add additional props from a scenes layout if you determine that you need another prop at the time you build the list for that scene.

                     

                    I would change your relationships a bit:

                     

                    Scenes List ---- Props List ---- Props List Resp

                     

                    Scenes List::_fkProjectID = Props List::_fkProjectID

                     

                    I used the Cartesian Join operator (x ) in the adventure file because I wanted to list all records in the table. In your case, you only want props that have been assigned to the current project.

                     

                    Your idea of using a global field and a script to add more props should work as long as you assign the project ID to the new Props List Record. I'd then use the same script to create a record in Pros List Resp with both the Scene and Prop ID's so that adding a new prop from the Scenes List layout also adds the needed linking record so that it appears already selected for the current scene--but this is an option, not a requirement.

                    1 of 1 people found this helpful
                    • 7. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                      ezeitgeist

                      Got caught up on a separate project and now back trying to wrap my head around your knowledge.

                       

                      For other purposes, I need to maintain the following relationship:

                       

                      Projects ---- Scenes List

                       

                      Projects::Proj_ID = Scenes List::Proj_ID

                       

                      If I create (and does "---<" mean a normal connection or a "less than or equal to" sign connection [pardon my lack of proper terminology]?):

                       

                      Projects---<Props List

                       

                      Projects::__pkProjectID = Props List::_fkProjectID

                       

                      BUT maintain the Projects to Scenes List table relationship, I have to create a 2nd TO for the

                       

                      Scenes List ---- Props List ---- Props List Resp

                       

                      relationship, yes? I can do that, I just want to make sure I am following what you are recommending and not going off on some crazy town tangent of my own by accident. Why not keep the Projects ---- Scenes List ---- Props List ---- Props List Resp relationship? (As in, what is the advantage of the Projects --- Props List direct relationship if Scenes are also going to have ProjectID?)

                       

                      -------------------------------------------------------------------------------------------------------

                       

                      In my modifying:

                       

                      How I have it setup at present is:

                       

                      Projects ---- Scenes List ---- Props List ---- Props List Resp

                       

                      The Scenes List::Scene_ID X Props List:: _pkQuestionID

                       

                      Props List::_pkQuestionID = Props List Resp::_fkQuestionID

                      Props List::gCurrentRespondentID = Props List Resp::_fkRespondentID

                       

                      SHOULDN'T I JUST CHANGE TO:

                       

                      Projects        ----     Scenes List       ----      Props List                ----      Props List Resp

                      ::ProjectID      =      ::ProjectID          =          ::ProjectID               =          ::ProjectID

                                                                                            ::_pkPropID             =          ::_fkPropID

                                                                                            ::gCurrentScene      =         ::_fkRespondentID

                       

                      TO NOTE: I am creating the list of props per scene from the Scenes List table. I eventually want to select them in the Props List portal with the check marks, and then have a field in the Scenes List table record show up with a list of all selected props in that scene. I do not foresee users needing to enter props outside of when they do the scene breakdowns by scene within this Scenes List table layout. I think that could muddy the props list and I don't trust the organization of the average user.

                       

                      PS: Do I even need Props List::ProjectID = Props List Resp::ProjectID ? If so, do I need to change the ProjPropList Check Select Deselect script to insert a ::ProjectID like it does for _pkPropID?

                      • 8. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                        philmodjunk

                        ----<

                         

                        stands for a "one to many". One record in the left occurrence matches to possibly many records in the right occurrence. >---- conversely, would represent a "many to one" relationship.

                         

                        A relationship between props and projects is optional. You would use it to start a list of props before working with scenes. After all, if you are putting on "the Scottish Play" you know you'll need a dagger, right? So set up that relationship if you find it useful.

                         

                        I've previously asked you to rename things. Using the names from the adventure file doesn't tell me how you will use a given field, table or table occurrence.

                        • 9. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                          ezeitgeist

                          Oh, okay, thank you (on the one-to-many).

                           

                          I thought I had changed them (let me know what is not changed ... I left _fkRespondentID as is but could change to _fkSceneID, if you think that is better? Since I am also going to use this setup as a template for Character List, VFX List, etc., I thought "respondent" would be a nice universal. But I can change it) thus the note of:

                           

                          SHOULDN'T I JUST CHANGE TO:

                           

                          Projects        ----     Scenes List       ----      Props List                ----      Props List Resp

                          ::ProjectID      =      ::ProjectID          =          ::ProjectID               =          ::ProjectID

                                                                                                ::_pkPropID             =          ::_fkPropID

                                                                                                ::gCurrentScene      =         ::_fkRespondentID

                           

                          Though with the question of if Props List::ProjectID = Props List Resp::ProjectID was needed.

                          • 10. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                            ezeitgeist

                            ADDITIONAL:

                             

                            I want to list out the Prop names in the Scene record.

                             

                            I've setup a Calculation field in the Scenes List table. I am using this currently:

                             

                            Substitute ( List ( Scenes List_Resp::_fkPropID) ; "¶" ; ", " )

                             

                            It gives me a comma separated list of the prop IDs of props in the scene. I would like the Name of the props to show up, which exist in my Scenes List table (ie. Scenes List::PropName).

                             

                            Could I just do:

                             

                            Substitute ( List ( Scenes List::PropName) ; "¶" ; ", " ) or do I need to add to the Select/Deselect script a new field in _Resp that takes the PropName from the Scenes List each time and then use that [ie. Substitute ( List ( Scenes List_Resp::PropName) ; "¶" ; ", " )]?

                             

                            I know this relates to what is registered as selected and not, and how I understand (maybe correctly, maybe not) is that the _Resp table is the one that keeps track of those "checked" off items from the related table.

                            • 11. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                              philmodjunk

                              "Should" is a slippery term. What you show is the minimum needed. As I said my last reply, you can put in a Projects to Props relationship (Using another occurrence of props) if you find it useful as a way to quickly put in a starting list of props that you know that your project will need before working through the different scenes. You may find that other occurrences and relationships are needed to complete your solution as indicated by your most recent question.

                               

                              using "_pkQuestionID" and "_fkRespondentID" raised questions as to how you really intended to make this work. The naming convention used in the adventure file is (_fk) or (__pk) , then the table name of the table where the pk is defined followed by "ID". So __pkQuestionID doesn't correspond to any tables in your solution, but does in the adventure file, the same for _fkRespondentID. So _fkRespondentID should be _fkSceneID so that it's clear that this field links the record to a scene.

                               

                              For your last question, you could do this:

                               

                              Scenes----<PropsList|bySceneID>-----Props|bySceneID

                               

                              Scenes::__pkSceneID = PropsList|bySceneID::_fkSceneID

                              Props::|bySceneID::__pkSceneID = PropsList|bySceneID::_fkSceneID

                               

                              This would be in addition to the original set of table occurrences.

                               

                              Then you can use:

                               

                              Substitute ( Props|BySceneID::Prop Name ; ; "¶" ; ", " )

                               

                              You can also use ExecuteSQL to produce this list and not need to add any more table occurrences nor would you need to use the substitute function to replace returns with commas.

                              • 12. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                                ezeitgeist

                                Understood on naming; I have changed the names to reflect specific tables, and will do so going forward. My approach to learning new tasks on FM has been to make sure I grasp the minimum needed first before adding on to it. I definitely see the advantage of the additions, and thank you.

                                 

                                I'd love to save myself adding TO and to use ExecuteSQL instead. I've only used it once before as a way to count number of records that fit a specific criteria. How would I use it to list out items in records that fit a criteria like Scenes List::SceneID?

                                • 13. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                                  philmodjunk

                                  The "simple but brittle" version:

                                   

                                  ExecuteSQL ( "

                                  SELECT

                                     PropName FROM PropsList Join Props ON PropsList."\_fkPropID\" = Props.\"__pkPropID\"

                                     WHERE

                                           PropsList.SceneID = ?";

                                  ", " ; "" ; Scenes::__pkSceneID )

                                   

                                  The "complex but flexible" query using the tools included with the last example of the Adventure #2 file would look like this:

                                  Let ( [

                                  @PropName = SQLRef ( Props::PropName ; "fld" ) ;

                                  @PropsListTBL = SQLRef ( PropsList::_fkPropID ; "TO" ) ;

                                  @PropsTBL = SQLRef ( Props::__pkPropID ; "TO" ) ;

                                  @PropsFK = SQLRef ( PropList::_fkPropID ; "fld" ) ;

                                  @PropsPK = SQLRef ( Props::__pkPropID ; "fld" ) ;

                                  @SceneIDFK = SQLRef ( PropsList::_fkSceneID ; "fld" ) ;

                                   

                                  SQLa = "SELECT

                                                       @PropName FROM @PropsListTBL JOIN @PropsTBL ON

                                                       @PropsListTBL.@PropsFK = @PropsTBL.@PropsPK

                                                       WHERE

                                                            @SceneIDFK = ?" ;

                                   

                                  SQL = Substitute ( SQLa ;

                                  [ "@PropName" ; @PropName ] ;

                                  [ "@PropsListTBL" ; @PropsListTBL ] ;

                                  [ "@PropsTBL" ; @PropsTBL ] ;

                                  [ "@PropsFK" ; @PropsFK ] ;

                                  [ "@PropsPK" ; @PropsPK ] ;

                                  [ "@SceneIDFK" ; @SceneIDFK ] )

                                  ] ;

                                   

                                  ExecuteSQL ( SQL ; ", " ; "" ; Scenes::__pkSceneID )

                                  ) // Let

                                   

                                  I have not tested either query against an actual database--always a good idea when building SQL queries and the names that I used may differ from the actual names in your file. In the first version, I've quoted the _fk and __pk fields to avoid getting a syntax error as FileMaker SQL will not accept a field or table occurrence name starting with an underscore as valid unless you enclose them in double quotes. (The \ is an escape character that enables you to include " as a character inside a quoted string without it being treated as the operator marking the end of a quoted string.

                                   

                                  In the second example, note that the only actual table occurrence and field name references are found between "Let" and "SQLa" plus in the actual ExecuteSQL line at the bottom. Everywhere else, placeholder names are used that do not have to specifically name table occurrences or fields in your file.

                                  • 14. Re: Adventure 2 EVS: Checkboxes w/Scrollbars -- ABILITY TO ADD & REMOVE ITEMS?
                                    ezeitgeist

                                    I am going to attempt the "complex but flexible" version. To clarify the "last example of the Adventure #2" is referencing "Button Bars as Value Lists"? Or do you mean the "Checkboxes with Scrollbar II" (or a different one)? I want to use it as reference so I follow correctly. Thank you!

                                     

                                    Also, for my understanding, you wrote"

                                     

                                    "\_fkPropID\" = Props.\"__pkPropID\"

                                     

                                    Is the first one or second one proper use of the \ and " or does it change depending on an order presented somehow?

                                    1 2 Previous Next