Using Case Statement in Calculated Field

[CameronB]

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

[AtayBalunbalunan]

Can you try TUMF_ID and Status without the square brackets?

[Alison]

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

 

[MaraMara]

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

 

[Alison]

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

 

[MaraMara]

Alison, Thank you very much! 

[KG360]

On 6/22/2020 at 5: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

 

A question on the topic of how to refer to fields in the current datapage when using select statements: Should there ALWAYS be 'target.' before the field name? I have several statements where I removed 'target.' and it worked better. Qoute ' To refer the  fields from the report datapage you should use "target." before the field parameter, for example,  WHERE TUMF_ID=target.[@field.ProjectID]' from this thread.

Appreciate any guidance/KG

[kpcollier]

Target is usually used when you have more than 1 record on a datapage, such as a Tabular Report. If you're just using a form datapage, I don't think you need to use target, as there is only data available for one record. I could be wrong on this, but that is how I understand it.