Since starting with Tadabase 2 years ago I have become more adept, and our App has grown and now has many Zapier and Make tasks that link it to other systems. But this week I decided to try and link directly to our CMS system via the MySQL Pipe (NB: we use Joomla, but it could be any MySQL system like Wordpress).
At first I struggled, it would not work as I expected. But, being stuck at home with Covid I had time to keep trying - and eventually I think I understand how it works, what it does and doesnot do and how to extend the functionality the basic Pipe offers. This post is for anyone else who is having the same struggles - it may help.
What was I trying to do?
What I want to do is have a button in my Tadabase app, in a page about a specific course, that gets all the email addresses of the users who are on that course, from our CMS and bring them back into a concatenated field that I can use in a email. (I know I can do this in Zapier or Make - but want to try and more native approach)
I loaded the Pipe and connected to the database. That was easy.
In the MySQL Pipe there are fixed set of 9 Queries. One of these is called âGet Filtered Recordsâ and that seems reasonable - I want to get records of users filtered by the course. This particular Pipe has just 2 Parameters. The Table the data is in, and the âfilterâ. This second part has hard to grasp for me. I was expecting a MySQL type âselectâ query but the filter is much compressed version of that - it is in the manual page but I had to read and re-read to grasp what it was saying. AND what made the whole process much harder was that I was not getting any results.
Or so I thought.
You see the âfilterâ that I used in the end was âusergroups,cs,Registeredâ. This means select rows where the field âusergroupsâ âcontainsâ the term âRegisteredâ. You might recall my end game was a concatenated string of emails. This query doesnât address that at all. It brings back an Array of all rows that match the filter.
And this is a general point about the MySQL Pipe, most of the requests will return an Array⌠and I donât understand how that array can be used, its not ready for use in a Tadabase field.
I thought I was getting nothing from my request, but I was getting an Array and Tadabase didnât know what do do with it. This had me stumped. I found that I could change the Pipe/Response datatype to âcomma separated valuesâ but that just gave me the array of data separated by commas, but at least I could dump this into an Txt field in a Tadabase table.
I was closer to my goal, but I only wanted the email column from the rows, not everything else.
After too long (I am not a coder so apologies if this should have been obvious) I realised that the Request I was using was âŚ
âactionâ : â/records/{tableName}?filter={filter}â
The fields with {} curly braces are Parameters in the Pipe
For a similar Pipe called âGet Records with Specific Columsâ the Request is âŚ
âactionâ : â/records/{tableName}/{primaryKeys}?include={columns}â
This pipe gets data from specific columns.
I realised that I might be able to EDIT the Request of the Pipe I was using to include the column(s) I wanted as well.
And so thatâs what I did. Into the âGet Filtered Recordsâ pipe I added a Parameter for column. Added the correct reference for the email field into the new âcolumnâ parameter in the Action calling the Pipe. And edited the Request to âŚ
âactionâ : â/records/{tableName}?filter={filter}&include={column}â
AND lo and behold I get want I wanted. A single record returned, all the email addresses separated by commas !!
Embolded with this I went further.
You see I said I used this as my filter field âusergroups,cs,Registeredâ. But âRegisteredâ is only one of the usergroups on our CMS. I wanted the search to be dynamic, and pass a variable from Tadabase into the Request.
For this I created a new Parameter - usertype. The filter parameter became âusergroups,cs,â and this was made complete in the Request by editing it to be âŚ
âactionâ : "/records/{tableName}?filter={filter}{usertype}&include={column}"
In the Action calling the Pipe I connectd the parameter for usertype to the record/field in tadabase.
This is now a dynamic search, the usertype is variable and the result is the list if email addresses I want.
I hope this makes sense to someone else and helps.
OBSERVATIONS
- Takes more work that ZApier/Make, but once set up its faster to run
- Itâs a shame I canât create my own Requests in the Pipe (I have to hack one of the 9 that are there)
- Shame I canât use regular MySQL query format. Complex queries with joins are a possibility but look very complex.
- Not sure what to do with the Arrayâs that normally get selected - how can they be used (Databridge?)
- The Update/New Record Pipes donât seem to work - I get errors (reported)