Invalid formula: Subqueries are not allowed in this context. Only scalar expressions are allowed.

[BGrambo]

Hello Everyone,

I'm attempting to reference another tables to see if a company is on 'Credit Hold', but the query is throwing the error 'Invalid formula: Subqueries are not allowed in this context. Only scalar expressions are allowed.' each time I try to apply it. I am trying to apply this on the 'Table Design' with a formula field where it wont work. HOWEVER, it works just fine on the view details page. Any ideas?

SELECT CreditHold FROM CompanyCreditHolds where CompanyName = target.[@field:CompanyName]

The field CompanyName from the table CompanyCreditHolds  is unique, so I'm unsure why I am constantly getting this error. 

[sandy159]

Hi @BGrambo,

This error usually appears when the SELECT expression returns an array of values instead of single value. You may try to add the TOP 1 statement like this: 

SELECT TOP 1 CreditHold FROM CompanyCreditHolds where CompanyName = target.[@field:CompanyName]

Here is the related forum post as well: 

Feel free to update this thread if it is not the case and the error still exists.

[CoopperBackpack]

Hello @BGrambo,

As I understand, you receive this error in the table, in the Formula field.

Please note that SELECT statements are not allowed in the Formula field. Basically, Formula works within one record. 

You may want to check more information about the Formula field https://howto.caspio.com/tables-and-views/data-types/formula-fields/

In case you need to get and store the values from the other tables, you may use Tasks or Triggered Actions. 

https://howto.caspio.com/tables-and-views/triggered-actions/

https://howto.caspio.com/tasks/

[TellMeWhy]

13 hours ago, BGrambo said:

Hello Everyone,

I'm attempting to reference another tables to see if a company is on 'Credit Hold', but the query is throwing the error 'Invalid formula: Subqueries are not allowed in this context. Only scalar expressions are allowed.' each time I try to apply it. I am trying to apply this on the 'Table Design' with a formula field where it wont work. HOWEVER, it works just fine on the view details page. Any ideas?

SELECT CreditHold FROM CompanyCreditHolds where CompanyName = target.[@field:CompanyName]

The field CompanyName from the table CompanyCreditHolds  is unique, so I'm unsure why I am constantly getting this error. 

Adding to Cooper's answer, probably best way is to create a View https://howto.caspio.com/tables-and-views/what-are-views/views/ since you mentioned you just want to see. Another way is Reports DataPage Calculated Field, you can use SELECT statement there.