Jump to content
  • 0

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


BGrambo
 Share

Question

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. 

image.png.4ee67f63cfabbc62c7fa647fb6d11985.png

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

  • 0

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/

dyBt9nq.png

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/

Link to comment
Share on other sites

  • 0
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. 

image.png.4ee67f63cfabbc62c7fa647fb6d11985.png

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.

Link to comment
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...
 Share

×
×
  • Create New...