Filtering Criteria using Calculated Field

[QueenB]

In my Reports DataPage, I have a calculated field which determines if the record is Active or Inactive. I want to use this as one of my criteria in the search form. How can I do so?

[cheonsa]

Hi @QueenB,

 

You cannot use a calculated field as your filtering criteria. Why not try to add another field on your table and use Formula field instead. 

Hope this helps.

 

Regards,

kristina

[IamBlossom]

Hi @kristina,

 

I got the same issue. Adding another field to the table and use formula will do. But, in formula field, Select function is not allowed.  Is there another workflow for this?

 

Thanks. 

 

 

[NailDyanC]

Hi @IamBlossom ,

Regarding with your issue, may I know what is your formula so that I can test it on my end? But if you are using a Select Statement unfortunately select function is not allowed in a formula field. However, maybe we can use triggers to achieve your desired result.

 

Thanks.

[IamBlossom]

Hi @NailDyanC,

 

Thank you for your response. 

I have two tables, Student_Record and Student_Info. Both tables are linked together. Student_Record  contains Payment field. 

Per Student_ID, I want to sum all the payments that particular student made. If greater than 0, then the Status should be Active, else Inactive. 

Here is my formula:

CASE 
WHEN
(SELECT SUM(Payment) FROM Student_Record WHERE Student_ID = target.[@field:Student_ID] > 0 THEN
'Active'
ELSE
'Inactive'
END 

 

I have this in my calculated field. I want to use Status as my filtering criteria but it seems that it is not allowed.

 

Thanks.

[Aether]

Hi @IamBlossom,

I think using a select statement in the Formula field at the table is not possible because it's pre-define. 

What I suggest is you just add a field in the first table that you can use to store the data that your going to get in the second table using a trigger action, you can check the process in this video tutorial:

And then you can use the data that is transfered in your Formula field.

I hope this helps

~WatashiwaJin~