1 2 Previous Next 20 Replies Latest reply on May 19, 2014 3:56 PM by philmodjunk

    Charting by Related Data

    pnomads

      Title

      Charting by Related Data

      Post

           I have a charting question that I’m hoping won’t be too difficult to accomplish. I have a database of donors that, for this chart, will use the related tables: Partners and Donations. These are related through the __pkPartnerID and _fkPartnerID fields.
            
           I have a form view layout based on the Partners table that is essentially a quickview for each donor. It has a Tab Panel that organizes all relevant data for each partner. One of those tabs is naturally “Donations”. While I could just make a portal displaying all donations, I’d like to do a column chart displaying “donations (y-axis) by Month (x-axis)”. 
            
           Here are where things may get tricky: 
            
           1) Each donor may have more than one donation in a given month, so if there were a month where a partner had two donations, I would want the sum of those listed for that month.
            
           2) I would like only the last 12 months of data displayed in the chart (dynamically updating)
            
           3) If a given month has no donations, I would like the graph to display that “0” or “null” value. (When I was testing charting this, if a month had no values it would not be displayed at all)
            
           Where I’m having a hard time with constructing a solution for this is that its charting based off of related records. I’ve searched on how to accomplish each one of the parameters listed above and found some information, but I haven’t seen them implemented in a solution all together. 
            
           Maybe I’m way off, but this seems like a chart with these parameters would be useful and very desired for many different applications (Customer sales by month, etc…). Following that line of thinking, my guess would be that it’s a fairly common chart and wouldn’t be that hard to create. Is a chart with these parameters possible, and if so, what am I missing to create it?

        • 1. Re: Charting by Related Data
          philmodjunk

               What version of FileMaker are you using? If you are using FileMaker 12 or newer, it might be possible to use ExecuteSQL to produce the needed data as delimited data--though I'm not sure about how best to include 0 values when there are no donation records.

               One method that does no use ExecuteSQL would be to add another table and set it up like this:

               Partners-----<PartnerMonths------<Donations|ByMonth

               Partners::__pkPartnerID = PartnerMonths::

               PartnerMonths::_fkPartnerID = Donations|ByMonth::_fkPartnerID AND
               PartnerMonths::Month = Donations|ByMonth::cMonth

               cMonth can be defined as: DonationDate - Day ( DonationDate ) + 1 to compute the date for the first day of that month.

               A calculation field defined in PartnerMonths can compute the total donations for a given partner for a given month:

               Sum ( Donations|ByMonth::Donation ) + 0 // clear the "do not evaluate if all referenced fields are empty" check box

               And this can provide the values needed for your chart of related data.

               An OnRecordLoad trigger can perform a script that uses Last ( PartnerMonths::Month ) to determine if any new records need to be added and any old ones need to be deleted in order to limit the data to the most recent 12 months.

          • 2. Re: Charting by Related Data
            pnomads

                 Sorry for the delayed response...ok so I want to understand this correctly before I dive in. I currently have a "Partners" table and a "Donations" table. So to implement the non-ExecuteSQL method, I would 

                 1) Create a brand new table called "PartnerMonths" and relate it to "Partners" via Partners::_pkPartnerID and PartnerMonths::_fkPartnerID

                 2) Create a "Month" field in "PartnerMonths"

                 2) Create a TO of "Donations" called "Donations|ByMonth" and relate it to "PartnerMonths" via the PartnerMonths::_fkPartnerID and Donations|ByMonth::_fkPartnerID

                 3) Create a field called cMonth in the "Donations|ByMonth" table. Relate Donations|ByMonth::cMonth to PartnerMonths::Month.

            4) Create a field in "Donations|ByMonth" called "DonationsSum" to compute the total donations for a given partner for a given month

            5) Create chart with PartnerMonths::Month as the x-axis, and Donations|ByMonth::DonationsSum as the y-axis

            6) Create OnRecordLoad script trigger (I'll have more questions on that once we get there)

            Thanks for your help and your patience with me! This is the first FileMaker database I've created and it's been a lot of fun to learn and build, but it can also be overwhelming at times for a newbie like myself.

                  
            • 3. Re: Charting by Related Data
              philmodjunk

                   1) yes, don't know how I left out the _fkPartnerID field.

                   2) This is a relationship that matches by two pairs of fields, one is partnerID and the other the Month the donation was made:

                   PartnerMonths::_fkPartnerID = Donations|ByMonth::_fkPartnerID AND
                   PartnerMonths::Month = Donations|ByMonth::cMonth

                   4) This field is defined in PartnerMonths

                   6) This is needed to automatically generate the needed records in PartnerMonths for each record in Partners

              • 4. Re: Charting by Related Data
                pnomads

                     Ok, finally got a chance to start working on this again. So I've created all of the fields, relationships, and the chart as instructed. Before I proceed to the script trigger, I have a couple of questions:

                     1) In the relationship graph of my database after creating the fields and relating them, I have a many-to-many relationship between both sets of related fields, which doesn't seem to be correct. From your initial post, shouldn't it be a one-to-many from each field in PartnerMonths----< to its corresponding related field in Donations|ByMonth? The attached file shows what it looks like in my Relationship Graph.

                     2) PartnerMonths::Month and Donations|ByMonth::cMonth should both display dates, correct? (PartnerMonths::Month as a Date field and Donations|ByMonth::cMonth as a calculation field resulting in a date)

                     Now as for the script trigger, I've created quite a few simpler scripts for this database, but I'm not sure exactly how to accomplish one like this. I assume that I probably need to create a calculation field in PartnerMonths for the "Last (PartnerMonths::Month)" equation to base a few IF script steps off of, but what script steps would I use to add and delete the records as needed? Thanks again! 
                • 5. Re: Charting by Related Data
                  philmodjunk

                       1) You do not have a many to many relationship. You just have a relationship where there is not enough field options specified for FileMaker to be able to identify which side of the relationship is "one" and which side is "many" so you get "crowsfeet" on both ends of the relationship line. This is normal and expected.

                       2) Yes, and these dates should be set to be the date for the first day of that month. That's the purpose of using the cMonth calculation, it takes any date and returns the date for the first day of the same month and year. Because they are as dates, they still sort correctly when you use these fields to specify a sort order.

                       3)

                       Set Variable [ $ThisMonth ; Value: Let ( T = Get ( CurrentDate ) ; T - Day ( T ) + 1 ) ]
                       If [ $ThisMonth > PartnerMonths::Month // Then  more PartnerMonth records are needed ]
                          Set Variable [$Month ; value: If ( Not IsEmpty ( PartnerMonths::Month ) ; Last ( PartnerMonths::Month ) ; GetAsDate ( "1/1/2014") )// see note below ]
                          Set Variable [$PartnerID ; value: Partners::__pkPartnerID ]
                          Freeze Window
                          Go to layout ["PartnerMonths" (PartnerMonths) ]
                          Loop
                             Set Variable [$Month ; Value: Date ( Month ( $Month ) + 1 ; 1 ; Year ( $Month ) ) // this works even when $Month is for December ]
                             Exit Loop IF [ $Month > $ThisMonth ]
                             New Records/Requst
                             Set Field [PartnerMonths::_fkPartnerID ; $PartnerID ]
                             Set Field [ParnterMonths::Month ; $Month ]
                          End Loop
                          Go to Layout [Original layout]
                       End If

                       Note:  The first time that you run this script for a given record in Partners, there will be no records in PartnerMonths to refer to in order to figure out what new records to add. You can specify any arbitrary date where I put "1/1/2014" as long as it is in the past and for the first day of that month. The earlier the date that you select here, the longer the delay, the first time around as the script will need to loop more times to generate the needed set of records. After the first time, from zero to only 1 or 2 records will likely be created by this script each time that it is triggered and I don't think you'll see much if any delay while the records are generated.

                  • 6. Re: Charting by Related Data
                    pnomads

                         Wow, that is really great! I set it up as an OnRecordLoad script trigger for my Partners Form View layout and it definitely starts working. A couple questions though:

                         1) When the script runs, I see the mouse cursor turn from an arrow to the command key with a period next to it. I set the script to start creating records from 1/1/2013. I let it run for 10 minutes and the cursor never changes, which I assume means the script is still running. However, if I cancel it with the ESC key and check the PartnerMonths records, records are created for that partner from 1/1/2013 to 5/1/2014. And if I switch to the next partner record, and quickly go back to the one that was previously done, it still gets stuck on the command key with a period cursor and never exits. I have a very small data set for testing at this point about 10 partner records and 20 donation records, so it shouldn't be struggling due to that. Any thoughts?

                         2) You had mentioned that there was a way to delete records so that only the last 12 months would be showing on the chart displaying the donation records. What would be the best way to implement that in the script?

                    • 7. Re: Charting by Related Data
                      philmodjunk

                           Better double check your script and compare the details of it with mine. I just ran a test in a test file to make sure that there were no issues and it created a set of records in PartnerMonths starting with 2/1/2013 and ending with 5/1/2014 just as it should. (to get 1/1/2013 as the first record, put "12/1/2012" as the initial value.)

                           You may want to post your copy of this script if you can't spot why it isn't working. To post a script to the forum:

                             
                      1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
                      2.      
                      3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
                      4.      
                      5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                      6.      
                      7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.

                           2) The same script can start by finding all records with the same PartnerID, but with dates in the Month field that are less than a specified number of months earlier than today's month, year. It can then delete those records.

                           #Delete all records for this partner with dates more than 12 months earlier than today
                           Set Variable [$PartnerID ; Partners::__pkPartnerID ]
                           Go to Layout ["PartnerMonths" (PartnerMonths) ]
                           Enter Find Mode [] ---> clear the pause check box
                           Set Field [ PartnerMonths::_fkPartnerID ; $PartnerID ]
                           Set Field [ PartnerMOnths::Month ; "<" & Date ( Month ( Get ( CurrentDate ) ) - 12 ; 1 ; Year ( Get ( CurrentDate ) ) ) ]
                           Set Error Capture [on]
                           Perform Find []
                           Delete All Records [no dialog ]

                           Notes: This script must be run from the Partners layout. I am using standard MMDDYYYY date formating used here in the United States. If you are working in a different country that uses DDMMYYYY formatting, you'll need to change the format used for GetAsDate ( ) to put the day before the month or you will not get the expected date.

                            

                      • 8. Re: Charting by Related Data
                        pnomads

                             That's odd that it works for you and not for me. I just double checked and it looks the same to me, and I ran it again after double checking with no change in behavior. I've attached a screenshot of the script just in case I'm missing something. I'm running it as an OnRecordLoad Script Trigger on my "PartnersForm" (Partners) layout. I'm in the US, so the date formatting shouldn't be the issue.

                             The script actually does what its supposed to do, it just hangs indefinitely until I cancel it. After its cancelled, if I go and check if the PartnerMonths records, they are created for that partner. The script just never ends...command period cursor and occasionally a split second of the spinning beach ball.

                        • 9. Re: Charting by Related Data
                          philmodjunk

                               Aha, It's the on recordLoad trigger. I didn't clue to that as I just clicked a button to test the script.

                               When the script uses Go to Layout [original Layout] to return to the Partners layout, this trips the OnRecordLoad trigger and the script runs all over again--resulting in an Infinite loop.

                               Let's make this change to the script to avoid that situation:

                               IF [ Not $$TriggersOff ]
                                  Set Variable [$$TriggersOff ; Value: True ]

                                  #Original script goes here

                                  Go to Layout [original Layout]
                                  Set Variable [$$TriggersOff ; value: False ]
                               End If

                               The triggers is still tripped a second time, BTW, but the global variable with the If block then causes scrip tot exit without doing anything and this breaks the loop.

                          • 10. Re: Charting by Related Data
                            pnomads

                                 That makes sense...So I implemented the changes to the script and it doesn't endlessly loop anymore, but it stopped creating the PartnerMonth records too. Attached is a screenshot of the modified script.

                            • 11. Re: Charting by Related Data
                              philmodjunk

                                   That should work. If you close and re-open the file, then trip this script trigger, what happens? (closing file will set the $$TriggersOff variable back to "False".)

                                   BTW, you don't need the last Go to Layout [original layout ] Step. I included that in my last example by mistake. But it's presence here should not make any difference in what happens.

                              • 12. Re: Charting by Related Data
                                pnomads

                                     Ok, closing and re-opening the file fixed it! Last question, should the "delete records older than 12 months" part of the script go just after the End Loop script step?

                                • 13. Re: Charting by Related Data
                                  philmodjunk

                                       The fact that this fixed it suggests that you ran the script once--setting the global field to True, but either the script was halted prematurely or you hadn't yet added the set variable step at the end to set the variable back to False. I sometimes keep a script in script manager that does nothing but set this variable to False so I can correct that issue during development. But if your scripts are designed correctly, this issue shouldn't happen during regular use.

                                       You could put those steps near the beginning of the layout or after the end loop. Just make sure that the script has brought up the PartnerMonths layout before it tries to find records and delete them.

                                  • 14. Re: Charting by Related Data
                                    pnomads

                                         Thank you so much, it works perfectly! I really appreciate the time you took to walk me through this. I learned a lot, and have taken note that variables and scripting are two areas I need to learn a lot more about. Thanks again!

                                    1 2 Previous Next