8 Replies Latest reply on Jan 13, 2009 3:29 AM by Orlando

    Searching Records and Displaying Results

    netguykb

      Title

      Searching Records and Displaying Results

      Post

      I have a table the that Tracks various types of activities (Activities) in my CRM like application. I have created a script that performs various finds, and sets a few fields in a related table (My Activity Count). The problem them I am having is that when no records are found I get the message indicating this however the My Activity Count::TodayNum column never gets updated resulting in a value that mayhave been inputted days earlier. This ultimately confuses the end user in think they have actual activities. Here is the scrip I wrote. It would appear that after performing the "Perform Find []" function the last part of the script does not execute if my variable = 0 or "". Any help would help to determine the correct order and or additioanl functions for this script to work.

       

      Enter Find Mode[]

      Set Field [Activities::activity_date_due; Get (CurrentDate)]

      Set Field [Activities::activity_assigned_to; Get (AccountName)]

      Set Field [Activities::activity_outcome; "Followup Call"]

      Duplicate Recod/Request

      Set Field [Activities::activity_outcome; "Followup E-Mail"]

      Duplicate Recod/Request

      Set Field [Activities::activity_outcome; "Initial E-Mail"]

      Duplicate Recod/Request

      Set Field [Activities::activity_outcome; "Initial Call"]

      Perform Find []

      Set Variable [$today; Value:Get (FoundCount)]

      IF [$today = 0 or ""]

       Set Field [My Activity Count::TodayNum;0]

      ELSE

       Set Field [My Activity Count::TodayNum;$today]

      END

       

       

       

       

       

       

       

       

       

        • 1. Re: Searching Records and Displaying Results
          Orlando
            

          Hi netguykb,

           

          at the start of your script insert the script step Set Error Capture and make sure the settings are on "On" this will supress the error dialog when no records are found.

           

          Also the If function you are using after performing the find, if they are just to set the number of found records in your fileds, could simply be

           

            Set Field [ My Activity Count::TodayNum ; Get ( FoundCount ) ]

           

          This will return zero if no records are found, but also not that if no records are found you will not be able to insert the value unless it is a global. 

           

             

          • 2. Re: Searching Records and Displaying Results
            fitch
              

            You shouldn't need any If/Else at the end: when there are no records found, Get( FoundCount ) will be zero. So the line after the Else is all you need. 

             

            However, when there are no records found, the relationship is not valid, so Set Field will fail; I guess you'll need the If/Else after all so that you can show all records or something after no records are found. 

             

            But the If should read simply:

             IF [$today = 0]

            ...

            FYI the way to write that kind of statement is:

            IF [$today = 0 or $today = ""]

             

            or better IMO:

            IF [$today = 0 or IsEmpty($today)

            • 3. Re: Searching Records and Displaying Results
              netguykb
                 I did the 2nd option of keeping the IF statements and the 1st options to include the "No Records Found" Dialog box from displaying. The table is still not getting updated with 0. I know this becuase I created a layout view of the table My Activity Count::TodayNum and the number is the last number that I had in their yesterday.
              • 4. Re: Searching Records and Displaying Results
                Orlando
                  

                Try, 

                 

                  #

                  Set Variable [ $Today ; Get ( FoundCount )

                  Go to Layout [ Activity Count ]

                  Set Field [ Activity Count::TodayNum ; $Today ]

                  # 

                 

                most likely reason is you do not have an active relationship to the Activity Count table when you are in a found set of zero records, so you need to navigate to a layout based on that TO

                • 5. Re: Searching Records and Displaying Results
                  netguykb
                     I understand however now the user is taken to a layout that is not desirable or for that matter should not be seen. From a work flow perspective, this is cumbersome. Is their way to make this update as transparent as possible?

                   

                  Currently the accounts table is joined to the My Activity Table. Not sure if I need to create a relationship but maybe rather use the table for just collecting data with no joins. Thoughts?


                  • 6. Re: Searching Records and Displaying Results
                    Orlando
                      

                    Just end on

                     

                      Go to Layout [ Original Layout ]

                     

                    This will take them back to where they pressed the button. Also if the screen flickers or the process is noticeable add in the Freeze Window script step at the start of your script.

                    • 7. Re: Searching Records and Displaying Results
                      netguykb
                        

                      Ok I noticed a separate issue, the Code for updating Counts (Line 15) does not update where the user logged in = AccounName It simply updates the first record in the table which may not be the correct user. I have included a simple ERD for the two tables in question. I have also Included the My Activity Count table. Currently there are only two users in the system. I am questioning my relationships between the primary account table and the My Activity Account Table. The union between the two is Account Name.

                       

                       

                      Table To Store Data for the various counts on Activities assigned to specific users

                       

                      If Scoan adds activity for today then Kbrady TodayNum gets updated

                       

                       

                      I tested this by making sure that user kbrady had 0 in column TodayNum, user scoan added an activity and ended up updating the kbrady TodayNum to 1.

                      1| Set Error Capture [On]
                      2| Enter Find Mode[]
                      3| Set Field [Activities::activity_date_due; Get (CurrentDate)]
                      4| Set Field [Activities::activity_assigned_to; Get (AccountName)]
                      5| Set Field [Activities::activity_outcome; "Followup Call"]
                      6| Duplicate Recod/Request
                      7| Set Field [Activities::activity_outcome; "Followup E-Mail"]
                      8| Duplicate Recod/Request
                      9| Set Field [Activities::activity_outcome; "Initial E-Mail"]
                      10| Duplicate Recod/Request
                      11| Set Field [Activities::activity_outcome; "Initial Call"]
                      12| Perform Find []
                      13| Set Variable [$today; Value:Get (FoundCount)]
                      14| Go to Layout [“My Activity Count” (My Activity Count)]
                      15| Set Field [My Activity Count::TodayNum;$today]
                      16| Go To Layout[“Accounts” (Accounts)]
                      17 IF [$today = 0 or IsEmpty($today)]
                      18|  Set Field [My Activity Count::TodayNum;0]
                      19|  Perform Script [“My Records”]
                      20| ELSE
                      21|  Set Field [My Activity Count::TodayNum;$today]
                      22| END













                      • 8. Re: Searching Records and Displaying Results
                        Orlando
                          

                        OK it looks to me like the issue may be line 14, Go to Layout [“My Activity Count” (My Activity Count)] You are going to the layout and it may or may not be on the current users record. I would suggest performing a find once you get to that layout and searching for the current users.

                         

                        Is there only one record per account in the 'My Activity Count' table?

                         

                        Also between lines 16 and 22 there seems to be duplication of step 15 and if you need to run the script My Records if no activities are found I have included this below.

                         

                        13| Set Variable [$today; Value:Get (FoundCount)]

                        14| Go to Layout [“My Activity Count” (My Activity Count)]

                        15| Enter Find Mode []

                        16| Set Field [ My Activity Count::account name ; Get (AccountName) ]

                        17| Perform Find [] 

                        18| Set Field [My Activity Count::TodayNum;$today]
                        19| Go To Layout[“Accounts” (Accounts)]

                        20| If [$today = 0 or IsEmpty($today)]

                        21|   Perform Script [“My Records”]

                        22| End if

                         

                        Does this help?