1 2 Previous Next 19 Replies Latest reply on Apr 22, 2017 11:49 AM by Menno

    select years

    PeterCortiel

      I have a donations -> donors pair of tables

      I need to find the donors who donated during the last 3 years but not yet this year.

      I have a date_donated field in donations

       

      This gives me what I need

       

       

      ExecuteSQL (

      "select   b.kp_donors_id,

      b.fullname,

      a.deposit_date_year

      from donations a

      Join donors b on a.kf_donors_id = b.kp_donors_id

      Where year (a.date_deposited) in (2016, 2015, 2014) and not year (a.date_deposited) =2017 ";

      "";

      ""

      )

       

      But I don't know what to do with it

      Once I know how I can use to filter the donors i'll replace the hard coded years with calculated values

      Can anyone help?

       

      Peter

        • 1. Re: select years
          beverly

          What does your query look like? Can you post it?

           

          Sent from miPhone

           

          EDIT: aplogies, your posted query did not appear on my phone. I see that you have it here.

          • 2. Re: select years
            SteveMartino

            If this is a find you always need, why not just use FileMaker's native find and omit in a script?

            Is this a report you will need to generate at the start of a year or is this a rolling report that you would want to produce on the current date.

            • 3. Re: select years
              PeterCortiel

              ExecuteSQL (

              "select   b.kp_donors_id,

              b.fullname,

              a.deposit_date_year

              from donations a

              Join donors b on a.kf_donors_id = b.kp_donors_id

              Where year (a.date_deposited) in (2016, 2015, 2014) and not year (a.date_deposited) =2017 ";

              "";

              ""

              )

              • 4. Re: select years
                PeterCortiel

                Thats what I want to do but don't know how

                 

                What shall i do in my script with the content of the query?

                • 5. Re: select years
                  philmodjunk

                  ExecuteSQL (

                  "select   b.kp_donors_id,

                  b.fullname,

                  a.deposit_date_year

                  from donations a

                  Join donors b on a.kf_donors_id = b.kp_donors_id

                  Where

                      year (a.date_deposited) >=? AND

                      year (a.date_deposited) <=? ";

                  "";

                  ""

                  Year ( Get ( CurrentDate ) ) -3 ; Year ( Get ( CurrentDate ) ) - 1 )

                   

                  Query kept simple so that it's easy to understand. If I were to use this query in one of my solutions, I'd use a different format that doesn't enclose field and table occurrence names in quotes.

                  • 6. Re: select years
                    PeterCortiel

                    I know the sql part. What I don't know is what to du with the returned list in fm

                     

                    I need to filter the donors so I can create a letter to previous donors who have not donated yet this year

                    I imagine a button when pressed filters the donors to the list from my sql query

                     

                     

                    I know i hate those letters myself but for a nonprofit it is essential

                    • 7. Re: select years
                      beverly

                      If you get the "list" of donor ID's from the query ok, then you can use that list in a global field that related to the donors. this would be the multi-line key relationship. It would need to be stored, so I suggest NOT making it a calcultion, but a scripted Set Field. Update as needed (by button or trigger). The relationship is used for the portal that needs no further "filtering".

                       

                      beverly

                      • 8. Re: select years
                        Menno

                        I think your query does not yield the result you need, because it will only not list the donations made in 2017. However the donators whom have donated in 2017 and also in 2014-2016 will stil be in the result. So you should change your query and FM doesn't work very well with subqueries so you need to carve your query into at least 3 others. Something like:

                         

                        Let( [

                             yr = Year ( Get ( CurrentDate ) ) ;

                             in = Substitute ( List ( yr - 3 ; yr - 2 ; yr - 1 ) ; [ ¶ ; "," ] ) ;

                             list1 = ExecuteSQL (

                                  "SELECT DISTINCT kf_donors_id" &

                                  " FROM donations " &

                                  " WHERE deposit_date_year in (" & in & ")" ; "" ; "," ) ;

                             list2 = ExecuteSQL (

                                  "SELECT DISTINCT kf_donors_id" &

                                  " FROM donations " &

                                  " WHERE deposit_date_year=" & yr ; "" ; "," )

                        ] ;

                             ExecuteSQL(

                                  "SELECT kp_donors_id,fullname FROM donors WHERE kp_donors_id IN (" & list1 & ") " &

                                  If ( list2 ≠ "" ; " AND NOT(kp_donors_id IN (" & list2 & ") )" ) ; "" ; "" )

                        )

                        • 9. Re: select years
                          PeterCortiel

                          Where year (a.date_deposited) in (2016, 2015, 2014) and not year (a.date_deposited) =2017

                           

                          Should give me donors who gave in 2016, 2015, 2014but not yet n 2017

                           

                          Or what

                          • 10. Re: select years
                            Menno

                            No it gives all the donations in 2014, 2015 and 2016 except for the ones in 2017. So you can just as well leave "and not year (a.date_deposited)=2017" out of the query, the result will be exactly the same.

                             

                            See the attached file

                            • 11. Re: select years
                              philmodjunk

                              Seems simpler to perform a Find instead of evaluating an ExecuteSQL query.

                               

                              You can then then use the data in your found set to produce your letters.

                              • 12. Re: select years
                                PeterCortiel

                                but how? I know how to find a singe word but not a calculation

                                • 13. Re: select years
                                  philmodjunk

                                  I'm not sure what you mean by "single word", but here's a scripted Find :

                                  Go To Layout [ "donors" (donors) ]

                                  Enter Find Mode [pause:off]

                                  Set field [ Donations::Date_deposited ; Year ( Get ( CurrentDate ) ) - 3 & "..." & Year ( Get ( CurrentDate ) ) - 1 ]

                                  New Record/Request

                                  Set Field [ Donations::Date_deposited ; Year ( Get ( CurrentDate ) ) ]

                                  Omit Records

                                  Set Error Capture [on]

                                  Perform Find [   ]

                                   

                                  Merge text on a layout can combine data from your table with layout text to produce a "form letter" that can be printed or saved as PDF.

                                   

                                  Calculations can also be used to combine quoted text with data from your database to produce a letter that can be printed, PDF'd or put in the body of an email.

                                   

                                  Calculations that insert data from your tables into text entered into a text field in order to produce the letter can also be printed, PDF'd or put into an email, and this last option makes updating such a letter a data entry task rather than having to redesign a script or calculation.

                                  1 of 1 people found this helpful
                                  • 14. Re: select years
                                    Menno

                                    This is the same example, but now scripted

                                    1 of 1 people found this helpful
                                    1 2 Previous Next