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:
WHERE field1 IN ( SELECT DISTINCT(field) FROM table2 )
" ; "" ; "" )
Use a nested ExecuteSQL statement instead:
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.
SELECT COUNT(field), use SELECT COUNT(*)
For some reason, it's faster to pull the entire table, rather than a column of fields.
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.
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.
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.
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:
$S1 = "Math";
$S2 = "reading";
$S3 = "Writing"];
Then in the data of that section i said:
$Query = "SELECT Avg(Value) from MasteryTracker WHERE Subject =?";
And it worked.
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.
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.
It is fast too, John is a very smart guy.
I would love to see that from John if he so desires.
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 - 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.
Good point Bruce. Us non-attendees wouldn't be able to participate or learn from the discussion.
Hopefully this thread can give DevCon tips to others as well