Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About madmac33

  • Rank

Profile Information

  • Gender
    Not Telling
  1. I'm drawing on a database of 100k+ records. First Query - retrieves up to 1,000 results (each is a SUM of up to 1-1,000 records - GROUPS of records) SELECT SUM(t) as sumT FROM Briefs WHERE t>4 AND Group='ABC' AND Status ='1' GROUP BY myID ORDER BY sumT ASC Grouped Data = (1,2,3) (10,2,3,5) (23,2) (10,90) >> SUMS = 6, 20, 25, 100 (could be up to a 1,000 results) Next I calculate quartile info - Q1, Q3, IQR, Upper/Lower limits ($Upper might be 45) Second Query (orig post) - similar to First Query but limiting the SUMS by the HAVING clause (45) using U
  2. I currently have a custom function doing this, but it seems a fiddly workaround and inefficient for this purpose as eventually I'll be iteratively calling this SQL (or write a more complex call if I can work it out).
  3. Unfortunately not. I need the average of the SUMs. e.g. 1. the AVG of the SUMs (100,120,123,123,290) = 756 / 5 = 151.2 2. the AVG of the diffDateTime could be ((10,20,30,40), (10,20,20,30,40), (10,20,30,40,23), (10,20,30,63), (10,20,30,40,50,50,90), = 756 / 25 = 30.24
  4. Hi, i am a novice when it comes to anything more than basic SQL and am stuck trying to get an average (using SQL) of grouped query result. My first query is: SELECT SUM(diffDateTime) as theDiffSum FROM Briefs WHERE diffDateTime>4 AND Group1 = 'ABC' AND Status = '1' GROUP BY ows_ID HAVING theDiffSum>99 ORDER BY theDiffSum ASC this gives me a result like: 100 120 123 123 290 Ideally, i would like to form a query on this result set to give an average - a single number, using SQL (not FMP functions). I've looked around but have been unabl
  5. Thanks so much - you guys are fantastic!! Problem is now solved. However, i feel embarrassed that I overlooked the "Do Not store" attribute - I have been using Filemaker since version 1 (before Filemaker Plus even!). I am new to using CFs, they work very well. thanks again Wayne
  6. Hi, i'm not sure if this is the normal practice for Custom Functions. I have made a change to my CF and expected the change to be reflected in the calculation fields that use the CF. However, all previously defined calculation fields remain stuck to the "older" version of the CF, although the CF may have been radically changed. To force the newly updated CF to be used, I have to edit the calculation field (in define fields) and make a change to force a recalculation. This seems to be wrong behaviour, it is as if the calculation field copies the CF available at time of field definitio
  7. I tried a*t as test, as suggested Fitch, but no luck. Maybe calculations on aggregates just not work with filemaker SQL at this stage, as eluded to by Electric_Soul. Perhaps I'll have to load up MAMP (does it still exist??) and play with mySQL, etc.
  8. Hi, I have very limited SQL experience and am trying to do some calculations on a SELECT statement in a local FMP database; When I do: SELECT COUNT(tab) as t, SUM("amounts") as a FROM SummaryData WHERE tab >15 GROUP BY tab Result is: 16, -152 17, 113 18, 255 When I add in a calculation: SELECT COUNT(tab) as t, SUM("amounts") as a, a * t FROM SummaryData WHERE tab >15 GROUP BY tab Result is (invalid): ? I have tried various combinations that seem to work on standard SQL systems, but Filemaker errors. Does Filemaker SQL not support calculations? Any ideas
  9. I didn't plan on that. Makes a whole lot of sense. I will update the "parent" table (1 record) to use standard fields and test it out. I am sure that will solve it. Many thanks !
  10. Hi, this might be something i am not doing right, however.... When i place a new picture (png) into a global container field in my mac FMP13 solution, the field is updated throughout the solution. But, when i then copy the FMP13 file to iPhone to run with FMGO 13, the container field retains the old picture (like it is cached somehow). I have tried flushing cache steps and refreshing object, but this not seem to fix it. The only way to force an update (for the mobile to display properly) is to do a new compressed version or create a new kiosk-version. This is seriously doing my he
  • Create New...

Important Information

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