5 Replies Latest reply on Jul 22, 2013 2:10 PM by philmodjunk

    Different user display items vs database table items causing issues

    ShaneB

      Title

      Different user display items vs database table items causing issues

      Post

           I have designed my database structure to have Observations have a dynamic number of tags, in this case the tag categories are ObservationAttributes and ObservationContent. Each observation has an __observation_id_pk that is the foreign key on the ObservationAttribute and ObservationContent tables. As multiple observations can have multiple tags, the tables consist entirely of foreign keys that will just list the observation_id and the content_slug when appropriate. The content_slug is designed to be easier for the system to manage, with no uppercase letters or spaces. For instance "Manager Behavior" would be manager-behavior. The display terms are the attribute_display and content_display terms, which are matched in columns to the __attribute_slug_pk and __content_slug_pk terms on the AttributeTags and ContentTags tables.

           I have figured out how to have the drop-down list use the display terms from attribute_display and content_display, but after the drop-down term is selected the field then shows the system term from the attribute_slug and content_slug. I would like my users to see the display term in the field EVEN THOUGH the system is storing the matched observation_id_fk and attribute_slug_fk on the appropriate tables.

           I have attached an image of the database structure. Any help is appreciated.

      database.PNG

        • 1. Re: Different user display items vs database table items causing issues
          philmodjunk

               Option 1:

               Use a pop up menu format instead of drop down list. Then the 2nd field value (apparently the _display field) will be what shows when you move on to the next field or record.

               Option 2: (This one's sneaky, I'll use the "attribute" tables in my example and leave it to you to generalize the method for use with the "content" tables)

               Add the Attribute_display field to your layout. Use the Inspector's behavior settings to deny access to this field when in browse mode. Give it an opaque fill color and place it directly on top of your drop down list formatted field so that the drop down list field is fully hidden from view. If you want the drop down arrow to be visible and this is FIleMaker 12 with rounded fields, give the attribute_display field a drop down list format so that you can specify the drop down arrow so that it looks like a drop down list. If the field is rectangular as you find in FileMaker 11 or in Classic themed layouts in 12, you can just resize this field so that only the arrow from the drop down list field is visible.

               When you click on this field, the drop down list field hidden behind it pops to the front and you can use the drop down list to select a value. When you select a value, the drop down list disappears back behind attribute_display and attribute_display then shows the more nicely formatted version of this information.

          • 2. Re: Different user display items vs database table items causing issues
            ShaneB

                 Awesome. I wonder why the pop up menu format works that way.... but it does.

                  

                 OK - another related question. How can I allow for a "+" type button next to sit next to the pop up menu(s) to allow for a dynamic number of tags to be assigned to each observation_id? So if a user wanted 1 tag they would just use it once, but if they wanted 2, or 3, they could hit the + and a new field would pop up. Does this make sense?

            • 3. Re: Different user display items vs database table items causing issues
              philmodjunk

                   Not really. I'd use a portal to the join table for recording observations and then you can add as many tags as you want by entering/selecting each one on as many rows of that portal as you need.

              • 4. Re: Different user display items vs database table items causing issues
                ShaneB

                     That worked great. However, I am trying to figure out how to allow a user to delete or remove a term once chosen from the drop-down list, and also how to restrict duplicate entries of the same term for individual records. For instance, I do not want observation 7 to be able to have the same attribute more than once. I'm going to guess there is a validation routine for this, but since I need the __attribute_slug_fk to only occur once for each __observation_id_fk on the ObservationAttributes table I am not sure how to specify this.

                • 5. Re: Different user display items vs database table items causing issues
                  philmodjunk

                       To delete a term, you use delete portal row to delete the join table record. If you set up a text field with an auto-enter calculation that combines the selected value with the correct foreign key, you can specify a unique values validation on that auto-enter calculation.

                       This demo file, may be useful to you: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7