Method:Classic General Q&A

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

kefpa01 — Thu, 03 Mar 2016 02:07:05 GMT

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.

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

Method_Inderdeep — Fri, 05 Feb 2016 17:03:18 GMT

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.

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

Method_Inderdeep — Thu, 04 Feb 2016 21:25:57 GMT

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 '' + Entity + ''

WHEN viewActivity.DueDateStart < ScreenValue THEN '' + Entity + ''

ELSE '' + Entity + '' END)

-Inder

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

kefpa01 — Thu, 04 Feb 2016 19:21:32 GMT

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

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

Method_Inderdeep — Thu, 04 Feb 2016 19:10:43 GMT

Hello kefpa01,

You syntax should look something like this:

(CASE WHEN [DueDateEnd] < viewActivity.8DaysAway THEN '' + Entity + ''
WHEN [DueDateStart] < viewActivity.8DaysAway THEN '' + Entity + ''
ELSE '' + Entity + '' END)

I hope that helps!

-Inder

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

kefpa01 — Thu, 04 Feb 2016 18:28:15 GMT

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

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

Method_Inderdeep — Tue, 02 Feb 2016 22:27:15 GMT

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

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

kefpa01 — Mon, 01 Feb 2016 15:45:47 GMT

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 '' + Entity + ''
WHEN [DueDateStart] < GETUTCDATE() THEN '' + Entity + ''
ELSE '' + Entity + '' END)

Thanks for your help,

Pam