14 Replies Latest reply on Apr 6, 2015 4:15 PM by codeslave

    Reports, and find functionality, fixing broken work

    codeslave

      Yep......well

      We have a report designed and coded by a  consultant, it displays the payments to staff.


      The way it works:

      FM goes into find mode and it  finds on several fields. any one of which can be populated....


      1. Staff ID

      2. First name

      3. Family name


      points for anyone  who spots the immediate problem (staff ID's are unique.... first/family names are not......)


      if you enter only the staff Id the system works perfectly


      The bug :

      If It searches for  2 or 3, then points the displayed  to the first record it finds(well it looks  correct on the screen was the reasoning....),

      now obviously in the summery calcs they are looking at the FULL selection as is the  rest of the report functionality.


      In reality

      any  pointers on where I can add functionality to  force the summary/ calculations to use the Staff ID as a filter...

       

       



        • 1. Re: Reports, and find functionality, fixing broken work
          siplus

          After the find, sort the found records by 1) Staff ID, then by 2) family name and 3) by first name.

           

          Add a subsummary to the report, with the totals by Staff ID.

           

          This way the report will be broken by Staff ID; if there's more than one you will get each list of staff members in its own staff and the total of it.

           

          ---

           

          If you want to be sure that the data entered in Staff ID corresponds to just one Staff ID and not to more than one, after the find and the sort you go to the first record in the found set, put the staff ID into a local variable, go to the last record and compare its Staff ID with the variable content and if they're not equal you display an error message and restart the whole - that means enclosing the find and sort into a loop to be exited only if the compare gives a true result.

          • 2. Re: Reports, and find functionality, fixing broken work
            codeslave

            It was only supposed to return 1 result.

            which obviously cannot be possible if there is more than one person with the same first name or surname....

             

            It looks like the guy tried to rely on the firstname/last name to force the load of the Staff ID field and be included in the SAME find!!!

             

            The idea was to try and make the report 'interactive'.... via the find button

             

            also there is a linked trigger on the layout with extra processing (onRecordLoad),

            Which means you cannot move the record pointer to check the records or it will recurse.......

             

            It seems like a bug to be able to add a script to "onrecordload", be able to manipulate the record pointer and have the same script re-enter until the filemaker program crashes.....

             

            The whole thing should never have seen the light of day...

             

            Purely to make the point that anyone can write bad code.....

            I'm of the mind to add a 'flip/flop' flag into the 'onrecordload' force a variable to be loaded with the first staff id , then narrow the find based on that!!! (a flip/flop is needed since the re-find will trigger the onrecordload again and recurse into infinity)

             

            :-)

            • 3. Re: Reports, and find functionality, fixing broken work
              gdurniak

              Yes, the trigger should probably be removed

               

              If you search on 2 OR 3,  you will of course get too many results.  This should not shock your users.  You could provide a button to select the ID they were looking for

               

              Note:  you could also require that First & Last together be unique,  thru a calculation ( and add Middle Initial )

               

              greg

               

              > If It searches for  2 or 3, then points the displayed  to the first record it finds

               

              > also there is a linked trigger on the layout with extra processing (onRecordLoad),

              Which means you cannot move the record pointer to check the records or it will recurse

              • 4. Re: Reports, and find functionality, fixing broken work
                siplus

                The whole thing should never have seen the light of day...

                 

                Purely to make the point that anyone can write bad code.....

                 

                 

                I feel your pain.

                 

                There are many alternatives to deal with your problem. Like overdoing it (scripts doing an executeSQL on every keypress in a field via a trigger, looking for valuecount(select distinct StaffID  from ... where...) and letting you continue only if the result is 1) or having a value list based on the StaffID field, StaffID field displayed as a popup menu and not leaving the search if StaffID is left blank.

                 

                Or also: on every keypress going to a layout with only the relevant fields on it, doing a quick findand not continuing until Get(FoundCount) = 1.

                 

                It also depends on the data. You could have 1000 people assembled in teams of 3, or 200 people in teams of 5. What you want to deliver is good UX, so depending on the above you might have to choose different methods.

                 

                Also investigate on the "constrain found set" script step.

                 

                Anyway, you will have a lot of flexibility if your find goes to a special layout with 3 global fields and when they press "find" you do all the magic.

                • 5. Re: Reports, and find functionality, fixing broken work

                  The word report may misslead since folks begin thinking about a FileMaker Report. The replies are all based on that word but that doesn't seem to be what you are asking.

                   

                  What I think you mean is Layout/Form.

                   

                  Your search produces a result showing in a form layout. What you see is the first record found even if there are more than one. This is causing the confusion.

                   

                  What you need is to show one record in the form and multiple records in a list/report view. Or, show all results in the list view and let the user click on the one to view.

                   

                  Search

                  If get(foundcount) = 0

                    dialog nothing found

                  else if gfc = 1

                  go to form layout   <- your design sends everyone here which is causing the problem

                  else if gfc > 1

                  go to list layout    <- the list view shows all of the records found and the user can click on one to go to the form layout

                  end if

                   

                  Using this your users will know immediately if more than one records is found for matching names. They can see the lsit and click on the one they want to use. I started using this idea 20 years ago and my users were quite happy with it since they had a lot of similar last names to deal with.

                  • 6. Re: Reports, and find functionality, fixing broken work
                    siplus

                    jackrodgers wrote:

                     

                    The word report may misslead since folks begin thinking about a FileMaker Report. The replies are all based on that word but that doesn't seem to be what you are asking.

                     

                     

                    of course, the word "report" is a trigger for most of us

                     

                     

                    codeslave, take a look at  this link:

                     

                    http://www.modularfilemaker.org/module/auto-quick-find/

                    • 7. Re: Reports, and find functionality, fixing broken work

                      See my previous post for the simplest answer.

                       

                      The most memorable incident I had with names was when I was dragged up to the front counter while consulting and shown that MY find report would not find this customer. I was introduced to the man and he was a very friendly person of Asian descent. We chatted for a moment and I asked him what his last name was. I entered that spelling in the last name field. Nothing. So I tried the first name field and there it was. The names were reversed but since the staff member was unfamiliar with the names from that country, he didn't realize that.

                       

                      So, I decided to rewrite this search and in fact predated FileMaker's limited quick search field.

                       

                      I did a fuzzy search for first and last name fields. In other words "John" would find all first AND last names that contained the word snippet "John". I think had a deeper search button for "J*hn". And so on.

                       

                      Since I was dealing with human typists and today's databases do not use tripple redundency with three people entering the same data searching for typos, the best I could do was to show a list of possiblities.

                       

                      Hence my suggestsion which is born of trial and error and a desire for simplicity and to empower the user. Users are empowered by being allowed to select from a list of possibilities...

                       

                      Of course phone numbers and social security numbers are a bit more exact...

                      • 8. Re: Reports, and find functionality, fixing broken work

                        Pretty cool...

                         

                        But having to scroll threw 50000 records to find...  well...

                         

                        Here's one I've put together for 13 and I think sets a world record for being the simplest typeahead construct.

                         

                        It will run out of steam with large databases but I am intending it for use in my smaller tables.

                         

                        There is only one link and one custom function of the small kind.

                         

                        The effort was to create triggers for the field to empty it and keep the typing going.

                         

                        Since it is a popup I can place it on any layout using this table.

                         

                        Nothing new except for the simplicity of the method (maybe, I haven't seen others...)

                         

                        The value of the typeahead vs the aforementioned method (which seems to work well with large tables) is that it only shows what it finds. Maybe I'll add an option for instring or inner words? Wish I hadn't thought about that.

                         

                        quickfind.png

                        • 9. Re: Reports, and find functionality, fixing broken work

                          Trigger words "Report" "SQL" etc.

                           

                          Trigger Script:

                          Stop Thinking

                          React to trigger word

                          (Possibly confuse newbies?)

                           

                          I try to look beyond the question and see what the problem is, which is what I described. Don't know if I am correct about that but I think so.

                           

                          It's a simple IF/Else solution and forgoes hours of SQL... 

                          • 10. Re: Reports, and find functionality, fixing broken work
                            codeslave

                            Hi,

                            Nope to everyone that thinks this is a 'layout' , it is actually a report.

                             

                            first there is a layout  to find a number of similar records (based on name), this then kicks over to a screen 'report'

                             

                            it is a report becasue it has:

                            Header, body, footer sections

                             

                            This is where the programmer got creative, to save time so the user could go in and prepare a report for each relevant staff, the programmer added the search fictionality in the Header!!!

                             

                            This was to allow the user to quickly prepare printed reports on a given staff by quickly searching for them

                            but it went bad because of the search functionality implementation plus the programmer not thinking the problem through clearly enough.

                            • 11. Re: Reports, and find functionality, fixing broken work
                              siplus

                              I feel you pain, again. More than often I have to deal with fixing all kinds of shite,


                              • originated back in Filemaker 4 which propagated through time

                              • created by inexperienced coders

                              • put together by people unable to say "no" to management

                              and the list can reach dozens of items easily.

                               

                              We all have to. But one thing is IMHO important.

                               

                              Do not throw mud on people.

                              Just say "I thought of a better implementation, given the power of the actual Filemaker version".

                               

                              If we as programmers, coders, UX creators, you name it, want to be taken seriously and respected and correctly paid for our work, we have to be united, defend our colleagues even when their choice was not the best one and defend the profession as such. Just like MD's do.

                              • 12. Re: Reports, and find functionality, fixing broken work
                                gdurniak

                                At the DevCon roll out of FileMaker 7,  FileMaker's Andy LeCates warned that we now have "enough rope to hang ourselves"

                                 

                                That may have happened here

                                 

                                greg

                                 

                                > but it went bad because of the search functionality implementation plus the programmer not thinking the problem through clearly enough.

                                • 13. Re: Reports, and find functionality, fixing broken work

                                  Often time and money constraints affect the results of any programmer. Billion dollar companies continue, during my 30+ years of buying software, to deliver products with an amazing number of bugs in them. The forthcoming update lists hundreds of them.

                                   

                                  No matter how hard you try, you can't defeat the efforts of the data entry people nor produce the best possible solution because the client doesn't want to pay for it. And sometimes you just screw up.

                                   

                                  One interesting instance was when I was called into the clients office and was considered the bad guy. Look, heres the name. But if we search for it we can't find it.  Let's say it was Margaret. Plain as day on screen.

                                   

                                  I tried. No luck with many variations.

                                   

                                  So, I ran the field through a little script that gave me a character by character analysis of the name. Seems the entry began with a few hidden characters which turned out to be Control+M rather than Shif+M. The Control+M (might have been a different character and name) didn't show in the field but it affected the search. I explained and put the cursor before the M and back deleted and then all was well.

                                   

                                  Just guessing but I bet the report could be improved by adding an additional report section for last name, first name. Then when it is sorted, the various duplicates show up as unique.

                                   

                                  Jones, Jim

                                     ID     Division    Notes

                                  Jones, Sally

                                     ID    Division   Notes

                                  Jones, William

                                     ID    Division   Notes

                                   

                                  (The names chosen indicate my cultural history, don't they.)

                                   

                                  It would help to see the actual screen shot rather than a verbal description.

                                  • 14. Re: Reports, and find functionality, fixing broken work
                                    codeslave

                                    I would have posted the code & the screen snapshots.

                                     

                                    Unfortunately it would expose highly confidential corporate information  and there is very little I could leave intact, it would end up looking like one of those publicly released NSA to the CIA memos.