SQL instead of unstored calc to display items of value list?

[innodat]

Two tables

Journalists ------  RecordID = JournalistID ------ Affiliations   (very simple)

 

1 Value List

called "ListAffiliationsForJournalist", based on Journalists, Affiliations::Name

 

1 Unstored calculation field

on Journalists, ValueListItems ( Get (FileName) ; "ListAffiliationsForJournalist" )

 

All of this works - if a journalist record has two affiliations records, the unstored calculation field lists the names of both affiliations.

 

 

HOWEVER: I'm worried about performance. Is there a way to replace this unstored calculation with an Execute SQL function?

 

I just spend several hours reading up on SQL and FileMaker (I'm really new with this), and I'm not getting anywhere... I believe I just need a simple sample to get me started in the above context. Or maybe this doesn't make sense at all??

 

 

Thank you so much in advance!

 

 

[Wim Decorte]

SELECT name FROM Affiliations WHERE JournalistID = <the journalist id you are on>

 

Or you could do away with calcs altogether and do the affiliation joining as a scripted workflow: when a journalist adds a new affiliation, you add a new join record between journalist and affiliation.  The affiliations can then just be listed as related records.

[innodat]

I'm embarrassed to ask, but what about the exact syntax?

 

Sticking with the calculation for now, based on http://www.filemaker.com/help/12/fmp/html/func_ref3.33.6.html  I'm guessing it should look like this:

ExecuteSQL ("SELECT name FROM Affiliations WHERE JournalistID = <RecordID>"; " "; " ")  

 

But this returns a "?" in the calculation field.

Edited October 24, 2014 by artvault

[eos]

 I'm guessing it should look like this:

ExecuteSQL ("SELECT name FROM Affiliations WHERE JournalistID = RecordID"; " "; " ") 

 

 

It should look like

 

ExecuteSQL ( "

  SELECT name

  FROM Affiliations

  WHERE JournalistID = ?

  " ; " "; " " ; RecordID

)

 

Understand that in ExecuteSQL(), the part in quotes is SQL syntax, and the rest is FileMaker.

 

The SQL query could read from a field named RecordID, but in you example, it doesn't find such a field in the queried table; hence, the ?.

 

Instead, inject the query value as a constant (which is of course different per context record) by referencing a FileMaker field value using FM syntax.

 

I think this is much more difficult to explain than to simply experiment and try …   make sure to grab a copy of Beverly Voth's eSQL intro that's floating around.

[innodat]

AH! Found the culprit: the calculation cannot be unstored.

Not that it did any visible indexing... but making it unstored somehow broke the SQL

 

Thank you for the above explanation - now I have exactly the example I needed and I can get started toying with SQL :-)

 

Thank you!!

Edited October 24, 2014 by artvault

[eos]

AH! Found the culprit: the calculation cannot be unstored.

 

Thank you!!

 

You're welcome. In fact, though you could set the calc to stored if the field references allow it, it must be unstored to update properly.

[innodat]

Yes, just realized that. 

 

I will also consider your other tip, getting rid of the calculation altogether and scripting it.

 

Though above should be pretty speedy?

[eos]

Yes, just realized that. 

I will also consider your other tip, getting rid of the calculation altogether and scripting it.

Though above should be pretty speedy?

 

In fact it was Wim's tip – to which I was about to add that even with a join table, there will still be times when you don't need (or can't use) a portal or a record list, but just a textual result somewhere (maybe even out of the relationship context); then eSQL is pretty nifty.

 

As to speed: it's not a Silver Bullet; if you reference unstored fields, it will be as slow as you would expect from a calculation in the native FM calculation engine.

 

btw: if you apply SQL to a join table, it will be helpul to learn about SQL JOINS …

 

Anyway, have fun experimenting.

[beverly]

Lots of lovely ExecuteSQL tips here (along with the "reference":

<http://filemakerhacks.com/category/executesql/>

Beverly

[comment]

There are two things that puzzle me here:

 

1. Why do you need the value list? Why not simply use:

List ( Affiliations::Name )

or - even simpler - show the related affiliations in a portal?

 

 

2. How is using "an Execute SQL function"- it's not quite clear if you mean the ExecuteSQL() function or the Execute SQL [] script step, but my question relates to both equally - how is using any of them going to improve performance?

[innodat]

Hi comment,

 

I was under the impression that SQL can increase performance when used instead of native FM functions, in particular when data needs to aggregated across (found) sets. In my experience, native FM summary functions are super slow in large record sets. Count (), Sum () for certain can bring a database to its knees, not sure about List (). So I figured that would be a good place to get started with SQL. But maybe I'm all wrong?

 

I also read that using a value list is generally the fastest FM native way to aggregate?

 

(portal is not what I need in this case)

[comment]

I was under the impression that SQL can increase performance when used instead of native FM functions, in particular when data needs to aggregated across (found) sets.

 

I suspect you may be generalizing some specific cases to a point where they no longer apply.

 

I also read that using a value list is generally the fastest FM native way to aggregate?

A value list is a fast way to get an alphabetically sorted list with no duplicates. Do you have journalists with duplicate (by name) related affiliations?

 

(portal is not what I need in this case)

Perhaps you should explain what your (final) purpose is first.

[innodat]

 Perhaps you should explain what your (final) purpose is first.

 

Sure: I need a comma separated listing of the affiliations for each journalist to be displayed in list-view. Better explained with image (see attached) than words. in the attached picture you can see the following fields:

 

journalist number        journalist type        affiliations           rating

[comment]

IMHO, you won't see any significant difference between an unstored calculation using List() and another unstored calculation using ExecuteSQL(). I am not sure how to test such thing, but I suspect that the advantage, if any, would be on the List() side.

 

I don't think you need to worry about performance with the numbers shown in your screen shot (~2k parent records with say 10 children each?). But with larger numbers, you might want to speed things up by denormalizing. This would mean turning the unstored calculation field into a stored text field - and making sure it's updated every time a journalist's affiliation is added/deleted/modified. This is not easy to do reliably - so I wouldn't rush there unless it's really necessary. 

[innodat]

Thank you for the input on this. So this table will be fine then.

 

A different table however will likely accumulate approximately 15k records per year, with archiving possible after four to five years. So it should be able to hold about 75k records. I will think about scripting it, as you suggested.

 

The performance challenge might not so easily be solvable with another function though: conditional filters (selection in filter A affects choice in filter B ). I have read about many different techniques, now resorted to the new native List () summary function. But I'm pretty sure it won't too work well with 75k records over 3G... I'm very fond of the function because it allows you to combine multiple filters with a free-form search field. AND you can undo each item individually.

 

I "crippled" the client's file and attached it to this thread to show that particular functionality.

 

I tried server side scripting, but the problem is that the server is not aware of the found set - or anything else a particular user is up to. If I need to aggregate first and send to the server for execution, I don't gain anything.

Conditional_Filters.fmp12.zip