Trigger Action error 2116 - Trying to pass more than one value to a field

[Melwyn]

Dear Community,

I have an issue with the Trigger Actions feature:

I have one Text field, that I want to populate with the result of a query.

 

 

 

On image_1, you can see that I'm trying to say:

invoice_leader_number = "result of a query"

(Just to let you know, the "invoice_leader_number" is a TEXT).

 

Now you can see that this does not work because "2116: Only one value is expected" (cf. image_2).

 

I've tried a work around by trying to use other functions to kind of transform the result of my query into a "one value" result, but none of the functions worked and they all expect only one value.

I suppose that Caspio can't be fooled so easily

 

So my question is:

Is it possible to grab the result of a query (which is in my case just a list of Strings), concatenate them into a single string (separated with commas) and pass the concatenated result to a field?

 

If not, what other solution does Caspio provide?

 

 

Many thanks

[Melwyn]

I've found an SQL query similar to what I'd like to accomplish (original post):

SELECT Column1, group_concat(Column2) FROM Table GROUP BY Column1

 

Do you think I can do this in Caspio?

 

Thanks

[Vitalikssssss]

Hi Melwyn,

I have a doubt that desired functionality can be achieved with Triggered Actions feature.

I have a Trigger in my App which concatenates first and last reference number into a single Text(255) field.

You may try to achieve desired functionality on a Datapage level by using the Javascript solution from this Forum topic.

It will allow you to store selected values in one field which will be separated by a comma. 

 

Hope this helps.

 

[Melwyn]

Hi @Vitalikssssss,

Thank you so much for your message!

Unfortunately in your trigger, with the concatenate function, you only retrieve one value that you concatenate to another one value.

If you remove the TOP statement, it will fail.

 

Thanks for the JS idea.
I'm trying to insert in another table the concatenated result.
Which means, with the JS solution, I would have to:
- create a data page with a form
- this form would have received the concatenated string from previous data page
- autosubmit the form

 

This is a lot of work

And more seriously, what if something breaks in between?

The trigger action would have been a great solution if only I could concatenate the result of a SELECT query...

 

Thanks