11 Replies Latest reply on Feb 11, 2014 1:11 PM by leebrock

    Use list values to group records FMPro11?

    leebrock

      Thank you for your help.

       

      I am sketching out a little data base for the real developers to use as an example of what we need.

       

      I have a single file DB with contact information on each service provider. One of the fields is SERVICES which contains a checkbox value list of 10 different services a service provider might offer. One could check any number of services or all of them.

      I've created a staff directory layout which I can sort by last or first name and I also need to be able to group the directory by category, i.e., each value held in the list in the field SERVICES would be a single category.

       

      I have a layout part, (sub-summary when sorted by SERVICES) above the list of names in my directory layout to group the categories but it's not working. The way it is intended to work is that each category header (values from SERVICES) would be listed with each provider who has that value checked, listed under the summary header. If a provider does 5 services, they will show up under all 5 categories from the checked values.

       

      Service Directory

      Service 1

      Joe*

      Jane

      Bob

      Sue

      Service 2

      Joe*

      Sue

      Service 3

      Joe*

      Bob

      Jane

      *Joe shows up in all categories.

      Thank you for your help.

        • 1. Re: Use list values to group records FMPro11?
          erolst

          It's simple to see why this cannot work – you need nine records to show the summarized list from your example, but you only got four: Joe, Bob, Jane and Sue …

           

          Instead of checkboxes, create a related table where each record is a Service offered by a Provider; then create your report from that table. (Following your setup, J, B, J & S would have a total of nine related records.)

           

          btw …

          leebrock wrote:

          I am sketching out a little data base for the real developers to use as an example of what we need.

           

          wouldn't it be more efficient if you'd sketch the sketches (like the example above) and let the quote real developers unquote take care of the implementation?

          • 2. Re: Use list values to group records FMPro11?
            leebrock

            Thanks, erolst for your reply.

             

            The example has nothing to do with the actual content or calculations of the project.  Just a visual example of how the grouping should look.   

             

            It would be more efficient, as you suggested, only if I were working with the developers directly.  This is something I have to demonstrate to my bosses first before they give it to the developers, when I will be out of the loop.    And to be honest, I like doing it.  And if you would be willing to helping me out, I would be happy to learn something new. 

             

            I hear what you're saying about the related database, that was my first thought. But really, the service name is the only information I need.  My boses are not computer guys--I don't want to risk them messing up the links, if there is a way around it,  since I won't be there to demostrate it.  Can it be done with values?

             

            Thanks

            • 3. Re: Use list values to group records FMPro11?
              erolst

              Just because you're using related records doesn't mean that the users have to know about it, and surely they should not be allowed to mess with them.

               

              The selection tool can still look like a value list – or something similar. See attached:

              • 4. Re: Use list values to group records FMPro11?
                leebrock

                thanks for the file, erolst, I'm on FMP11 which doesn't open your file unless you know of a way I can convert it. I wasn't planning to upgrade since I am not a developer.  I created my business DB app but after that I don't have a lot of need except for little projects like this.  Do you think I need the upgrade anyway?

                • 5. Re: Use list values to group records FMPro11?
                  erolst

                  FM11 – it's right in your thread title … Sorry.

                   

                  If you're happy with what you've got, then I guess there's no reason to upggrade; FM11 is reasonably powerful.

                   

                  If you want to take a look at the example file (and no, you cannot back-convert it), you could download the 30-day-trial of FM13 from the FMI website. (Of course there's the risk that once you've tried it, you want to upgrade …)

                  • 6. Re: Use list values to group records FMPro11?
                    leebrock

                    Clever.  Thanks

                    • 7. Re: Use list values to group records FMPro11?
                      leebrock

                      Yup, your file gives me exactly the results  I need.  It's a little over my head--I'll need to chew on it a bit.  (I said I wanted to learn.)  It give me an idea for something I wanted in my own business DB.  Thank you for your time, I really do appreciate it. 

                       

                      I just spoke with FM tech.  According to them, my FMP11 shouldn't even be running on my Max OS 10.9.1.  Ha!  Apparantly because I already had FMP11 installed  when I upgraded the os to Mavericks, it's running ok but, if I were to start making changes to my DB files there could be trouble.  So I guess I'll need to upgrade soon anyway. 

                       

                      Cheers!

                      • 8. Re: Use list values to group records FMPro11?
                        erolst

                        leebrock wrote:

                        Yup, your file gives me exactly the results  I need […]

                         

                        Glad you liked it. Then I guess you could mark the question as “Answered”.

                        • 9. Re: Use list values to group records FMPro11?
                          leebrock

                          I had left it open just in case someone else wanted to take a whack at making the value list situation work. But what the heck, you helped me out a lot even going with the relational approach.  Many thanks, erolst. 

                          • 10. Re: Use list values to group records FMPro11?
                            erolst

                            You could create a Value List from the Services table, display a utility field as a checkbox set with that value list and combine that with creating and deleting related records. The scripting is a tad more complicated, but it would give you a 'native looking' checkbox, if that's your heart's desire.

                            leebrock wrote:

                            in case someone else wanted to take a whack at making the value list situation work

                            You can use something like the Virtual List (don't ask) technique, or a real hack to make this work, but why bother. You have a relational database, and Services have a one-to-many-relationship to a provider, so create and use relationships.

                             

                            Also, I think in the long run you may have to say more about those Services (prices? availability?), and then their own table is the right place to store that data.

                            • 11. Re: Use list values to group records FMPro11?
                              leebrock

                              got it.  Thank you for working with me on this, erolst.  I really appreciate it. 

                               

                              Cheers