1 2 3 4 Previous Next 52 Replies Latest reply on Apr 17, 2016 6:39 AM by ZoocMan85

    Value List Field Check Boxes

    ZoocMan85

      Hey Everyone ,

       

      I have 3 tables i am working with.

      Customers

      Active Brews

      Tracking Log

       

      On my customer layout i have a field that is titled active brew list which is a value list. This value list gets populated from the active brews table.

       

      This value list shows 2 fields.  Slot ID which is a number 1-100 and the beer name.

       

      Slot ID must be the first value and the beer name must be the 2nd value. I can not change this given how everything is setup.

       

      My Issue.

       

      I have a script that when a user edits the field active brew list which is a check box set to go to to my tracking log table and record what was changed. Here is a copy of the script. My issue is when it copies the field information from active brew list, the check box field, it copies the slot number in the tracking log only. I need it to copy both fields from that value list. How can i do this? Thanks for your help.

       

       

      screenshot.jpg

        • 1. Re: Value List Field Check Boxes
          mikebeargie

          Line 3 - you are clearing the field "active brews list" by not setting the field with any content.

          Line 6 - you are not setting a variable because "active brews list" is now empty.

          Line 11 - Since $newActiveBrew has no content, nothing is set.

           

          Possibly move line 3 until after line 6?

           

          Also, learn how to use the script debugger and data viewer in fm pro advanced. These issues are easily identified by seeing (for example) that line 6 did not successfully set an active variable.

          • 2. Re: Value List Field Check Boxes
            Mike_Mitchell

            If I understand what you're doing correctly, you want Customers::Active Brews List to contain something like this:

             

            1 Coors

            2 Budweiser

            5 Guiness

             

            This shouldn't be necessary. Since you can make a relationship between a return-delimited list and a corresponding field in another table, you should be able to do:

             

            1

            2

            5

             

            and join that to the Brews table's ID field. Then List ( Brews::Beer Name ) will look like:

             

            Coors

            Budweiser

            Guiness

             

            This is called a "multi-key". It's a basic principle of relational design that you shouldn't keep data in more than one place. What happens if you change the name of a brew at some point in the future? All the records that had the old value would have to be updated, too.

             

            If, on the other hand, you wanted (for historical purposes) to know what the brew names were at the time the tracking record was created, then you could use a lookup or auto-enter calculation to copy List ( Brews::Beer Name ) into a separate field in the tracking log table. Or you could use ExecuteSQL to enter it all in a single field (although this makes it more difficult to extract). Or you could use one of a number of functions to concatenate the two lists into a single list.

             

            The really "proper" way to do this is not to use a checkbox set at all, but a join table instead. You have a many-to-many relationship between Customer and Brew: One customer can have many brews, and one brew can be associated with many customers. In such a situation, the standard practice is to use a table in between the two where each record represents the unique combination of Customer and Brew. You'd have, at a minimum, the customer ID and the brew ID in that table, and create the records via either scripting or pull-down menus (depending on your workflow).

             

            Just some food for thought. HTH

             

            Mike

            • 3. Re: Value List Field Check Boxes
              erolst

              Just wanted to add this for completeness' sake:

              Mike_Mitchell wrote:

              The really "proper" way to do this is not to use a checkbox set at all, but a join table instead.

               

              If the only single piece of information you want to store is the association itself (a person is somehow associated with a brew, but you don't want/need to elaborate on that association any further), a list in a field will do.

               

              As soon as the association has attributes of its own, a join table is the proper way.

              • 4. Re: Value List Field Check Boxes
                Mike_Mitchell

                It violates First Normal Form to have multiple facts in a single field. That’s what I meant by “proper” (in quotes).

                 

                However, we all do it at various times, so “proper” will often give way to “expedient”. (That is, the view isn’t worth the climb.)

                • 5. Re: Value List Field Check Boxes
                  ZoocMan85

                  The beers will be changing periodically in those slots. So if in the event if slot 1 is Budweiser at one time and now slot 1 is corona. That's fine. If the beer name changes the checkbox will still be checked cause the beer name was the only thing that changed not the slot number . The purpose of the tracking log is to record what beer that customer drank at the time . So if slot 1 was Budweiser ill know at this date the customer drank this beer, regardless if slot 1 is different at a later date. I'm tracking the completion of slots however for historical data purposes the customer should know what they drank. So this is why I need the beer names to be sent to the tracking log table.

                  • 6. Re: Value List Field Check Boxes
                    Mike_Mitchell

                    I thought that might be the case. You can use one of the several methods I gave you to record that information for historical purposes.

                    • 7. Re: Value List Field Check Boxes
                      ZoocMan85

                      Mike I changed the script but, its still working the same. Here is the screen shot.

                       

                      screenshot 2.jpg

                      • 8. Re: Value List Field Check Boxes
                        mikebeargie

                        Have you tried using the script debugger and data viewer to see where it's going wrong?

                        • 9. Re: Value List Field Check Boxes
                          Mike_Mitchell

                          Line 4 is your problem. It will only fetch the list of brew IDs, not the list of IDs and names you want.

                           

                          Please read through my former post in order to fix the problem. It's not going to be a "tweak three script steps" fix. You're going to have to do more than that.

                          • 10. Re: Value List Field Check Boxes
                            ZoocMan85

                            Mike Mitchell,

                             

                            I do have a relationship between customers and active brews with a field in both titled actve mugs club but its not by beer ID. This is why.

                             

                            In my active brews list its 1-100 ok. So a bar has the option to choose up to 100 beers for a mug club.  However some bars may choose 50 or 60. Within that layout there is a yes check box. If yes is checked then it means that slot is active for the mug club. The relationship between customers and active brews is from a field titled Active Mugs Club. So if the field is YES then it will display in the value list field. Basically I'm filtering my value list to show only the slots that have YES. This is the relationship.

                             

                            screenshot 3.jpg

                            • 11. Re: Value List Field Check Boxes
                              ZoocMan85

                              I know line 4 is my issue but knowing the relationship I have setup now. What is the best way of doing this out of the options in your former post ?

                              • 12. Re: Value List Field Check Boxes
                                ZoocMan85

                                I have done the debugger Mike Beargie.

                                • 13. Re: Value List Field Check Boxes
                                  Mike_Mitchell

                                  I’m not talking about the main database. I’m talking about in your history table / log.

                                  • 14. Re: Value List Field Check Boxes
                                    mikebeargie

                                    and you checked the data viewer after each step to see if a variable was created with the content you were expecting?

                                    1 2 3 4 Previous Next