13 Replies Latest reply on Aug 20, 2013 6:50 PM by jbrown

    the ONE thing from DevCon

    jbrown

      Sitting here at 4:30 in the morning working on some charting, my brain remembered ONE thing from DevCon2013 that transforms the way I do things.

      What is the ONE (or TWO or THREE) thing(s) that you picked up from DevCon 2013 if you went? What one little trick, technique did you learn? It might be something that thousands of other developers already know, but it suddenly resonated with you, then DevCon was worth it!

      -----------------

      For me, it was a throwaway comment, I think, in Greg Lane's Advanced Reporting techniques class. He showed a graph that had been created using variables. The Variables were declared in one part of the charting dialog box and used throughout the rest of the dialog box. If I got it right (and I'm experimenting to be sure), once I declare a variable within the chart, it is 'active' throughout that entire chart. In one of my Y-axis data series Series Names, I declared variables that were all the subjects to be shown, one data series each. In the rest of the Series Name fiels, I simply typed in the Variable, and presto! the titles read the value of the variable, not the the $S4 as text!! Whoo hoo!. AND I used that same variable in that series' Data calculation.

       

      In the session, a participant asked about that; I wasn't keen enough to understand what I saw in the demo, but someone else caught it and I'm glad they did!!

        • 1. Re: the ONE thing from DevCon
          mikebeargie

          It was in the optimizing calculations technet session, but this executeSQL optimization:

           

          Instead of using nested SELECT statements in something like an IN or LIKE operator:

          ExecuteSQL("

          SELECT fields

          FROM table

          WHERE field1 IN ( SELECT DISTINCT(field) FROM table2 )

          " ; "" ; "" )

           

          Use a nested ExecuteSQL statement instead:

          ExecuteSQL("

          SELECT fields

          FROM table

          WHERE field1 IN (" ExecuteSQL("SELECT DISTINCT(field) FROM table2";"";"") ")

          " ; "" ; "" )

           

          From my understanding, the top calculation will run the inner SELECT statement for every iteration of the outer SELECT before returning results, whereas the bottom returns the inner ExecuteSQL statement as a list once to be used by all the outer ExecuteSQL statements.

           

          This boosts performance considerably.

           

          Also, a simpler one is optimizing ExecuteSQL's COUNT() function.

          Instead of

          SELECT COUNT(field), use SELECT COUNT(*)

          For some reason, it's faster to pull the entire table, rather than a column of fields.

          • 2. Re: the ONE thing from DevCon
            dvaklyes

            While sitting in Chad Novatny's session Reporting Outside the Box, I finally had my Eureka! moment about virtual lists. (While the lists are virtual, the table and fields are very real.) Not exactly the topic of his session, but now I understand how virtual lists work.

             

            In Jonathan Mickelson's session FileMaker Unleashed, Thriving in the Outside World, it was his tip on using Word or Excel (or PowerPoint, I suppose) files saved as XML as templates. Make a document with placeholder text (he used [[fieldname]] ) where you want to substitute in data, and save the document as XML. Get the documet into a field or variable, do a substitute for the placeholder text, and save the file back out as a text file with an XML extension. Open it back up in Word or Excel, and you have a document with your data. Can even do photos this way. And all without plugins.

            • 3. Re: the ONE thing from DevCon
              BruceRobertson

              I'm glad to see this thread has started and I hope it keeps going. One thing I observe is how hard it is to follow up on any comments here.

               

              That is - if I attended ( I did ) and I have copies of the files ( I do ) then how do I find the slides or demo files given the title or comment?

               

              Though we have Devcon2GO13 it doesn't link to the _sesson folders from the Devcon CD. I'll need to build a content management DB. Oh wait - don't we have an example file? Hm.

              • 4. Re: the ONE thing from DevCon
                BruceRobertson

                Regarding the graph with variables. That was in DEV029, Advanced Charting and Reporting?

                 

                Was the example you cite in one of the example files? I took a brief look, didn't find  it.

                 

                I heard some other people commenting on this method also.

                • 5. Re: the ONE thing from DevCon
                  jbrown

                  Yes it was. - Actually I'm not sure.  I just remembered them talking about it.

                  If I see the files I'll send them to you.

                   

                  I set it up this way:

                   

                  In my Series 1 box I delcared all the variables to use in this chart:

                   

                  Let (

                  [

                  $S1 = "Math";

                  $S2 = "reading";

                  $S3 = "Writing"];

                   

                  $S1)

                   

                  Then in the data of that section i said:

                   

                  Let (

                  $Query = "SELECT Avg(Value) from MasteryTracker WHERE Subject =?";

                   

                  ExecuteSQL($Query;"";"";$S1)

                   

                   

                   

                  And it worked. 

                  • 6. Re: the ONE thing from DevCon
                    dvaklyes

                    That is a gripe which is going on my evaluation. The conference book lists each talk by time, by code, and by name. The foldout out pages have the name and time, but not the code. DevCon2Go doesn't list the code. But the files (which are copied to my drive rather than running it through the CD) are only listed by code.

                    • 7. Re: the ONE thing from DevCon
                      BruceRobertson

                      John Renfrew showed me how he builds beautiful complex multipage PDF documents in memory with iText and ScriptMaster, then directly sets a container field to the result. Virtual Lists are part of the method.

                       

                      No export/append/import etc. This includes using fonts that are also stored in container fields - so the fonts do not need to be installed on the user machine.

                      • 8. Re: the ONE thing from DevCon
                        TimAnderson

                        It is fast too, John is a very smart guy.

                        • 9. Re: the ONE thing from DevCon
                          jormond

                          I would love to see that from John if he so desires.

                          • 10. Re: the ONE thing from DevCon
                            DelfinaDaves

                            Hi Bruce:

                             

                            How about making comments in the Speaker Updates section? For example, Chad's session updates are posted here: https://fmdev.filemaker.com/docs/DOC-3456, the forum is open to comments on the session. Keeps it all in one place.

                             

                            (Granted, not all speakers had updates, but most did).

                             

                            Delfina

                            • 11. Re: the ONE thing from DevCon
                              BruceRobertson

                              Delfina - thanks for noticing and thanks for your comment!

                               

                              That's a good start. Next, we'd want to see that all topics are listed. Right now by my quick count, about 29 out of 55 topics have updates.

                               

                              Then comes another issue - if we are trying help spread FileMaker knowledge and skills broadly and effectively, should lessons-learned only be available to attendees?

                               

                              We also need to think about how it is labeled and promoted. Right now, people COULD add comments there but I think that currently, most people think about it as strictly about where to find updates. Whereas we want them to think of updates; lessons learned; concepts; examples; questions.

                              • 12. Re: the ONE thing from DevCon
                                jormond

                                Good point Bruce. Us non-attendees wouldn't be able to participate or learn from the discussion.

                                • 13. Re: the ONE thing from DevCon
                                  jbrown

                                  Hopefully this thread can give DevCon tips to others as well