Start a process based on sql table.

0

Hi,

I would create this service workflow with these steps:
- Initialization, save draft to start the instance, continue to read the changes.
- Changes read
- Create workflows
- Completed

The first three steps have a Timeout/Timer which triggers after 1 minute to move the workflow to the next step.


Depending on the number of workflows it maybe necessary to create a loop
Create workflows -> Start workflows -> All workflows started -> Yes -> Completed
Create workflows -> Start workflows -> All workflows started -> No -> Create workflows

The path to read the changes would get the difference and store those changes in an item list. This item list will then be used to start the workflows.
This way you can test the process manually and have a transparent overview, where something goes wrong.

The question is how to identify the differences.
- Is there a timestamp in the table?
- Are only new rows added`?

Depending on it, you could check which was the last record in the previous workflow instance and add only the new ones in the current one.

Best regards,
Daniel

Hi,

I need to update a source tables in SQL, I need to insert an "instanceNumber" "status" and a "url" a direct link, that WebCon gives to each process started.
Each row in the source sql table should start a single process in WebCon.
This sql table will be a place where erp system will be inserting the data and also reading the status and grabbing a document number and url.

Best regards
Tomasz

Hi,
I am not sure if i understood right.
The first idea:
You need the action start subworkflow sql. Each row return from sql from the action will start an instance in the process. In the process you can create an action for to send the instance number/instance id in your sistem. You will update the table with instance number like ("update TableSql set columnId='current instance number' where uniqValue='current uniq value from instance' )
The steps for to create this process are:
1. Create de process and global timeout action with start subworflow sql
2. Config the action start subworkflow sql with : select columns from yourTable left join WfElements on yourTable.uniqValue=WfElements.uniqValue where yourTable.uniqValue is null.
3. Create sql for identify if exista at less one record for insert and put the proporties action in the tab execute condition.
4. In the process on the save path create an action for update your table cu instnace id.

Thanks
Raluca

Hi,

Second idea:
You can create a trigger on the your table and when it is inserrt a row call
1.Create a queue table for waiting
2.Create a Trigger that inserts data into the table
3.Create a SQL Agent Job that periodically runs a PowerShell script
4. In the process on the save path create an action for update your table with instance id.

Thanks
Raluca

Hi,

Thank you for the suggestion. I face one more problem. How to convert a base64 format to be visible as a attachment in WebCon?
I found only "CONTENT AS BASE64" :/ I need a reverse function :)

Best Regards

Hi,

Thank you for the suggestion. I face one more problem. How to convert a base64 format to be visible as a attachment in WebCon?
I found only "CONTENT AS BASE64" :/ I need a reverse function :)

Best Regards

Try this:

Set the "Files choice mode" in the "Add attachment" action configuration to "Based on SQL query".

Paste the following query into the "SQL for choosing source file" field:

SELECT
CAST(N'' AS XML).value('xs:base64Binary("{SNT:1196}")', 'VARBINARY(MAX)') AS Content,
'file1.pdf' AS FileName,
'New document' AS Description


{SNT:1196} → Base64 or Field with Base64
FileName → File name
Description → File description

Set "Source files" to "Copy attachments".

Best regards,
Smerfetka😊

Thank you :) it works :)

Best Regards
Tomasz

Hi,
I am not sure if i understood right.
The first idea:
You need the action start subworkflow sql. Each row return from sql from the action will start an instance in the process. In the process you can create an action for to send the instance number/instance id in your sistem. You will update the table with instance number like ("update TableSql set columnId='current instance number' where uniqValue='current uniq value from instance' )
The steps for to create this process are:
1. Create de process and global timeout action with start subworflow sql
2. Config the action start subworkflow sql with : select columns from yourTable left join WfElements on yourTable.uniqValue=WfElements.uniqValue where yourTable.uniqValue is null.
3. Create sql for identify if exista at less one record for insert and put the proporties action in the tab execute condition.
4. In the process on the save path create an action for update your table cu instnace id.

Thanks
Raluca

Hi Raluca,

Are you able to give me an example of how to use Start a subworkflow (SQL) to start many new workflow instances?
I have a query that returns two rows, but how to start a separate workflow instance, how to move data from subworkflow (SQL) to new workflow instances?

Best regards,