3 Replies Latest reply on Aug 7, 2014 10:06 AM by ultranix

    Top10-Top20 prospects in database

    ultranix

      Title

      Top10-Top20 prospects in database

      Post

      Preface. I have a feature „Top10“ and Top20“ prospects in my database, which is very dynamic – prospects either turn to Customers or Non-Customers in a matter of days. Therefore, Top10/Top20 features were created in order to sort on which prospects I need to put more focus than on others. Thus, Top10 prospects are those, whom I am going to meet in an upcoming week, while Top20 prospects in most cases are those, whom I already met, but they haven‘t decided whether they are going to be customers or not (I-want-to-think-it-over case).

      Setting. In order to track which prospects are put into Top10/Top20 category, I created a table TOP, where the most important fields are id_conta (foreign key, ID of prospect, that matches primary key from BASE id_conta), date_start, date_end (both Self-explanatory), date_end_cond (field that is auto-entered every new day with date value of tomorrow, if date_end field is empty. I couldn‘t think of another way to achieve it) and top, which, depending on category is either “10” or “20”.

      Relationships. PAY table is linked with TOP_10 table (based from TOP table) in TOP10 and PAY table is linked with TOP_20 table (again, based from the same TOP table) in TOP20 situation.

      Date_end_cond. Purpose is simple – I only need active (i.e. which have empty date_end field) and ended during the current day to be displayed (i.e. date_end field = xdate (which is the global date field in the layout and according to which all the records are displayed). In order to have date_end_cond field filled, I created a script, which, everytime the file opens, is run and it basically updates the date_end_cond with the date of tomorrow, if date_end field is empty.

      Adding to TOP10/TOP20. Here I displayed the script, which I perform everytime I want to add client to Top10. First of all, it checks whether a prospect is already in Top10 list and in that case it prompts about it. If not, it creates new record in TOP database and then goes to check, whether the same prospect is already in Top 20. Nobody can be Top10 and Top20 at the same time. So if you become Top10, you lose your Top20 status and vice versa. When this is done, script goes back to original layout and sets BASE::top_cond field to either 10 or 20, which I use for conditional formatting (If any of them is not empty, the particular number is highlighted (10 or 20)).

      Removing from TOP10/TOP20. Script’s scheme is similar – first of all it checks, whether a prospect is in Top10/Top20 list. If not, you’ll be prompted with a message. If yes, it sets current date value to date_end and goes back to original layout, clearing BASE::top_cond field value, so no number is highlighted.

      Usage. I mainly use this feature in 2 ways. 1) I have two lists – Top10 and Top20, where portals from TOP_10 and TOP_20 table occurences are displayed on PAY layout (relationship could be viewed above). So in that way, I want to see how many Top10 and Top20 prospects are for the selected date (xdate field, drop down calendar, then runs a script to find actions for only that date, but this is redundant for Top10/Top20 viewing puposes). 2) When client card is open, I see whether “Top10” or “Top20” is highlighted, so I see, whether a prospect belongs to any of those groups.

      Problems. 1) date_end_cond field doesn’t always produce appropriate results, that may be because some of the scripts are faulty. For example, today I had to have 6 prospects in the Top10 category, but it put values, for no proper reason, in date_end field of yesterday and therefore I was unable to view them in Top10 list. Is there a better way to have my purpose achieved without date_end_cond, or maybe there could be an improvements?

           2) When running Top10 [On] script, which also enters end_date for Top20 for the same prospect, I experienced some issues, like, when I wanted to remove prospect from Top10 list later, I got message, that “prospect is not in your Top10 list”, but it shouldn’t be this way. If customer is added to the Top10 for the first time (i.e. he didn’t belong to Top10 or Top20 list before), and I run Top10 [Off], no problems are present. So there may be issues with Top10 [On] script.

           I would appreciate any help with this, because I really need to get this working.

        • 1. Re: Top10-Top20 prospects in database
          SteveMartino

               I think you are making it way too complicated.  You may be better off with a 'Virtual List'.  This automatically creates the records and deletes them with out a lot of extra work (scripts, etc.)  Here's one I found online that I implemented quite easily and works great.

          https://dl.dropboxusercontent.com/u/71328624/VirtualList.zip

          • 2. Re: Top10-Top20 prospects in database
            philmodjunk

                 I'm going to focus my response on just this part:

                 

            Date_end_cond. Purpose is simple – I only need active (i.e. which have empty date_end field) and ended during the current day to be displayed (i.e. date_end field = xdate (which is the global date field in the layout and according to which all the records are displayed). In order to have date_end_cond field filled, I created a script, which, everytime the file opens, is run and it basically updates the date_end_cond with the date of tomorrow, if date_end field is empty.

                 If I read that correctly, you only need to match to records that:

                 a) Top_10 and Top fields match

                 b) xdate > date_start

                 AND

                 date_end is EMPTY correct? From what I can tell, the only reason you put today's date into date_end_cond is because Date_End is empty.

                 So here's an alternative expression that should not need a daily update of the date_end_cond field:

                 Define a calculation field with Number as the result type: IsEmpty ( Date_End ) in your top 10 table. In pay, define a calculation field with a number result with this simple expression:

                 1

                 Now change the last match field pair in your relationship to be:

                 Pay::constOne = Top_10::IsEmptyDateEndField

                 If I haven't missed a key detail, that should produce the same results that you are looking for without any need for daily updates of a date field.

                  

            • 3. Re: Top10-Top20 prospects in database
              ultranix
                   

                        a) Top_10 and Top fields match

                   

                        b) xdate > date_start

                   

                        AND

                   

                        date_end is EMPTY correct?

                   Well, as I said,

                   

                        I only need active (i.e. which have empty date_end field) orended during the current day to be displayed (i.e. date_end field = xdate