10 Replies Latest reply on Jan 9, 2014 2:59 PM by erolst

    Need help with sorting data into 5 groups evenly on a report.

    mmrobinson

      Version – Database hosted on Fm12 server - using FileMaker Pro Advanced 2012

       

      I am self-taught on how to use the program and need some assistance setting up a script (or using an existing script) for the report I need to do.

       

      Scenario – perform a find to get a set of users and their incoming hire date – I need to sort them by the date and separate them in to 5 different groups evenly. With group 1 containing those with the most tenure and group 5 containing those that are the newest. If an uneven number fill in from group 1 – 5. For example: 104 records – groups 1-4 contain 21 names and group 5 contains 20.

       

      Report layout example:

       

      Group 1 Group 2 Group 3 Group 4 Group 5

      Fisher, S. Camp, D. Prather, M. Perkins, L. McCardle, P.

      Wills, J. Scott, K. Milton, D. Richards, T. Jackson, M.

      Robinson, D. Frost, J. Nimble, J.

       

      Not sure if I could use an “If” statement, a “case” statement, or something completely different than these two options which would produce the report I need. This report, previously performed in foxpro, has been stumping me for the last 6 months. Have tried to find a corresponding issue in the forums and on the web, but have yet to find someone addressing this type of issue. Any help would be greatly appreciated. Thank you in advance.

       

      Thanks,

      Marcie Robinson

        • 1. Re: Need help with sorting data into 5 groups evenly on a report.
          erolst

          See if this helps you. Make sure you understand the calculation used to determine the group number, so you can adapt it if necessary.

           

          .

          1 of 1 people found this helpful
          • 2. Re: Need help with sorting data into 5 groups evenly on a report.
            mmrobinson

            Is there a way I can see the calculation you used to get the group number?   I am locked out of the scripts. Is there any way to have the groups displayed across the page rather than down?  This way they can view it on one page.  Most groups will have at least 20 people.

             

            You have shown me it can be done....which is better than I have seen so far.   Just not sure how you did it.

             

             

            Thanks,

            Marcie

            • 3. Re: Need help with sorting data into 5 groups evenly on a report.
              erolst

              mmrobinson wrote:

              Is there a way I can see the calculation you used to get the group number?   I am locked out of the scripts.

               

              This is odd, because the auto-login uses the default Admin / no password Full Access account, so you should be able to view and use the complete file.

               

              Here's another try, using a self-join to collect the group lists, then displaying the groups in columns across; let me know if you have problems opening that one, too.

              1 of 1 people found this helpful
              • 4. Re: Need help with sorting data into 5 groups evenly on a report.
                mmrobinson

                Thanks Erolst - this second one is exactly how I need it to look.  Unfortuantely I am unable to view the calculation fields and the scripts.  So I am still not sure how you did this.

                 

                Also, in your examples if I was to add a new person that has the earliest date - will your setup insert him in the first group and move the others accordingly?

                 

                Thanks Again for your help.

                • 5. Re: Need help with sorting data into 5 groups evenly on a report.
                  erolst

                  mmrobinson wrote:

                  Thanks Erolst - this second one is exactly how I need it to look.  Unfortuantely I am unable to view the calculation fields and the scripts.  So I am still not sure how you did this.

                  I don't understand this. If it was a permissions problem, you wouldn't even be able to perform the script, since then the file could not be modified … maybe try to log in manually by holding the Alt (Mac) or Shift key (Windows) while opening the file, then enter account name: Admin / password: empty (as in: leave the field empty!). 

                   

                  Or see if the file from this link works for you: https://www.dropbox.com/s/661lwiida6au839/GroupArbitrarily_eos.fmp12.zip

                  mmrobinson wrote:

                  Also, in your examples if I was to add a new person that has the earliest date - will your setup insert him in the first group and move the others accordingly?

                   

                  The script works on the found set, i.e. make sure all the persons you want to see are included in it. From then on, it's sort by date, assign group numbers, sort by group, use preview mode to display in columns. So a new entry will go into the correct group if you re-run the script.

                  • 6. Re: Need help with sorting data into 5 groups evenly on a report.
                    mmrobinson

                    Thanks a million was able to see everything on the last one you sent.    Now I just have to figure out how to incorporate it into my database with my corresponding fields.  I also tested it by just adding a new person with an earlier date and it work perfectly. 

                     

                    Thanks Again,

                    Marcie Robinson

                    • 7. Re: Need help with sorting data into 5 groups evenly on a report.
                      mmrobinson

                      So I have everything set up in my database per your recommendation.  Everything is working properly except for the distribution into the groups. This is what I am getting- I have 105 records total.  Which means all groups should have the same amount in each column.  However, I am getting 22 users in groups 1-4 and only 17 in Group 5.  Thoughts?  Any suggestions?

                       

                      Thanks again for your help.

                       

                      Marcie

                      • 8. Re: Need help with sorting data into 5 groups evenly on a report.
                        erolst

                        mmrobinson wrote:

                        Thoughts?  Any suggestions?

                         

                        Change the 5th line of the group number calculation from

                         

                        incomp = n - comp ;

                         

                        to

                         

                        incomp = Min ( 0 ; n - comp ) ;

                         

                        Mod() trips me up somehow every time …

                        • 9. Re: Need help with sorting data into 5 groups evenly on a report.
                          mmrobinson

                          Thanks again for all of your help.  The scripting is a completely different language for me sometimes.

                           

                          One more question while I have you - is it possible to get the total record count displayed on the report?  Would I add the script to the grouping calculation or would it be somewhere else completely?

                           

                          Thanks Again,

                          Marcie

                          • 10. Re: Need help with sorting data into 5 groups evenly on a report.
                            erolst

                            On your report layout, add the following text as text object to the header part:

                             

                            All records: <<$$fc>>

                             

                            or use some other variable name. In the ShowInColumns script, after Show All Records add the step

                             

                            Set Variable [ $$fc ; Get ( FoundCount ) ]

                             

                            to set the value of the variable you use in the layout object. Normally you could simply use

                             

                            {{FoundCount}}

                             

                            as a layout text object (this works with all Get () parameters), but the way the scripts work, the found count is identical to the number of groups, not the number of displayed names; therefore you need to capture the count of persons before we find those with the lists.

                            mmrobinson wrote:

                            Would I add the script to the grouping calculation or would it be somewhere else completely?

                             

                            You can inspect the button and see what script is attached to it, and take it from there … I have put it into the ShowInColumns script because it doesn't actually have anything to to do with the group number calculation, but with navigating to a certain layout and preparing the display.