Jump to content
  • 0
CameronB

Using Case Statement in Calculated Field

Question

I keep getting an error when running the report that has a calculated fieldwith a sql case statement.  I'm trying to pull a value from another table based on the current record on the datapage.  I'm using the following:

 

 Each record has a field called "Phase" with the values in the 'when' clause.  Trying to pull from a table of agreements that has the amount of funding for each of these phases based on a project ID.

What am I doing wrong?  The Verify button says it's all good, but when I preview the report it says there's an error.

CASE

WHEN [@field:Phase]="CON" 

THEN

(SELECT ConAmount FROM Reimb_Agreements WHERE [TUMF_ID]=[@field.ProjectID] AND [Status]='Active')

WHEN [@field:Phase]="ROW" 

THEN

(SELECT ROWAmount FROM Reimb_Agreements WHERE [TUMF_ID]=[@field.ProjectID] AND [Status]='Active') 

WHEN [@field:Phase]="PSE" 

THEN

(SELECT PSEAmount FROM Reimb_Agreements WHERE [TUMF_ID]=[@field.ProjectID] AND [Status]='Active')

WHEN [@field:Phase]="PAED" 

THEN

(SELECT PAEDAmount FROM Reimb_Agreements WHERE [TUMF_ID]=[@field.ProjectID] AND [Status]='Active') 

Else 'No Agreement'

END

Share this post


Link to post
Share on other sites

5 answers to this question

Recommended Posts

  • 0

Hi @CameronB,

There are some errors in the formula:

1) Fields from the table in the Select statement should be without any brackets, for example,  WHERE TUMF_ID=[@field.ProjectID]

2) To refer the  fields from the report datapage you should use "target." before the field parameter, for example,  WHERE TUMF_ID=target.[@field.ProjectID]
3) The  result of the  When and Else statements in the Calculated field should be the same data type, for example, if ConAmount,  ROWAmount, ...  fields have integer data type, then in the Else statement you should use integer values

 

Share this post


Link to post
Share on other sites
  • 0
On 6/22/2020 at 12:06 PM, Alison said:

Hi @CameronB,

There are some errors in the formula:

1) Fields from the table in the Select statement should be without any brackets, for example,  WHERE TUMF_ID=[@field.ProjectID]

2) To refer the  fields from the report datapage you should use "target." before the field parameter, for example,  WHERE TUMF_ID=target.[@field.ProjectID]
3) The  result of the  When and Else statements in the Calculated field should be the same data type, for example, if ConAmount,  ROWAmount, ...  fields have integer data type, then in the Else statement you should use integer values

 

Hello Alison, I hope it is ok to reply and leave a question here, this is my first time in a forum... 

 

In a Submission form dapage, In a calculated field, I want that acording to what the user selects on  field:HRCOMF my other field:HRSITI  receives a value. (In the form, therefore the table) 

if  field:HRCOMF equals 70 then i want field:HRSITI filled with a '7'

if it has any other value different from 70, then I want it filled with a value found  in another table according to what other field has in another table. 

All fields are numbers. 

 

I don't know if it possible, i tried to figure it out but i don't have an education on this, 

 

Fields starting with HR are from the form, the one starting with CL are from the reference table.

I've tried these two and they verify as valid, but return blank values in the form and therefore the table when saved:

1)

CASE

WHEN [@HRCOMF]='70' = THEN HRSITI='7'

ELSE

THEN SELECT CLCIVA FROM CLIENTES WHERE CLCODI=target.[@field:HRCLIE] END

 

2) 

CASE

WHEN [@HRCOMF]='70' = THEN HRSITI='7' 
WHEN [@HRCOMF]<>70' THEN SELECT CLCIVA FROM CLIENTES WHERE CLCODI=target.[@field:HRCLIE] END

 

 

 

I really hope you can help, thanks in advance

 

Share this post


Link to post
Share on other sites
  • 0

Hi @maramedinan,

To receive the value in the field:HRSITI, you should make it Calculated value form element and add the following formula there:
 

CASE
 WHEN [@field:HRCOMF] = 70 
 THEN 7 
 ELSE
 (SELECT CLCIVA FROM CLIENTES WHERE CLCODI=[@field:HRCLIE])
END

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...