Jump to content

Charting new record count by month

This topic is 2245 days old. Please don't post here. Open a new topic instead.

Recommended Posts

K, here is another one for the chart guru's.


Using FMPro13


This seems like it should be really easy but i have tried several similar tutorials i found and tried reverse engineering several sales total charts by month and just cant get it to work.


On my [CLIENTUPDATE] table i have these two fields in question:  (Update Date) & (CLIENTUPDATE_ID) 

(CLIENTUPDATE_ID) is the primary key in this table.


I am trying to create a line graph that counts the number of primary keys "updates" and charts them by month over the last 12 months.



Thanks for your help!






Link to comment
Share on other sites

You need


• a calculated field that serves as a month indicator, say, cUpdateMonthAndYear, defined as Month ( YourTable::updateDate ) & "|" & Year ( YourTable::updateDate ), with result type text


• a summary field sCountOf for clientUpdateID


• a list layout with a sub-summary-part that uses cUpdateMonthAndYear as break field


• a chart object that uses sCountOfClientUpdateID as chart data, and "Found Set" as data source


• a script that finds all records for the last 12 months and sorts them by cUpdateMonthAndYear


Note that you don't have to display the calculated field, either in the sub-summary parts or in the chart.


For the x axis title of the chart, you can use e.g. MonthName ( YourTable::updateDate ) & " " & Year ( YourTable::updateDate ); in the layout, give updateDate field a custom date formatting.

Link to comment
Share on other sites

Can you elaborate on the:

• a script that finds all records for the last 12 months and sorts them by cUpdateMonthAndYear


You want to see a statistics for the past 12 months, so you need to find the records in that period; you also need to sort them by cUpdateMonthAndYear because that's the break field for your summary, and a sort is needed for the summary field to hold correct (i.e. grouped by Month&Year) results.
A script along these lines should work for you (when using your actual object names …) :
Enter Find Mode
Go to Layout [ Updates ( Updates) ]
Set Field [ Updates::updateDate ; ">" & Let ( cd = Get ( CurrentDate ) ; Date ( Month ( cd ) ; Day ( cd ) ; Year ( cd ) - 1 ) ) ]
Set Error Capture [ On ]
Perform Find
If [ not Get ( FoundCount ) ]
  # error handling steps, e.g. Show Custom Dialog, Go to Layout [ original ], Exit Script etc.
End If
Sort [ Restore ]
# sort by Updates::cUpdateMonthAndYear

Study the date functions and how you can over- and underfeed them (and let FileMaker figure our the actual date) to easily create the date range you want to search for; e.g. if you need to cover entire months, you could use a calculation like …


Let ( [
  cd = Get ( CurrentDate ) ;
  m = Month ( cd ) ;
  y = Year ( cd ) 
  ] ;
  Date ( m - 11 ; 1 ; y ) & ".." & Date ( m + 1 ; 0 ; y )
… which today returns (in US notation) 6/1/2013..5/31/2014 – but tomorrow would result in 7/1/2013..6/30/2014
Link to comment
Share on other sites

you also need to sort them by cUpdateMonthAndYear because that's the break field for your summary


That is correct - and that is also why you should not use:


Month ( YourTable::updateDate ) & "|" & Year ( YourTable::updateDate )


as the formula for the cUpdateMonthAndYear field. It will not sort correctly across the year boundary, and (being Text) it will also not sort correctly one-digit months and two-digit months among themselves. For example, the 12 months starting from June 2013 will be sorted as:















For this reason as well as others, it is preferable to calculate cMonth as =

UpdateDate - Day ( UpdateDate ) + 1

and set the result type of the calculation to Date.

  • Like 1
Link to comment
Share on other sites

  • 1 year later...

Just came across this post (in June of 2015) and now I've got a solution to getting a chart to display a trend over years and months, sorted chronologically. While I had all of the components in place (calc field, sort, chart setup correctly), the calc field was defined incorrectly. I never would have figured the calc formula that @comment suggested. It worked. 

Thank you for the post and suggestions.

Link to comment
Share on other sites

This topic is 2245 days old. Please don't post here. Open a new topic instead.

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

  • Similar Content

    • By HOnza
      Everyone is excited about Lion. Even though many new features on Mac OS X 10.7 are giving us improvements we kept asking for since 10.1, Apple has again succeeded in giving all the new features the strong essence of innovation that makes us unable to hesitate to install and try Lion out as soon as we are able to.
      But we are also afraid. What if some important apps stop working?
      Read more in my article. Alredy updated with links to other resources and opinions...
    • By Teilo
      So, the time has come. Runtime support is gone in 19.
      This puts me in a quandary. At my company, I have a handful of small apps that do nothing but import CSVs and print labels with barcodes on Zebra label printers, or packing slips with pictures for each item. These are one-off special purpose databases. There is no active data management. The scripts clear out the DB and import a fresh CSV on each run.
      I have tried making these applications work with Server, to no avail. I doubt anything has changed in 19.
      I can understand Claris wanting to move to an online subscription-based model, but the problem is that the server version is not at feature parity with the desktop client for certain essential things I need, like printing to a label printer, or exporting PDFs with images that come from a folder full of PNG files that change on each run.
      I have some time, obviously, but version 18 will not run forever, especially if Apple's shenanigans continue. Is Claris listening, or should I just assume that this is a lost cause?
    • By TaiChi56
      I have always developed with FileMaker for windows. I have bought myself a MacBook Air and love it. So now I am using FileMaker 16 Pro advance for MAC. I know that MAC does not label their directories like windows does. So I am having problems figuring out to get the right coding to access my photos. I have a folder called "Photos" that reside on the C: drive in windows. Then I reference that folder so pictures will populate automatically when I put in a record. Here is what I have on the windows fileMaker:
      Let ( [ // Transform the contents of a container field named Images to text ImageRef = GetAsText ( ImagePath); Photos = Position ( ImageRef; "/"; 1; PatternCount ( ImageRef; "/")) ]; Middle ( ImageRef; Photos + 1; Length ( ImageRef ) - Photos ) ) images:
      "imagewin:/C:/Photos/" & Film::title & ".jpg" Then I go into Import folder and point to the folder all the photos are in. The problem I have now is how to point to that folder in "imagewin", is their an alternative called imagemac, then I could put in Macintosh HD/Users/patricks/Documents.  The folder is in the Document folder, called Photos. Thank you for any help.
    • By Jonah74
      Am I righting in thinking I can use a case statement to evaluate two fields and return an answer into a third field ie: Field A has the number 27 in it. Field B has the number 32 in it.  Could the case statement look at the difference and put the difference in Field 3.The difference being 5? 
      I’m also trying to get this to do the math on data entry, so the figure in Field A is 27 but when the user enters the new figure in Field B (i.e 32), it looks at that difference between A and B, copies the difference of 5 to Field 3 and then also copies the figure 32 from Field B into Field A thereby updating Field A to the new value.
      Help, suggestions or thoughts will be greatly appreciated.
    • By DChord568
      I am not a FileMaker power user by any stretch of the imagination, nor does my question relate to any particularly sophisticated use of the program, so I hope others here will forgive and bear with me.
      I formed a band my junior year of college with guys who have remained my best friends throughout all of the subsequent 46 years. Though we’re scattered in different parts of the country now, we reunite at least twice a year to make music again, and for the past 23 years we have recorded songs together at these reunions.
      I created a FileMaker database to chronicle these recording sessions, and all of the songs done at each of them. The record for each song contains the fields you might expect: Title, Songwriter; Original Artist (for songs we didn’t write ourselves); Year; Session; Lyrics; and a repeating field for personnel — i.e., who sang and played what on each song. Each band member also has a Comment field so he can share his feelings about the song or memories of the recording session for it.
      We store the master copy of the database in Dropbox, and all can access it at any time — though I’ve put a system in place to ensure that no more than one person at a time can modify or add data to the file, to avoid the dreaded “conflicted copy” syndrome.
      Each record in the database also has a Container field for the recording it pertains to. To avoid the unthinkable bloat that would result in storing 23 years worth of .wav files in the database, I naturally used FileMaker’s “Store only a reference to this file” feature for this field.
      The music files themselves reside on a 128GB thumb drive. The plan was to strike exact copies of this Master thumb drive for each band member that they would physically insert into their own computers, so the the song associated with a given record would play when they access the database and double-click the Container field.
      Perspicacious readers may now guess where I’m headed. I am the only one of the five of us who is on a Mac system. My Master thumb drive, as well as a copy I struck from it, works perfectly for its intended purpose. I have taken the copied drive from home (where it was created) and used it with no problems whatsoever on my work Mac. No matter which Mac I use, as long as I access the database from Dropbox and have the thumb drive inserted in that Mac, all is well — since the Mac automatically “sees” and can instantly access any external device plugged into it.
      My bandmates, however, are all on Windows systems — and as my closest friend feared, when I sent that copy of the thumb drive to him. it did not work in the same way, due to Windows’ convention of assigning a letter to any external device plugged into a Windows-based machine. It appears a Windows system needs a strictly defined file path to locate the music files on the thumb drive. And a different letter is likely to be assigned to this drive in each person’s machine, based on how many other peripherals he has plugged into it at any given time.
      My obvious question: is there any way around this issue? Is there some way to tell a Windows system “Look for the music files referenced in this database on the ‘F:’ drive” (or whatever drive is appropriate)? Or is each individual member doomed to manually relinking each one of the 280 or so music files that I so carefully linked on the master thumb drive?
      Only my closest friend I referenced has a degree of computer savvy, so I was hoping to make this entire process as easy as possible for all concerned. Thank you very much in advance for any help that can be given.
      P.S. The database was created in FileMaker Pro 11.
  • Create New...

Important Information

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