5 Replies Latest reply on Feb 26, 2014 4:44 PM by philmodjunk

    Script for absent costumers

    AlexTorry

      Title

      Script for absent costumers

      Post

           Hello, I have a solution that tracks my costumers payments, I want a script to show me the costumers who have not been around for 6 months, I usually check that by consulting each costumer's payment history by tracking their last payment but it would be very funcional to have a script that shows me in a list view all customers whom have been absent for more than 6 months.

           Any ideas on how to achieve this?

           Thanks 

        • 1. Re: Script for absent costumers
          philmodjunk

               If you have this relationsihp:

               Customers-----<Payments

               On the Customers layout you can perform a find specifying a date range for the last 6 months in a date field from Payments.

               This will find all Customers that have made at least one payment in the last 6 months.

               Then Show Omitted Only can be used to pull up all the customer records that weren't found--which will be all customers that did not make a payment in the last 6 months.

               This process can be scripted: Scripted Find Examples

          • 2. Re: Script for absent costumers
            AlexTorry

                 Thanks Phil it works perfectly!

                 Now I have a new task.... I have this relationship:                  

                              l-----< Payments

            Customers 

                              l-----< Notes

            For every payment recorded I ask my secretary to write a note (like a description of the experience with the costumer)  so I need a Script that shows me the costumers that have payments recorded but no corresponding note according to the date it happened, both payment and note tables have a date field.

            Thanks in advance

            • 3. Re: Script for absent costumers
              philmodjunk

                   From your relationships, that can't happen. There is no link that links a note directly to a given payment. They all link back to the customer instead.

                   You might consider using a text field in payments to record the note instead of a separate table. If some notes are not linked to payments, you might use one of these two relationship options (or both):

                   customers----<payments-----Notes

                   customers-----<Notes-----Payments

              • 4. Re: Script for absent costumers
                AlexTorry

                     Hello Again,

                     Im Having a problem with this script, I'm getting a costumer that has a payment recorded last week and is being shown as a costumer who has been around in the last 6 months as the script is supposed to show, I have no idea why, the date range I am using is this:

                     Let ( [ m = Month ( Get ( CurrentDate ) ) ;
                                y = Year ( Get ( CurrentDate ) ) ;
                               m1 = m - 6 + If ( m < 6 ; 12 ) ;
                               y1 = y - If ( m < 6 ; 1 ) ;
                               m2 = m - 1 + If ( m = 1 ; 12 ) ;
                                y2 = y - If ( m = 1 ; 1 )
                              ];
                              m1 & "/" & y1 & "..." & m2 & "/" & y2
                            )
                      
                     I also double checked the date on the payment recorded last week and is correct....any ideas??
                      
                     PS. Attached image of the script
                      
                       
                      
                     Thanks

                      

                • 5. Re: Script for absent costumers
                  philmodjunk

                       Apologies but I can't read the language and there aren't enough "clues" to the format of your script to reliably guess at the English language equivalents to these steps.

                       

                            I'm getting a costumer that has a payment recorded last week and is being shown as a costumer who has been around in the last 6 months as the script is supposed to show,

                       Is there a mistake in what you wrote there? I read that as a description of a script that is working correctly, not one that is not working correctly.

                       

                            I also double checked the date on the payment recorded last week and is correct....any ideas??

                       Check to make sure that the date field is of type date and not text--this is a common error we need to rule out.

                       But did you check that against the date range computed by your calculation? I'm not sure why it excludes the current month from this range as it only finds records from 6 months ago to last month--excluding any dates for this month...