3 Replies Latest reply on Oct 7, 2011 9:21 AM by philmodjunk

    Reading all the notes instead of just the top one

    nickodm

      Title

      Reading all the notes instead of just the top one

      Post

      Hello,

      I was wondering if you know a solution to this particular problem:

      When looking at a contact we have a tab that someone can enter notes. Those notes are entered in a JobComments table. Each note has the text, the date stamp, and a method drop down box (ie. we called, they called, we emailed, etc).

      Recently I was trying to apply a rule to turn the contact text blue with white font if nobody has entered a note with the method of "we called" in 72 hours. 

      So I have something like this:

      Conditional Formating

      Formula Is

      (Get(CurrentDate) - GetAsDate(JobComments::Date)  ≥ 3) or ((Get(CurrentDate) - GetAsDate(JobComments::Date)  < 3) and (JobComments::Method  ≠  "We Called Customer"))

      Now this seems to ONLY check the LATEST note entered. So in a case where someone called the customer and then entered another note that is not method of "We Called Customer" the formating changes again.

      Is there any way I can check the ENTIRE array of notes instead of just the top one? 

       

      Thanks for all the help!

        • 1. Re: Reading all the notes instead of just the top one
          philmodjunk

          There are actually several methods you could use here. The list function could be used to produce a list of Method entries for the job comments records and FilterValues function can test to see if "We called customer" is present in that list, but you also need to limit this to a specific date range so it makes more since to set up a different relationship to the jobComments table that filters out all records except 'we called' entries that fall within a certain date range based on the current date.

          In your contact table...

          Define two unstored calculation fields:

          Today : Get (CurrentDate )
          TodayPlus3 : Get ( CurrentDate ) + 3

          Define a calculation field to return the specified text for Method in all records:

          constWeCalled : "We Called Customer"

          Now go to Manage | Database | Relationships and use the duplicate button (two green plus signs) to make a new occurrence of your JobComments table. Double click it so you can change its name to something more descriptive than JobComments 2.

          Set up this relationship:

          Contacts::ContactID = WeCalled72::ContactID AND
          Contacts::Today < WeCalled72::Date AND
          Contacts::TodayPlus3 > Wecalled72::Date AND
          Contacts::constWeCalled = WeCalled72::Method

          Now your conditional format expression can be:

          Not WeCalled72::ContactID

          • 2. Re: Reading all the notes instead of just the top one
            nickodm

            I assume that this change is not retro-active, right? I made the above changes and everything turned blue. :)

             

            I assume it's because a note has not yet been entered with this new relationship?

            • 3. Re: Reading all the notes instead of just the top one
              philmodjunk

              It is retroactive.

              As designed, no note entered will result in your blue color.

              If you used:

              JobComments::ContactID AND Not WeCalled72::ContactID

              You won't get the format change when no related comment record exists.