4 Replies Latest reply on Oct 31, 2015 4:48 PM by disabled_morkus

    Creating a picklist?

      I have a vendors table which is M:M with a category table (one vendor can have many categories and vice versa). And, there is a VENDORS-CATEGORIES join table.

       

      On a "Vendors" layout I have a portal where I can add multiple categories, as expected, for each vendor. Yet, as with portals, I have to manually add each row of the portal for each vendor. So, if a vendor has three categories (for example, electrical, mechanical, and other), I would have to manually create three portal rows. It all works OK, but ...

       

      What I would much prefer is if I could have, say, a separate tab on the Vendor's layout where I could see ALL the categories possible and then click off the ones that apply to this vendor (say, with check boxes).  Then, be able to go to the next Vendor and select the categories, again from the list of all possible categories, that apply to this vendor.

       

      Without coding something custom with lots of scripts, I'm not sure how to do what I want.

       

      Am I missing something that FM makes easy?

       

      Thanks in advance,

       

      - m

        • 1. Re: Creating a picklist?
          jbrown

          Good morning.

          This is completely possible to do using scripts. FileMaker is meant to be easy to get scripts written, relationships set up, and layouts created, but it still needs some actions, so you do need to write the script to get it to function.

           

          Your request is possible. One or two scripts should suffice. Would you like some guidance in that?

           

          Here's what I'd do:

          1. Assuming you're using FM 13 or greater, I'd create a popover on the layout.

          2. Create a field called "Categories" in this Vendors table. Make it global. Put it inside the popover.

          3. Create a value list that shows all the possible categories. Set this global field to this category.

          4. Inside the popover, a button is needed that will run a script called "Set categories".

           

          The script "Set Categories" needs to do some work. It needs to get all the values a user selected and create a child record in the join table. It would grab the ID of the current-record vendor, go to the join table layout and create a record for each category selected using a loop and GetValue() function. Then it would go back to the original layout (the vendors).  Finally it would clear out the global field so as to reset for the next vendor.


          After that a new record would be in the portal for each selected category.

          • 2. Re: Creating a picklist?

            Thanks. I'm in FMP 13 so I should be good to go with your suggestion.

             

            I was pretty sure I'd need to do something like this.

             

            Appreciate your reply.

             

            - m

            • 3. Re: Creating a picklist?

              Your idea worked fine. Works great in fact.

               

              I'm amazed how awful the FMP script interface is compared to any real programming IDE. Not object oriented, no "initellisense" (of course, since not OO), zero refactoring (not even search & replace, SERIOUSLY?), etc. Plus the terrible script interface itself. Just mind boggling slow and cumbersome.

               

              Plus the script debugger, data viewer are somewhat model vs. other windows so you can't always open them. Data viewer isn't really good for evaluation unless you close lots of windows.

               

              Well, I know they changed the script interface in 14, but, well, doesn't really address much in my view ....

               

              Appreciate your reply. I was basically trying to do a sanity check to make sure I needed to do the scripting and not having missed an easy way FMP already does things.

               

              Thanks again!

               

              - m

              • 4. Re: Creating a picklist?

                I was probably a bit too critical of FMP above. FMP is actually an amazing product, which I use for many, many things. Although I don't use it for production work, it's an amazing product and I'm glad I have it!

                 

                Quick user interfaces and the ability to tie things together quickly with scripts, etc. make it a super usable product.

                 

                I'm hoping that FMP 15 addresses some of the things I've been hoping for since version 11.

                 

                Because of all the great FM users, this forum is one of the best, with the best peer user support, on the Internet.

                 

                - m