Custom Query - Delete Row from Table

asked on April 6, 2020

I have a LF Form that displays a table with values from a lookup. I have the Workflow Insert function working to add new rows and the Update works when a row is edited. I have a Conditional Decision inside a For Each Row.

I need the give the user the ability to delete random rows from the LF Forms table and have the corresponding row in the SQL table deleted when the Workflow is called.

I have a Custom Query started which works when I Test and obviously deletes any values not equal to the specified test value.

DELETE
FROM Table_Name
WHERE UserID != @id

How can I delete a SQL table row (UserID) when the corresponding row (id) on the Form is no longer present?

I'd appreciate any assistance.

0 0

SELECTED ANSWER

replied on April 6, 2020

Here are three options:

Add a hidden ID column, and then allow the user to remove rows in the Forms table, and deleting any from SQL whose ID no longer exists in the table.
Add the hidden ID column and additionally a keep/delete dropdown column, and any that are marked as 'delete' you can then delete in SQL.
Delete all entries from the table in SQL and re-add those that exist in the Forms table. You no longer would need to update each row since this will be handled in the inserts, but this is not an option when this table is used as a foreign key in SQL.

1 0

replied on April 6, 2020 • Show version history

Thanks, Jim. Much appreciated. Options 1 and 3 are preferred. I'd like to try to get option 1 working before resorting to option 3, or option 2, if all else fails.

I already have the ID field hidden. Added rows on the form don't have an ID until INSERT. As stated previously, I have a Conditional Decision within a For Each Row. Would the Custom Query Delete be another decision branch or should it be ran after any INSERTS and/or UPDATES?

Can you assist in the syntax for the Custom Query delete?

0 0

replied on April 6, 2020

Try this: before the For Each that updates it, add a Query Data to get all the (old) IDs. Then, after the For Each and inside another For Each Row for those results, check if the current iteration's ID is in the list of those from the form. (How you want to do this, good luck.) If it isn't, it was deleted.

For the custom query to delete, replace "Table" and "ID" with your appropriate names, but leave the "?":

DELETE FROM [Table]
	WHERE [ID] = ?

Then you will need to add a variable in the parameters of the custom query as so, replacing only the "%(ID)" with your variable:

0 0

replied on April 8, 2020

I couldn't use ? as a parameter name. Either way, I ended up using the Keep/Delete dropdown and all is OK.

Thanks again for the assistance, Jim.

0 0

Question

Question

Custom Query - Delete Row from Table

Answer

Replies

Sign in to reply to this post.