3

I have created a Zapier Zap to populate data from a SmartSheet to a MySQL database. I have it branching so if the row does not already exist in MySQL a new row is created. This part works fine. In my second branch, if the row already exists then the data in the row is updated with new data from the SmartSheet row. When existing data is replaced with new data the Zap works fine. E.g. for an example existing MySQL row: 

+--------+---------------+--------------------+
| row_id | email_comment | smartsheet_orig_id |
+--------+---------------+--------------------+
|    895 | easy          |  6876364645150921  |
+--------+---------------+--------------------+

In the SmartSheet if the user replaces the comment with another, the MySQL data is updated successfully, e.g:

+--------+---------------+--------------------+
| row_id | email_comment | smartsheet_orig_id |
+--------+---------------+--------------------+
|    895 |  difficult    |  6876364645150921  |
+--------+---------------+--------------------+

But, if the user has deleted the comment in SmartSheet and not replaced it with another, leaving the comment empty, the data is not removed from the corresponding MySQL record e.g:

+--------+---------------+--------------------+
| row_id | email_comment | smartsheet_orig_id |
+--------+---------------+--------------------+
|    895 |  difficult    |  6876364645150921  |
+--------+---------------+--------------------+

What I need the MySQL record to look like in this case would be: 

+--------+---------------+--------------------+
| row_id | email_comment | smartsheet_orig_id |
+--------+---------------+--------------------+
|    895 |               |  6876364645150921  |
+--------+---------------+--------------------+

After quite a lot of testing, and a conversation with Zapier support it appears the problem is that Null values are removed from the Zapier Code output step. So, the above case, this is a summary of what I'm expecting to happen:

Zapier Code step: email_comment = Null --> MySQL Update Row step: email_comment = Null

But at the output of the Code step my Null value for email_comment is stripped and so the MySQL Update Row Zap step interprets the record as not needing to be updated as there is no change and leaves the old value there.

I have tried, in my code, passing an empty string " " instead of a Null but I get the exact same result. The only way around I can see is to pass on some empirical character and then in the Update Row step replace that with a Null to store in the record but I can't see a way of doing that in Zapier.

I have searched Google and Here for others wrestling with this issue but have drawn a complete blank. The search strings I have been using are [Zapier] delete data, [Zapier] remove data and [Zapier] Null. None of the results of those searches seem to be dealing with the issue I am having.

This is the Python code I'm using to gather the inputs from SmartSheet:

#for a non existent input store an empty value
def gather_vals(inp):
    return input_data.get(inp, emptyInput)

def pull_inputs(inputs, vinputs):
    for key, value in zip(vinputs,inputs):
        v = gather_vals(value)
        d_inputs.update( {key:v})

x_vinputs = ['input_equipment', 'input_from', 'input_to', 'input_description', 'input_contractor', 'input_booked', 'input_confirmed', 'input_job_no', 'input_complete', 'input_est_val', 'input_inv_val', 'input_inv_no', 'input_book', 'input_update', 'input_comments_email']
x_inputs = ['equipment', 'from', 'to', 'description', 'contractor', 'booked', 'confirmed', 'job_no', 'complete', 'est_val', 'inv_val', 'inv_no', 'book', 'update', 'comments_email']

# Gather rest of inputs
emptyInput = None
d_inputs = {}
#gather pick-up/delivery date/time input data
pull_inputs(x_inputs, x_vinputs)
results.update(d_inputs)

return results

It appears that the code works, it returns no errors and when there is an updated actual value in SmartSheet it is updated in MySQL but when the comment is deleted the old value is left in MySQL.

I'm hoping someone may have a suggestion for me to follow.

This is the Zap flow:

Zap flow

Zapier support tells me the problem is that Nulls are being stripped off the output of the Python code step circled in red. The Nulls need to flow through to the Update Row step.

Manually entering NULL or Null or null in the Update Row step results in a string of characters being sent to MySQL. See the outpt from MySQL Workbench for that record:

enter image description here enter image description here enter image description here

Sending an empty string results in a string with quotation marks being sent to MySQL:

enter image description here

It appears this Zapier step will only send strings to MySQL so I guess it is a moot point that the code step strips NULLs from the output.

culix
  • 10,188
  • 6
  • 36
  • 52
Karl In OZ
  • 63
  • 8
  • Are you mapping data straight out of the code? Did support mention which step is removing the null value? Does it work if you type the word `null` in the mysql step or is that where the issue lies? – xavdid Jan 29 '19 at 23:27
  • Thanks for your response @xavdid. Support told me the issue is at the output of the Python code step circled red above - Zapier are removing the nulls there. Typing the word null in the MySQL step will not work because if there was an input at the Updated Row step at the start I want to write that input. Or are you suggesting to type null in the Update Row step just to test? – Karl In OZ Jan 30 '19 at 04:24
  • As far as mapping data straight out of the code goes, I am reading the returned values from the code step straight into the Update Row step – Karl In OZ Jan 30 '19 at 06:32
  • correct, type `null` in the `update row` just to test it works and then we can think about how to get it there. – xavdid Jan 30 '19 at 15:30
  • Ok so I tried `NULL`, `Null` and `null`. All resulted in strings being sent to MySQL, as did entering `""` so it looks like we can only send a string from this step. I updated my question with these results. – Karl In OZ Jan 30 '19 at 22:20
  • I'm wondering now whether there are two options left for me: 1. Sending a specific string and triggering a procedure in MySQL to replace the string with a NULL or 2. Using code in Zapier to write to MySQL. I have shyed away from 2. because I don't know how to do that safely without putting my DB credentials into the code - i.e. accessing the account credentials stored in Zapier the way the MySQL steps do. I would need to do a bit of research to see how to do 1 but maybe it's simple. – Karl In OZ Jan 30 '19 at 22:28
  • LOL I may have found the solution in an answer I gave some moons ago here: [https://stackoverflow.com/questions/46297752/combine-insert-and-select-in-one-sql-query-zapier]. @xavdid do you think a MySQL custom query would be the best solution in this case? – Karl In OZ Jan 30 '19 at 23:09
  • haha that's awesome. I don't know a ton about mysql, but a custom query might be the best bet. there's not a way to have secret data safe in code steps (unfortunately). – xavdid Jan 31 '19 at 14:37
  • Hi @xavdid, looks like I need a little more help, maybe from someone who knows SQL better. In my Custom Query step I tried: `UPDATE logistics.transport SET week = '27' WHERE ss_original_row_id = '1458074898720644'` which of course returns a 500 error because it returns nothing. From Zapier: _We made a request to our server and received (500) Internal Server Error._ – Karl In OZ Feb 06 '19 at 00:43
  • So I tried enclosing my UPDATE query inside a SELECT IFNULL() statement, which works nicely for a SELECT query: `SELECT IFNULL( (UPDATE logistics.transport SET week = '27' WHERE ss_original_row_id = '1458074898720644') ,0) AS result;` however that fails because it is not a valid SQL statement. Testing it in MySQL Workbench confirms that for me. So, it looks like I need to enclose my UPDATE query inside some other query than SELECT IFNULL(). Can you or someone suggest a query I could use? – Karl In OZ Feb 06 '19 at 00:43
  • At this point, i'd probably create a new SO question targeting SQL specifically. That's likely where you'll find the best help – xavdid Feb 06 '19 at 02:30
  • Hmmmm this is worrying: [https://stackoverflow.com/questions/53352315/select-statement-nested-query] The responder says an SQL INSERT is not possible to be nested inside a SELECT, I guess the same would be for an UPDATE. I'm beginning to think I'll not to be able to do it with a custom query either. – Karl In OZ Feb 06 '19 at 03:13
  • Well after quite a few searches it seems not possible to get an UDATE query to post an output. See these results: [https://stackoverflow.com/questions/22867006/nested-update-in-select] **Nested update in select** _you can use select as subquery in update query, but in select query you can't do update subquery_ [https://stackoverflow.com/questions/45854653/mysql-select-after-insert-ignore] **MySQL: SELECT after INSERT IGNORE** _You cannot return a select result for an update or insert query, that's obvious._ ... – Karl In OZ Feb 06 '19 at 05:47
  • [https://stackoverflow.com/questions/25054844/mysql-return-results-from-update] **mysql return results from update** _An UPDATE and a SELECT are two different things. They have to be two separate queries._ _UPDATE updates, SELECT selects. You can't do both at once._ _In MySQL you have to use two statements to select and update, and not a nested statement _ [https://stackoverflow.com/questions/37668401/php-mysql-pdo-get-result-from-update-query] **PHP, MySQL, PDO - Get result from UPDATE query?** _Only mysqli has a method for doing multiple queries in a single call,_ ... – Karl In OZ Feb 06 '19 at 05:47
  • So, @xavdid it appears we can't do an update using the custom query. Do you have any other ideas on how a row in MySQL can be updated with cells including nulls? – Karl In OZ Feb 06 '19 at 05:49
  • I personally do not - i've never gotten much into SQL minutia or functions outside of simple `SELECT * WHERE`. :/ – xavdid Feb 06 '19 at 17:54
  • Ok so it looks like it is impossible for zapier to do an update on an entire row if the update requires updating a value to a null. That's a real shame. Can I submit a feature request for this? – Karl In OZ Feb 06 '19 at 19:56
  • you certainly can! the fine folks at `contact@zapier.com` can sort you out. – xavdid Feb 06 '19 at 20:26

0 Answers0