Automate

Hello Experts!

We need to get several fields from SQL view as part of workflow, and doing that via Set Form Data plugin and $$GetValueFromDataConnection.

Unfortunately ,it executes same SQL query several times for each field, that is pretty slow, because the view is complicated.

Question: can we get data in json or xml format once and then parse it into several solution or form fields - or even reference it in workflow as an object?

------------------------------
Alexey Skotnikov
GPO, Data Governance
ABBOTT RAPID DX INTERNATIONAL LIMITED
------------------------------

Hi Alexey

In order to try to help you, can you please share a bit more:

• What's the use case, i.e. what you're trying to do
• When you're trying to do it, i.e. what triggers the query?
• Does it have to happen in the background? Can it be done in the foreground via a Query control?
• What is the query? 
• After a quick search, it looks like you can call a SQL stored procedure as an API, but I have not done this before - here's a link: https://dev.to/zuplo/how-to-convert-a-sql-query-to-an-api-request-e8l  and another link re OData APIs with SQL https://learn.microsoft.com/en-us/sql/integration-services/data-flow/tutorial-using-the-odata-source?view=sql-server-ver16

If you feel comfortable, please share the solution or a partial solution.  Otherwise, the above may help.

Thanks,

Sigrid

------------------------------
Sigrid Kok
*Precisely Software Inc.
------------------------------

Original Message:
Sent: 02-11-2025 09:22
From: Alexey Skotnikov
Subject: Json/XML transformation in workflow

Hello Experts!

We need to get several fields from SQL view as part of workflow, and doing that via Set Form Data plugin and $$GetValueFromDataConnection.

Unfortunately ,it executes same SQL query several times for each field, that is pretty slow, because the view is complicated.

Question: can we get data in json or xml format once and then parse it into several solution or form fields - or even reference it in workflow as an object?

------------------------------
Alexey Skotnikov
GPO, Data Governance
ABBOTT RAPID DX INTERNATIONAL LIMITED
------------------------------

Hi Sigrid,

Our use case is the following. We have a table with material fields (e.g. plant data - MARC). Each field may have different department responsible for it (e.g. MRP data is Planning, purchasing data is Purchasing, Finance, etc.). Also, each plant belongs to different organization and data owners/approvers for MRP data in US are different from ones in China.

We have a couple of references that can evaluate based on OrgUnit (plant) and data element being changed the approver group (one or several), and those are used in workflow. In some cases - for some plants and data elements - values are standard ("locked") and can't be changed. In this case we want no human interaction for request, and it should spawn from parent and get posted completely automatically. For that reason the logic is put into workflow in SQL view, that reads form XML, applies logic from reference lists to determine approver groups and also calculates some reporting fields (like list of materials, plants, etc.).

It works pretty well via Set Form Data plugin that essentially calls SQL view and pulls data - except performance. Because $$GetValueFromDataConnection can only return one value from one column. When request is big (e.g. >500 rows in a table), it takes minutes to run this query, and because we have to run it many times for each column, it increases dramatically.

We could structure a view in a way that it returns all data in a single column in JSON or XML, but then we have no means to parse it into different fields in the workflow. And that is my question - how to do that.

Hope that helps.

------------------------------
Alexey Skotnikov
GPO, Data Governance
ABBOTT RAPID DX INTERNATIONAL LIMITED
------------------------------

Original Message:
Sent: 02-11-2025 09:55
From: Sigrid Kok
Subject: Json/XML transformation in workflow

Hi Alexey

In order to try to help you, can you please share a bit more:

• What's the use case, i.e. what you're trying to do
• When you're trying to do it, i.e. what triggers the query?
• Does it have to happen in the background? Can it be done in the foreground via a Query control?
• What is the query? 
• After a quick search, it looks like you can call a SQL stored procedure as an API, but I have not done this before - here's a link: https://dev.to/zuplo/how-to-convert-a-sql-query-to-an-api-request-e8l  and another link re OData APIs with SQL https://learn.microsoft.com/en-us/sql/integration-services/data-flow/tutorial-using-the-odata-source?view=sql-server-ver16

If you feel comfortable, please share the solution or a partial solution.  Otherwise, the above may help.

Thanks,

Sigrid

------------------------------
Sigrid Kok
*Precisely Software Inc.

Original Message:
Sent: 02-11-2025 09:22
From: Alexey Skotnikov
Subject: Json/XML transformation in workflow

Hello Experts!

We need to get several fields from SQL view as part of workflow, and doing that via Set Form Data plugin and $$GetValueFromDataConnection.

Unfortunately ,it executes same SQL query several times for each field, that is pretty slow, because the view is complicated.

Question: can we get data in json or xml format once and then parse it into several solution or form fields - or even reference it in workflow as an object?

------------------------------
Alexey Skotnikov
GPO, Data Governance
ABBOTT RAPID DX INTERNATIONAL LIMITED
------------------------------

Hi Alexey,

You can try creating a custom plugn.
First fetch the json value in a textarea on the form using 'set Form Data' Plugin.

Now the custom plugin would parse through this json and set the form fields.

consider json as : {"field_1dtstr":"test1","field_2dtstr":"teest2"}

//code starts here 

        // XML string as provided
        string xmlString = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><?mso-infoPathSolution name=\"urn:schemas-microsoft-com:office:infopath:Form1:-myXSD\" href=\"manifest.xsf\" solutionVersion=\"1.0.0.4\" productVersion=\"14.0.0\" PIVersion=\"1.0.0.0\" ?><?mso-application progid=\"InfoPath.Document\" versionProgid=\"InfoPath.Document.2\"?><my:myFields xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xhtml=\"http://www.w3.org/1999/xhtml\" xmlns:pc=\"http://schemas.microsoft.com/office/infopath/2007/PartnerControls\" xmlns:d=\"http://schemas.microsoft.com/office/infopath/2003/ado/dataFields\" xmlns:dfs=\"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution\" xmlns:my=\"http://schemas.microsoft.com/office/infopath/2003/myXSD\" xmlns:xd=\"http://schemas.microsoft.com/office/infopath/2003\"><my:field_1>test3</my:field_1><my:field_2>test4</my:field_2><my:field_3></my:field_3><my:field_4></my:field_4><my:field_5>{\"field_1dtstr\":\"test1\",\"field_2dtstr\":\"teest2\"}</my:field_5><xd:DataConnection Name=\"FR_FileInfo_DataFiles\" type=\"\"><dfs:myFields xmlns:my=\"http://schemas.microsoft.com/office/infopath/2003/myXSD/2025-02-24T18:53:57\" xmlns:d=\"http://schemas.microsoft.com/office/infopath/2003/ado/dataFields\" xmlns:dfs=\"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution\" xmlns:xd=\"http://schemas.microsoft.com/office/infopath/2003\"><dfs:dataFields><d:FR_FileInfo_DataFiles FileID=\"\" FileInfo=\"\" FileName=\"\" IsLatest=\"\" PropPromotionValues=\"\" ReviewerId=\"\" RowVersion=\"\" RunDetails=\"\" SapConnectionName=\"\" SapSystemId=\"\" ScriptName=\"\" ScriptStoreId=\"\" ScriptVersion=\"\" SequenceGuid=\"\" /></dfs:dataFields><dfs:dataOrderByFields>\r\n\t\t\t\t{{OrderByFields}}\r\n\t\t\t</dfs:dataOrderByFields><xd:SchemaInfo LocalName=\"myFields\" NamespaceURI=\"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution\"><xd:Namespaces><xd:Namespace LocalName=\"myFields\" NamespaceURI=\"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution\" /></xd:Namespaces><xd:RequiredAnys /></xd:SchemaInfo></dfs:myFields></xd:DataConnection></my:myFields>";

        // Parse XML
        XDocument doc = XDocument.Parse(xmlString);

        // Extract the JSON string from field_5
        string field5Json = doc.Root.Element("my:field_5")?.Value;

        // Parse the JSON using Newtonsoft.Json
        JObject jsonObject = JObject.Parse(field5Json);

        // Extract the field_1dtstr and field_2dtstr values
        string field1dtstr = jsonObject["field_1dtstr"]?.ToString();
        string field2dtstr = jsonObject["field_2dtstr"]?.ToString();

        // Update field_3 and field_4 with these values
        XElement field3 = doc.Root.Element("my:field_3");
        XElement field4 = doc.Root.Element("my:field_4");

        if (field3 != null) field3.Value = field1dtstr;
        if (field4 != null) field4.Value = field2dtstr;

// code ends here

in above example i have used a static xml but this can be picked up from current document and run dynamically in custom plugin.

 as you can see field_5 has the josn value (fetched from database) further i am parsing through this json and setting field_1dtstr to field_3 and field_2dtstr to field_4 on form respectively.

Documentation related to custom plugin : https://help.precisely.com/r/t/1016755365/2023-12-31/Automate-Evolve/pub/Latest/en-US/Automate-Evolve-User-Guide/Plug-ins?tocId=4himg_YrBfI~t16mtdrv2A§ion=plugins__add

Hope this helps.

Regards

------------------------------
Hammad Naeem
Precisely Software Inc.
------------------------------

Original Message:
Sent: 02-11-2025 09:22
From: Alexey Skotnikov
Subject: Json/XML transformation in workflow

Hello Experts!

We need to get several fields from SQL view as part of workflow, and doing that via Set Form Data plugin and $$GetValueFromDataConnection.

Unfortunately ,it executes same SQL query several times for each field, that is pretty slow, because the view is complicated.

Question: can we get data in json or xml format once and then parse it into several solution or form fields - or even reference it in workflow as an object?

------------------------------
Alexey Skotnikov
GPO, Data Governance
ABBOTT RAPID DX INTERNATIONAL LIMITED
------------------------------