Hi Rennette
Did this long ago in Foundation, and I didn't have that example anymore.
Use the QUERY control in the form. Doc: https://help.precisely.com/r/Automate-Evolve/20.3/en-US/Automate-Evolve-User-Guide/References/Query-Element-Properties
I tried some plugins, but it didn't do what I wanted, which was simply update rows.
My table was simple for this test, with Email an index and cannot be blank - and which I'll use in my SQL WHERE clause to update the records
One Query control to read from the table - use whatever you want in the select statement - I read everything in
set the column values based on the fields in the Table fields in the query control
Then the tricky part was getting the syntax just right to update the rows that Evolve would be happy with
no need to set fields in this query control
notice the set quoted_identifier off in front of the Update statement, so that you can use the single quotes in the SQL statement, just like you would enter in SSMS. If you are using quotes in your select statement, I think you'd need it there, too.
The other tricky part, it didn't like if I used a sort field in the data connection to the SQL db table. If I had sort by Email ascending, it actually added that to the end of my update statement, which I didn't want. So you can just use sorting in the repeating table, if that's important.
My simple test:
The attached example is a single view, no workflow, and it only updates rows. Email is the key in the table and read only, but the rest of the fields are editable and can be null. Note I had to zip it up to attach it here, so unzip and import into Evolve if you want to look at it. It was built in version 20.3.1. Otherwise, you can leverage the screen shots.
Hope this gives you enough of an idea of what is possible!
Best Regards,
Sigrid
------------------------------
Sigrid Kok
Precisely Software Inc.
------------------------------
Original Message:
Sent: 02-14-2024 09:45
From: Renette Nienaber
Subject: Update SQL table / records via Evolve form / workflow.
Hi! Is it possible to update (change, add, delete) a SQL table / records via an Evolve form / workflow?
until now I have only made use of a SQL Data Connection for reference list purposes.
I would like to create a form with approval workflow that update the values within the SQL table that is then used in other solutions as reference data / drop down lists.
Thank you.
Regards,
Renette
------------------------------
Renette Nienaber
Data Management specialist
VIVO ENERGY SOUTH AFRICA (PTY) LTD
------------------------------