Jump to content
Sign in to follow this  
dansmith65

only show 5 highest values?

Recommended Posts

In the (FM12) file attached, can anyone think of a clever way to limit that chart to only the 5 highest results?

chartTest.zip

Share this post


Link to post
Share on other sites

I was hoping to come up with a way to do this with just relationships, calculations, and summary fields. But the Fast Summary method seems to work well for this, and run plenty fast. I attached my modified test file for anyone interested. I used this blog post to help implement Fast Summaries: http://www.filemakerhacks.com/?p=2098

chartTestv2.zip

Share this post


Link to post
Share on other sites

You could also use something like this and then parse out the X and Y values from it.

LeftValues (



ExecuteSQL ( "

SELECT Count (c_date) as thedate, Region

FROM eqs7day_M1

WHERE c_date >= ? AND c_date <= ?

GROUP BY Region

ORDER BY thedate DESC

"

; "" ; ""; global::dateStart; global::dateEnd )



; 5

)






or






LeftValues (



ExecuteSQL ( "

SELECT Count (c_date) as thedate, Region

FROM eqs7day_M1

WHERE c_date BETWEEN ? AND ?

GROUP BY Region

ORDER BY thedate DESC

"

; "" ; ""; global::dateStart; global::dateEnd )



; 5

)

  • Like 1

Share this post


Link to post
Share on other sites

Thanks, that's a good idea. I wonder if Fast Summaries or SQL is faster? If I am feeling ambitious some day, I might test it.

Share this post


Link to post
Share on other sites

Thanks, that's a good idea. I wonder if Fast Summaries or SQL is faster? If I am feeling ambitious some day, I might test it.

I just did that speed test - and the results were shocking...

With the database hosted on my local computer:

Fast Summaries: 0.05 sec

SQL: 0.33 sec

On my client's database hosted on the web, accessed remotely: (different database than chartTest, attached above)

Fast Summaries: 1.40 sec for first run, 0.14 all additional runs

SQL: ~200 sec for first run, 0.22 all additional runs

So, it seems like using SQL is causing all records to be downloaded, whereas Fast Summaries does not. Once the data is downloaded, running the test additional times is quick using either method - all it takes to make it slow again is to close the database, then log into it again.

Share this post


Link to post
Share on other sites

Interesting. I really wish FMI would have some kind of whitepaper on how much is actually processed on the server vs client using ExecuteSQL.

Curious though... do you have a SQL plugin? Also what if using ODBC? These would be good to benchmark against as well, would you be able to test those?

I do know that the aggregation happens on the client side which at DevCon I was persistent that FMI try and look to improve / change this model in the future as more and more people start using WAN especially GO solutions. Using ODBC though it should happen on the server side.

  • Like 1

Share this post


Link to post
Share on other sites

I tested with BaseElements plug-in when I did the tests last night - it was so close in processing time it wasn't worth mentioning it separately.

No, I won't be able to test with ODBC on the same database that I ran the previous WAN tests on.

Share this post


Link to post
Share on other sites

I was hoping to come up with a way to do this with just relationships, calculations, and summary fields. But the Fast Summary method seems to work well for this, and run plenty fast. I attached my modified test file for anyone interested. I used this blog post to help implement Fast Summaries: http://www.filemakerhacks.com/?p=2098

Thanks Dan thats a neat technique, esp speed wise.

I have a need along similar lines, I was going down the path of convoluted, complex looping scripts building up vars but this should help speed up the process (and development).

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.