3 Replies Latest reply on Nov 9, 2015 9:01 AM by Mike_Mitchell

    Table relationship with an empty value

    ericjlindholm

      I have a Event::Cwarnings field that is a calculation based on different things like event status in relationship to the end date.  for example, if the event is over and not invoiced or paid, returns a warning that says "not invoiced."

       

      If there are no warnings and the field is empty.

       

      I want to create a table relationship with the user homepage where

       

      User::User ID = Event::Account Manager

      AND

      (?unknown field representing a non value?) does not equal Event::cWarnings

       

      I was able to do this with User::User ID = Event::Account Manager and filtering the portal but the performance is lacking because there are many event records to filter.

       

      Thanks!

        • 1. Re: Table relationship with an empty value
          Mike_Mitchell

          I'm not 100% understanding your business need, but let me suggest that, instead of creating an unstored calculation (which is what this would have to be), you build some scripting into the solution that updates a field as part of the business flow. Whenever a user takes an action that might change the status of the warning flag, you would run the calculation for that particular record only and then update the field. That spreads out the performance impact and keeps it from dragging the entire system down every time the screen refreshes.

           

          An alternative would be to use a batch script, run from the server, to update the flag. You would run that on an appropriate frequency (based, again, on the business rules).

           

          HTH


          Mike

          • 2. Re: Table relationship with an empty value
            ericjlindholm

            Mike,

             

            I ended up writing a script that updates populates a field to my relationship to work on record load and commit.  my only issue now is that all those calculations are based on get(current date) and a loop script to update all the events needs to be triggered every morning to update everything.  I have a flawless batch script that I can run manually and the only error i get is when it reaches the last record in the loop. 

             

            when I try to schedule it in server, it returns  an error every time.  I have been researching how to debug scripts run on the server but haven't figured it out yet. 

             

            Thanks for your help

            • 3. Re: Table relationship with an empty value
              Mike_Mitchell

              ericjlindholm wrote:

               

              Mike,

               

              I ended up writing a script that updates populates a field to my relationship to work on record load and commit.  my only issue now is that all those calculations are based on get(current date) and a loop script to update all the events needs to be triggered every morning to update everything.  I have a flawless batch script that I can run manually and the only error i get is when it reaches the last record in the loop.

               

              when I try to schedule it in server, it returns  an error every time.  I have been researching how to debug scripts run on the server but haven't figured it out yet.

               

              Thanks for your help

               

               

              1) What error does it return?

               

              2) You can't do much in the way of debugging with server-side scripts (since the client isn't the server). What you can do instead is create a logging table that writes out records based on events that occur in your script.

               

              3) Common errors associated with performing server-side scripts:

               

              • Permissions: Make sure the account under which the script is run has appropriate permissions to access what it needs
              • Context: Make sure the script navigates to the correct layout ahead of time
              • Script step compatibility: Make sure all script steps are server-compatible

               

              As an aside: I'm not sure you need both OnRecordLoad and OnRecordCommit. Probably overkill. But I could be wrong.