Method Community

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

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

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

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

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

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

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.

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.