Hi Heather,
You have to pass parameters into your custom sql query – you can’t use the dmx {{}} type of syntax directly in your query.
You add a parameter and give it a name, like :P1 and then you choose your dynamic data by setting an expression, which has the data binding options. You then type :P1 wherever you want that value inserted into your query.
Thanks! That makes sense, but I’m getting “no results returned” when I execute the query. I know that tripbit_id 14 (as given in the json above) is in the tripbits table. I’m guessing I still have some syntax wrong?
Query = SELECT * FROM tbl_tripbits WHERE tripbit_id IN (:P1)
:P1 = {{api1.data.resources.context.custom.groupBy(‘tripbit_id’).keys() }}
Heather
I don’t know how to get you to a list of tripbit_ids with just the formatters (maybe somebody else can chime in with that).
But this works for me:
Repeat through the results from your api, and set a temporary value within the repeat. After the repeat, create the final list by using groupBy and keys:
In the custom query, use FIND_IN_SET instead of IN, and join the final list with a comma:
We do have a whole bunch of server actions for collections manipulations and transformations coming up 
Just have to find time to include them, with all those new things each week
Wow! Thanks for creating all the steps for me to follow. Unfortunately though, it’s still not working.
Here are the screen shots for mine…
If I remove the Where clause I get the full tbl_tripbits table so that bit is working correctly.
Hmmm.
You guys do an amazing job getting new stuff out every week! Of course I’d love this to be included soon, but understand you have priorities.
You’ll have to give us a little more to go on…
Have you tried entering a test value?
Are you getting an error?
What does the value final_list evaluate to?
If I enter in a test value of 14 it runs properly.
I’m not getting an error when running the query, just No results returned
If I run the file I get the following…
"code": 0,
"file": "/home/tripmakers/public_html/dmxConnectLib/lib/formatters/collections.php",
"line": 127,
"message": "Undefined index: idvalue",
"trace": "#0 /home/tripmakers/public_html/dmxConnectLib/lib/formatters/collections.php(127): exception_error_handler(8, 'Undefined index...', '/home/tripmaker...', 127, Array)\n#1 [internal function]: lib\\core\\formatter_groupBy(Array, 'idvalue')\n#2 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(410): call_user_func_array('\\\\lib\\\\core\\\\forma...', Array)\n#3 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(364): lib\\core\\Parser->objectMember(Object(Closure))\n#4 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(306): lib\\core\\Parser->primary()\n#5 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(283): lib\\core\\Parser->group()\n#6 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(263): lib\\core\\Parser->unary()\n#7 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(253): lib\\core\\Parser->multiplicative()\n#8 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(243): lib\\core\\Parser->addictive()\n#9 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(233): lib\\core\\Parser->bitwiseShift()\n#10 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(223): lib\\core\\Parser->relational()\n#11 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(213): lib\\core\\Parser->equality()\n#12 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(203): lib\\core\\Parser->bitwiseAnd()\n#13 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(193): lib\\core\\Parser->bitwiseXor()\n#14 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(183): lib\\core\\Parser->bitwiseOr()\n#15 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(171): lib\\core\\Parser->logicalAnd()\n#16 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(155): lib\\core\\Parser->logicalOr()\n#17 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(151): lib\\core\\Parser->conditional()\n#18 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(96): lib\\core\\Parser->expression()\n#19 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(221): lib\\core\\Parser->parse('tripbit_ids.gro...', NULL)\n#20 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(206): lib\\App->parseObject('{{tripbit_ids.g...', NULL)\n#21 /home/tripmakers/public_html/dmxConnectLib/modules/core.php(99): lib\\App->parseObject(Object(stdClass))\n#22 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(173): modules\\core->setvalue(Object(stdClass), 'final_list')\n#23 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(137): lib\\App->execSteps(Object(stdClass))\n#24 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(107): lib\\App->execSteps(Array)\n#25 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(72): lib\\App->exec(Object(stdClass))\n#26 /home/tripmakers/public_html/dmxConnect/api/Other/tt_tripbit_images.php(8): lib\\App->define(Object(stdClass))\n#27 {main}"Can you post the entire json received from the api?
{
“resources”: [
{
“public_id”: “Tripmakers/51/Ashley_taking_Video_in_Main_Square_ak5a4d”,
“version”: 1603568045,
“format”: “jpg”,
“width”: 4320,
“height”: 2432,
“type”: “upload”,
“created_at”: “2020-10-24T19:34:05Z”,
“context”: {
“custom”: {
“tripbit_id”: “14”
}
}
},
{
“public_id”: “Tripmakers/51/Ashley_in_Charles_Bridge_Tower_with_Prague_Castle_s7jxg5”,
“version”: 1603568042,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:34:02Z”,
“context”: {
“custom”: {
“tripbit_id”: “1446”
}
}
},
{
“public_id”: “Tripmakers/51/Ashley_listening_to_Audio_Guide_dh3ayr”,
“version”: 1603568041,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:34:01Z”,
“context”: {
“custom”: {
“tripbit_id”: “1445”
}
}
},
{
“public_id”: “Tripmakers/51/546_dfcsmn”,
“version”: 1603568038,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:58Z”,
“context”: {
“custom”: {
“tripbit_id”: “1458”
}
}
},
{
“public_id”: “Tripmakers/51/551_mirv64”,
“version”: 1603568038,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:58Z”,
“context”: {
“custom”: {
“tripbit_id”: “1499”
}
}
},
{
“public_id”: “Tripmakers/51/552_jybedj”,
“version”: 1603568034,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:54Z”,
“context”: {
“custom”: {
“tripbit_id”: “1451”
}
}
},
{
“public_id”: “Tripmakers/51/545_cuifql”,
“version”: 1603568031,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:51Z”,
“context”: {
“custom”: {
“trip_id”: “300”
}
}
},
{
“public_id”: “Tripmakers/51/543_lx2sgi”,
“version”: 1603568029,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:49Z”
},
{
“public_id”: “Tripmakers/51/540_c4k5to”,
“version”: 1603568029,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:49Z”,
“context”: {
“custom”: {
“tripbit_id”: “1448”
}
}
},
{
“public_id”: “Tripmakers/51/537_adbxoq”,
“version”: 1603568028,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:48Z”,
“context”: {
“custom”: {
“tripbit_id”: “1458”
}
}
},
{
“public_id”: “Tripmakers/51/538_nzgxez”,
“version”: 1603568028,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:48Z”,
“context”: {
“custom”: {
“tripbit_id”: “1458”
}
}
},
{
“public_id”: “Tripmakers/51/081_y08hcc”,
“version”: 1603568019,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:39Z”
},
{
“public_id”: “Tripmakers/51/528_ucuq0m”,
“version”: 1603567999,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:19Z”
},
{
“public_id”: “Tripmakers/51/521_guin7u”,
“version”: 1603567998,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:18Z”
},
{
“public_id”: “Tripmakers/51/085_rrv4zi”,
“version”: 1603567995,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:15Z”
},
{
“public_id”: “Tripmakers/51/526_a2wvs8”,
“version”: 1603567995,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:15Z”
},
{
“public_id”: “Tripmakers/51/525_fcav74”,
“version”: 1603567994,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:14Z”
}
],
“updated_at”: “2020-10-26T20:33:24Z”
}
Okay, this works for me.
Try ticking the output box for the Set Value action within the repeat. If I remove the output, then I receive the same error.
AHA!!! That worked to get the final_list.
However I’m still getting No Results when I run the query.
I have mine setup to use column type Numbers…maybe your tripbit_id is a string field? If so, then remove the toNumber() in the Set Value idvalue.
Just to be clear…you mean when you hit the play button within the query, correct? If so, then that will be using the test value, which you show as empty.
Running the file, is the only way to see the expression in use.
Well, duh! Of course.
It’s working perfectly now. Thank you so much for your help. I really appreciate it!
Hey mebeingken, do you know if it’s possible to pass parameters to the query to be used for inserting dynamic values?
E.g. I’m using an INSERT custom query, and I have some JSON i’m including directly in the query (this inputs correctly with static data) - I’d like to replace some of the values in the static JSON with dynamic data using :P1 + a dynamic expression. Do you know if this is possible?
That’s already possible using the Server Connect Array List component:
This topic was automatically closed after 26 hours. New replies are no longer allowed.
