Method Community

 

SQL Override syntax to use Action Results, Shared Results, Screen fields in the condition

Last post 03-02-2016 8:07 PM by kefpa01. 7 replies.
Page 1 of 1 (8 items)
Sort Posts: Previous Next
  • 02-01-2016 9:45 AM

    SQL Override syntax to use Action Results, Shared Results, Screen fields in the condition

    I found SQL Override examples on the forum which allow me to change color/font of cells of a grid based on a condition.  It works great.  However, in the example below, the condition is against GETUTCDATE().  I would like the condition to be against a date field on the screen called 8DaysAway.  How do I reference screen fields or Action Results or Shared Reults in SQL Override statements?  Is it possible? If anyone has some syntax examples, I would be very grateful.  Or point me to documentation that goes into detail on SQL Override.  I'm not seeing much on the Method Customization pages.

    (CASE
    WHEN [DueDateEnd] < GETUTCDATE() THEN '<font color="red"><b>' + Entity + '</b></font>'
    WHEN [DueDateStart] < GETUTCDATE() THEN '<b>' + Entity + '</b>'
    ELSE '<font color="black">' + Entity + '</font>' END)

    Thanks for your help,

    Pam

    Pamela Keffer
  • 02-02-2016 4:27 PM In reply to

    Re: SQL Override syntax to use Action Results, Shared Results, Screen fields in the condition

    Hello Pam,

    Can you please let me know the field and the table you want to store that value in. Is 8DaysAway a field or a screen?

    -Inder

  • 02-04-2016 12:28 PM In reply to

    Re: SQL Override syntax to use Action Results, Shared Results, Screen fields in the condition

    Yes, 8DaysAway is a date field on a screen and the comparison field is a grid field from the Activity table

    Pamela Keffer
  • 02-04-2016 1:10 PM In reply to

    Re: SQL Override syntax to use Action Results, Shared Results, Screen fields in the condition

    Hello kefpa01,

    You syntax should look something like this:

    (CASE WHEN [DueDateEnd] < viewActivity.8DaysAway THEN '<font color="red"><b>' + Entity + '</b></font>' 
    WHEN [DueDateStart] < viewActivity.8DaysAway THEN '<b>' + Entity + '</b>' 
    ELSE '<font color="black">' + Entity + '</font>' END)

    I hope that helps!

    -Inder

  • 02-04-2016 1:21 PM In reply to

    Re: SQL Override syntax to use Action Results, Shared Results, Screen fields in the condition

    The SQL Override is on the grid column DueDateEnd and I want to compare it to a value on the screen called 8DaysAway.  So, I'm wanting the override to look something like this.

    (CASE WHEN [DueDateEnd] < 'screen value 8DaysAway' THEN ... )  Where 'screen value 8DaysAway' is a date on the screen.   How do I reference 8DaysAway in an SQL Override?

    Thank you.

    Pam

    Pamela Keffer
  • 02-04-2016 3:25 PM In reply to

    Re: SQL Override syntax to use Action Results, Shared Results, Screen fields in the condition

    Hello kefpa01,

    According to your requirement the code should look like this. In this code it is looking for value on the grid column and comparing it with screen.

    CASE WHEN viewActivity.DueDateEnd < ScreenValue THEN '<font color="red"><b>' + Entity + '</b></font>' 

    WHEN viewActivity.DueDateStart < ScreenValue THEN '<b>' + Entity + '</b>' 

    ELSE '<font color="black">' + Entity + '</font>' END)

    -Inder

  • 02-05-2016 11:03 AM In reply to

    Re: SQL Override syntax to use Action Results, Shared Results, Screen fields in the condition

    Answer

    Hello kefpa01,

    In addition to my previos comment, you can get the screen value for 8daysAway by clicking on Advanced on DueDateEnd

    anc click on SQL Override.

    The important thing on SqlOverride is Step 3 as we are more focused on getting the screen value for 8DaysAway field.

    In step 1, you can choose Activity table and in Step 2: choose any field.

    In step 3: Select DueDateEnd < Value from Screen select 8DaysAway.

    Then, go to step 4 and click on Generate SQL and it will generate the code and you can get the value of 8daysaway from the code.(See Screenshot).

    You can now use this value in your override.

  • 03-02-2016 8:07 PM In reply to

    Re: SQL Override syntax to use Action Results, Shared Results, Screen fields in the condition

    Thank you so much.  I did not realize you could generate SQL. I know it was staring me in the face but I hadn't tried it... Good tip.  

    In order to get that screen name, i have been executing an action GeField/ObjectID, and displaying in a field, and then going back to customization and updating my SQL code.  It did the trick, but I'm glad to know how to do it this way and can avoid the multi step customization here. 

    Thanks again. 

    Pamela Keffer
Page 1 of 1 (8 items)