3 Replies Latest reply on Sep 10, 2013 2:03 PM by philmodjunk

    Finding accounts for recurring billing based on two fields

    TomFairley

      Title

      Finding accounts for recurring billing based on two fields

      Post

           Here's the business case...

            

           I have a number of clients that I bill on a Quarterly, Annual, Semi-Annual or Monthly basis.  I need to script a find that will show all the accounts that require an invoice to be generated in a certain month.

           The field "Billing_Cycle" records the frequency of billing (Quarterly, Annual, Semi-Annual or Monthly as "3", "12", "6", and "1" respectively)

            

           The field "Billing_Renewal" records the month they signed up ("January", "February" and so on)

            

           So for instance for February I would need to see the following criteria met:

           Billing_Cycle = "1"

           OR

           Billing_Cycle = "3" AND Billing_Renewal = "February" OR "May" OR "August" OR "November"

           OR

           Billing_Cycle = "6" AND Billing_Renewal = "February" OR "August" 

           OR

           Billing_Cycle = "12" AND Billing_Renewal = "February" 

            

           HELP!  I've tried a number of scripts and nothing seems to test correctly!! :(

            

        • 1. Re: Finding accounts for recurring billing based on two fields
          philmodjunk

               You have two different challenges for your script to deal with:

               1) you need an "OR" type of find logic where you are finding records with criteria matching any one of several multiple sets of criteria.

               2) the criteria specified will change for the current month.

               The first issue can be handled with either a script that generates multiple find requests specifying different criteria for each find or by performing a find for the first set of criteria followed by several extend found set operations.

               The second issue can be handled with a script that uses Get ( CurrentDate ) to calculated the correct criteria.

               See this thread for examples of scripts that handle the first issue as well as examples of scripts that calculate criteria to be used in the finds: Scripted Find Examples

               To use the current month as Find Criteria: MonthName ( Get ( CurrentDate ) )

               To compute the name of the month 3 months from the current month to use as find criteria:
               Let ( M = Month ( Get ( CurrentDate ) ) ; MonthName ( Date ( M + 3 ; 1 ; 2013 ) ) )

               To compute names for other intervals such as 6 and 9 months, just change the + 3 term in the above calculation.

          • 2. Re: Finding accounts for recurring billing based on two fields
            TomFairley

                 Thanks Phil.

                  

                 I was thinking of keeping it even simpler (if it makes sense)

                  

                 I have buttons setup, one for each month of the year, that would run a script for which accounts require billing...  I'd like to create 12 separate scripts, one for each month.. this is because we invoice in advance for those who need to send in cheques.  I need the ability to run the October report sometime around the end of Aug, beginning of Sept.

                  

                 basically I have tried the following, but on testing it does not perform as desired...

                  

            Perform Find [ Specified Find Requests: Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=1”
                                 

            Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=3” AND T01_ACCOUNTS::Billing_Renewal: “=September, December, March, June”
                                Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=6” AND T01_ACCOUNTS::Billing_Renewal: “=September, March”

            Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=12” AND T01_ACCOUNTS::Billing_Renewal: “=September” ]
                                [ Restore ] 

                                 

            This one also didn't work.  I tried doing a find and extending found record sets...

            Perform Find [ Specified Find Requests: Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=1”

            Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=12” AND T01_ACCOUNTS::Billing_Renewal: “=January”

            Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=3” AND T01_ACCOUNTS::Billing_Renewal: “=January”

            Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=3” AND T01_ACCOUNTS::Billing_Renewal: “=April”

            Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=3” AND T01_ACCOUNTS::Billing_Renewal: “=July”
                                               Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=3” AND T01_ACCOUNTS::Billing_Renewal: “=October”

            Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=6” AND T01_ACCOUNTS::Billing_Renewal: “=January”

            Find Records; Criteria: T01_ACCOUNTS::Billing_Cycle: “=6” AND T01_ACCOUNTS::Billing_Renewal: “=July” ]
                                               [ Restore ] 

                  

                  

                 PS - I printed the scripts to PDF to copy and paste the code above... So what you see should be exactly what I've got...

            • 3. Re: Finding accounts for recurring billing based on two fields
              philmodjunk

                   My mistake, you can't use month names as date criteria in a find. You can use day names like Monday, but not month names.

                   To find records dated for a given month and year, you'll need to use the month number in an expression such as: 3/2013 to find all records for March, 2013.