8 Replies Latest reply on Apr 12, 2016 1:28 PM by sonic10

    Finding volume over a date range

    sonic10

      Title

      Finding volume over a date range

      Post

           I have an old Appleworks program for calculating donations that I continue to use because I can't figure out how to make the same calculation in Filemaker. I have to hang onto an old computer to run the program but Appleworks keeps crashing and I'm afraid I'm destined to lose 8 years worth of information.

             
      •           Donors can give up to 500 units every 2 months.
      •      
      •           Appleworks have a command (NOW? - I don't remember right off hand) for today's date and I subtract 60 from it to define the range.
      •      
      •           The database then adds up the donations over 60 days and subtracts from 500 to get the donor's current available amount.

           Suggestions for doing this in FMpro12?

        • 1. Re: Finding volume over a date range
          philmodjunk

               There are a number of options for doing that in FMP 12. The details depend on the structure of your database.

               It might be as simple as creating a summary report based on your donations table with the records grouped by Donor. You'd first perform a find to find all donations records for a given 2 month time interval. In a script, get ( CurrentDate ) & "..." Get ( CurrentDate ) - 60 can be used to find all donations for the last 60 days.

               Then a sub summary layout part can show the total for each donor after sorting to group them by Donor. A "total of" summary field will give you that total. You can then define a calculation field that uses getSummary to access the sub total for each group of records so that you can subtract that subtotal from 500. You can then put this calculation field in the sub summary layout part to show that result. (You don't have to put the summary field here unless you want to, but I'd but it there as least at first to confirm that the report works and then remove it later if I don't need it.)

               The report can be structured to list all the donations with a subSummary layout part serving as a "sub head" with the totals or you can remove the body layout part from this layout and get just one row of data for every donor that donated in the specified date range.

               Here's a thread that gives scripted find examples that you may find useful: Scripted Find Examples

               Here's an old but still useful tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

               Please post any questions you have about either of these here, not in those other threads so that I can answer them in the context of your original post.

          • 2. Re: Finding volume over a date range
            sonic10

            I just got around to actually doing this, hence the delay in response. I seem to have it done except that the "Get (CurrentDate) - 60" command isn't working. I set this up on March 31 and it keeps giving me Jan 31 for 60 days ago. Do I have to reset the CurrentDate? I had to do that in the old AppleWorks program. If so, how?

            • 3. Re: Finding volume over a date range
              RickWhitelaw

              make sure the calf is unstored.

              • 4. Re: Finding volume over a date range
                RickWhitelaw

                CALC I mean. Frigging auto enter.

                • 5. Re: Finding volume over a date range
                  sonic10

                  An unstored calf always ends up in the middle of the highway. They're kind of stupid like that. I know - I grew up on a farm.

                  • 6. Re: Finding volume over a date range
                    erolst

                    Calf or no calf – if you're using that calculation in a script, there is nothing to store anyway:

                     

                    Enter Find Mode

                    Set Field [ YourTable::yourDateField ; Let ( cd = Get ( CurrentDate ) ; cd - 60 & ".." & cd ) ]

                    Set Error Capture

                    Perform Find

                    # [ etc. If [ Get ( FoundCount ) ] ]

                     

                    There – no need to store that date anywhere, and the function will return the correct date.

                     

                    Note that you can express "all from now until 60 days ago" either as

                     

                    Let ( cd = Get ( CurrentDate ) ; cd - 60 & ".." & cd )

                     

                    like in the script, or simply as

                     

                    Get ( CurrentDate ) - 60 & ".."


                    meaning the range operator is smart enough to add a missing edge value automatically

                    • 7. Re: Finding volume over a date range
                      sonic10

                      Your answer lead me to the solution although perhaps not what you had in mind. I opened the Storage Options for my Date_Test (the field for "Get (CurrentDate)-60"). The options shown are Global Storage and Indexing. I left Global Storage unchecked (which seemed to be your suggestion) but checked the Indexing box for "Do not store calculation results -- recalculate when needed". Not the Date_Test field always gives 60 days ago.

                      • 8. Re: Finding volume over a date range
                        sonic10

                        Correction - Now the Date_Test field always gives 60 days ago.