10 Replies Latest reply on Oct 19, 2016 6:02 AM by philmodjunk

    check box instead of portal


      I am struggling with an issue and hoping I can some suggestions on how best to proceed.


      I have a database that tracks tags for pictures.  each record is a picture, and for each record the customer wants to tag the picture as having various attributes.  So currently we have a PICTURE table that has about 100 different fields that are a simple checkbox.  User checks the box and it means that this criteria is in the picture.  These 100 fields are set using a value list of "1", so if checked the field contains a value of 1, otherwise it is empty.


      Although things are working fine, I'm really struggling with this from an efficiency point of view and wanting to find a way to make the table MUCH more narrow.  and since this table is referenced many other places via portals ... I dont want to send down a ton of data that isn't needed.  The main problem is that a typical picture has only 3-5 tags checked, so out of these 100 fields, only 3-5 are checked at any one time.


      a portal would work beautifully obviously since it would simply create 3-5 related records per picture and the picture table would stay narrow as a result, problem is ... the customer wants a screen of checkboxes and not a portal like interface.  They want to come to a screen with an alphabetical list of all possible tags ... and quickly check the tags that apply for this picture.


      I was toying with creating a one to one relationship with a new table called "PICTURETAGS" and putting the 100 fields in this new table (removing them from the original PICTURE table.  This would make the original table narrow, but seems inefficient again to have 100 fields in a table.


      Then I thought maybe we create a one to many type of relationship but don't show a portal, but rather show the checkboxes and have each checkbox use a different relationship to the foreign table to display a checkmark if there is a related entry.  This would require 100 TO's ... which seems ridiculous as well.


      Lastly I was pondering using a one to many, and using a virtual list on record load to load the checkboxes for each picture.  This I assume would require 100 global fields in the PICTURE table and a script to populate the globals on record load.  This on the surface seems reasonable, but I don't want to introduce a delay as the user is going from one picture to the next while globals are being populated.  I also think this sounds like a lot of work to deal with FINDs (when the user wants to enter find mode and check a bunch of tags to find which pictures match their criteria).


      Has anyone had any experience getting portal type information to appear in a checkbox style look (again, where you see ALL the fields whether they are checked or not).  Maybe I should just keep things as is, it just sits wrong with me knowing these many fields are being used.

        • 1. Re: check box instead of portal

          I have done this with a "portal" of values from a single "list" with a bogus "checkbox". clicking the check really only sets the value of the check in a field that is in the parent. this can become a "multi-line key" and if the value is in this field, then the "check" shows in the portal.

          Since it is a list like you would get if you really have a value list, it will only show those items that are "checked" via a relationship between this field and the portal items. (no need for separate relationships per item)


          You can use the ID (if you have a table) or the value, it mostly depends on what you need to do with the data once it is captured. A "list" (return-delimited) may work, or convert to comma-separated with a Substitute(myfield; char(13); ", " (or similar).

          • 2. Re: check box instead of portal

            Hi Beverly!  Thank you for replying. 


            I'm not sure I'm understanding correctly and have a feeling I wasn't clear with my original question.  Ideally I don't want a portal to appear, but rather they want to see 100 checkboxes with most unchecked and some checked.  They want to see the unchecked values as well as the checked values.


            To complicate matters, this isn't a vertical list, but there are indents and it goes across 3 rows to fit on one screen.  So roughly 25 down, then 26-50 is in the next column, and 51-75 is in the next column.  BUT ... there are indents for some of the items ... so it is more complicated.  as an example ... if the user checks one box it unhides a few other (indented) checkboxes that are similar in nature.


            I mentioned a portal only because it seems like a one-to-many is the logical solution here, but they don't want to see a list in a portal type look.  They want a screen of 100 checkboxes in alpha order.


            I think what you are saying is to have a single field in the parent table that holds all values that the user has checked (not 100 separate fields).  It would be like a typical checkbox field ... so if the user has checked 5 items there would be 5 return separated values in the field.  Then how would the individual checkboxes show an input.


            I feel like I'm over complicating a simple solution, but maybe not???


            thanks again!

            • 3. Re: check box instead of portal

              I use a slightly different version of this what beverly describes. when I do this, the button either creates (to select) or deletes (to de-select) a related record when it is clicked.


              You can set up a table of all your "tags" and then set up these relationships:




              Photos::anyfield X Tags;;anyfield


              tags::gPhotoID = SelectedTags::_fkPhotoID AND

              tags::__pkTagid = selectedTags::_fkTagID


              An OnObjectLoad trigger on the layout keeps gPhotoID, a global field, updated to point to the correct set of records in SelectedTags: Set Field [Tags::gPhotoID ; Photos::__pkPhotoID ]


              A button in the portal row does this:


              If [ IsEmpty ( SelectedTags::_fkPhotoID ) // tag is not selected, select it]

                 Set Field [ SelectedTags::_fkTagID ; Tags::__pkTagID //this is just to create the related record ]


                 Set Field [ SelectedTags::_fkPhotoID ; "" // disconnect selected tag record ]
              End IF


              A "clean up" script can be set to run when the file is closed that finds all records in selected tags where the _fkPhotoID field is empty to do the actual record deletion.

              • 4. Re: check box instead of portal

                ah! more information. Yes, I think a portal will help. See Hierarchical

                something like:


                but when you select a value in the portal, it is being "set" into the parent single field (yes as a checkbox field would), so really there is only the data that is "checked". click again to remove the value from the list.


                getting the portal to show what is checked is a match with the single field as a multi-line key.



                1. portal showing hierarchical 'list' based on a table of values as needed - because it's a portal the value can be 1-30 in portal one, 31-70 in the second version of the same portal, etc. separate if needed as well.


                2. this portal also displays a graphical element simulating the checkbox, but is really a button to set/unset the value to the single field in the parent


                3. there is a relationship between the single field that toggles what the "checkbox" is showing (checked/unchecked) in the portal(s)

                • 5. Re: check box instead of portal

                  Happy Mac

                  This may not be what your asking for but I have situations like this as well.


                  I have only one field that holds the value (each value is unique) with a descriptive word, matching the checked box description.


                  When the box is checked it adds that value to the field, and when uncheck,it removes it.  The check box then has a conditional value that shows checked if the descriptive word is in the field.


                  When it is done I have an easy to read plain English field showing all attributes and only one field instead of 100.


                  Best of luck


                  • 6. Re: check box instead of portal

                    thanks for your thoughts philmodjunk ...


                    I want to make certain I understand your answer. 


                    So on the photos layout, you have a portal showing 100 rows showing related records from tags ... and since it is a Cartesian relationship it shows ALL tags whether checked or unchecked.  In this same portal, you are showing a field from photos -> tags -> selectedTags which acts as the checkmark for the tags that were selected previously.


                    And when a user clicks an tag (from the TAG related record), it runs a script to check if it's that record exists in "photos -> selectedtags by using the global field match. and if so it kills the relationship by removing the foreign matched key field, otherwise it creates a new related record in photos->selectedtags by using the set field command.


                    Am I following you correctly?


                    one question, instead of using a global field for the photoID and a onRecordLoad could you instead simply use the primary key in the photo table to connect to a foreign key in the selected tag table.  photo::pkphoto -> selectedTag::fkphoto?


                    Is there any downside here when trying to search?  If you wanted to find all pictures with three specific tags along with non tag specific photos info ... I assume it would behave as normal non-related fields would behave.

                    • 7. Re: check box instead of portal

                      Tom Droz ... your reply is crafty.  I like it.  The only issue here is that I might have an issue with "unique" values.  I need to ponder this a bit, but simple and slick.  thank you.


                      one question ... you said "when the box is checked it adds that value to the field".  Are you simply using a script trigger to tell the script which value is being added?

                      • 8. Re: check box instead of portal

                        So on the photos layout, you have a portal showing 100 rows...

                        Perhaps, or maybe just a portal of 20 rows with a scroll bar. It might be filtered by filter expression or relationship to show groups of tags organized into categories...

                        In this same portal, you are showing a field from photos -> tags -> selectedTags which acts as the checkmark for the tags that were selected previously.

                        No field at all, just a button that change's appearance via a "hide object when" calculation that hides a "tick mark" object if there is no matching record in selectedTags

                        one question, instead of using a global field for the photoID and a onRecordLoad could you instead simply use the primary key in the photo table to connect to a foreign key in the selected tag table.  photo::pkphoto -> selectedTag::fkphoto?


                        No you cannot. You are using a value in Photos, to match to a set of records in SelectedTags, but from the context of Tags, not Photos so you have to copy the ID from Photos into a global field defined in Tags in order to make the relationship work.


                        For finds, you would ordinarily do a find on a layout based on selected tags and then use a relationship that matches to Photos by PhotoID to bring up a corresponding found set. Hmm, but finding Photos that have all of a specific set of tags selected? That might be a challenge and from a layout on a different occurrence of Photos that links directly to selected tags by ID, yes this would work like searching with criteria entered into records of a related table.

                        • 9. Re: check box instead of portal

                          Happy Mac

                          The unique value could  be the label, but you need to avoid the issue of mistaken pattern matching.


                          3 labels example



                          Very Fine


                          Field Results "Nice, Fine, Very Fine, "

                          The problem here is "Fine," is there twice for pattern matching.


                          My script says if "Fine," is a pattern match, then upon click in the check box, remove it, if it dosent exist, then add it.


                          So care needs to be taken to not have two pattern matches that are identical.  Easy to fix by adding something to the field so the results of the above could be "Nice, Fine, Very Fine2,"


                          The pattern it looks for includes the ","  so now there is only one "Fine,"

                          • 10. Re: check box instead of portal

                            Wouldn't it be simpler just to use a single field formatted with checkboxes?