12 Replies Latest reply on Aug 27, 2012 6:25 AM by mb3210

    Calculation for no donation in last 3 years

    mb3210

      Title

      Calculation for no donation in last 3 years

      Post

      Hi!

      I'm struggling with writing a proper calculation for the field No Donation in Past 3 years (also including no donation ever made.  I want to use this field in a script to get a list of these donors sorted by the date of last donation.  After alot of effort I came up with:

      Year ( Date_Last_Donated ) ≤ Year (Get( CurrentDate)) - 3       

      I indicated the result to be text.  It does not return the proper result.  Can you help??

        • 1. Re: Calculation for no donation in last 3 years
          philmodjunk

          Is this criteria to be used in a Find Request?

          If so:

          Enter Find mode []
          Set field [YourTable::Date_Last_Donated ; "≤" & Year (Get( CurrentDate)) - 3 ]

          If it is not used as find criteria, please explain how you intend to use this calcualtion to find your donations data.



          • 2. Re: Calculation for no donation in last 3 years
            mb3210

            I will be using the find in a script.  When I tried to set up your find reqeust, I got an error message:  "The value of this field must be a valid date in the range of years 1 to 4000 and should look like "12/25/2003."  

            Undecided  Thanks!!

            • 3. Re: Calculation for no donation in last 3 years
              philmodjunk

              I ran a quick test on one of my files and it works perfectly.

              Did you include the first step to enter find mode?

              You'll get that error if the set field step is executed when in browse mode.

              • 4. Re: Calculation for no donation in last 3 years
                mb3210
                I am SO frustrated and feel so stupid!  This is exactly what I did:
                Enter Find Mode [Pause]
                Set Field [CON_CONTACTS::Date_Last_Donated]
                • Clicked on Specify and entered:
                • CON__CONTACTS::Date_Last_Donated; " ≤ " Year(Get(CurrentDate)) - 3
                I then got error message on the calculation that "An operator is expected here:  ; " ≤ " Year(Get(CurrentDate)) - 3
                Please forgive my ignorance.  I'm just determined to get this right if you can put up with me!
                Thanks!
                • 5. Re: Calculation for no donation in last 3 years
                  philmodjunk

                  Note that my example has:

                  Enter Find mode[]

                   and your script has:

                  Enter Find Mode [pause]

                  Clear the pause check box so that there is no chance of exiting find mode prematurely and this should work for you.

                  • 6. Re: Calculation for no donation in last 3 years
                    mb3210

                    Thanks anyway for all your trouble!  The problem still remains..........I can't get the calculation to be accepted. 

                    • 7. Re: Calculation for no donation in last 3 years
                      philmodjunk

                      Please post the exact script you have created.

                      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 Answer.
                      2. 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.
                      3. If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                      4. 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. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)
                      • 8. Re: Calculation for no donation in last 3 years
                        symbister

                        Hi 

                        you've probably noticed already but the reason your calculation isn't being accepted - you've missed out the concatenation '&' in the formula. check Phil's post again.

                        • 9. Re: Calculation for no donation in last 3 years
                          mb3210

                          I have repeatedly copied & pasted Phil's exact message into the calculation as:

                          CON__CONTACTS::Date_Last_Donated; "≤" & Year (Get( CurrentDate)) - 3 

                          Each time I get an error message saying "An operator (e.g. +, -, *, ...) is expected here" and the following is highlighted:

                          CON__CONTACTS::Date_Last_Donated; "≤" & Year (Get( CurrentDate)) - 3 

                          As a result, there is no script to upload.  I have :

                          1. Enter Find mode
                          2. Set field (where I have the calculation that won't get accepted
                          • 10. Re: Calculation for no donation in last 3 years
                            symbister

                            I have similar db - screenshot worked fine (donations::donation_date is set as type date, and so is set field specification)

                            But this wouldn't help the missing operator error. Sounds odd, but suggest you don't copy and paste, but type it in directly..

                            • 11. Re: Calculation for no donation in last 3 years
                              philmodjunk

                              You are trying to paste in: CON__CONTACTS::Date_Last_Donated; "≤" & Year (Get( CurrentDate)) - 3

                              when you can only paste:"≤" & Year (Get( CurrentDate)) - 3

                              When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                              • 12. Re: Calculation for no donation in last 3 years
                                mb3210

                                Good morning Phil,

                                Please forgive me for not responding to your latest answer...I was out of the office.  IT WORKED!!!  I can't thank you enough.  It is really apparent that I have a lot to learn and am doing VTC tutorials.  Again thank you & God bless you!

                                Mother Barbara