PM with Automation Cloud - How to implement update query with Jinja SQL for Tranformation in PM App

[nisha.sonawane]

Can some one help here on how to implement Update query with Jinja SQL for Process Mining Transformation, I am trying to implement Transformation to refelect data in PM app, however getting following error:

(‘42000’, "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword ‘UPDATE’. (156) (SQLMoreResults); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword ‘case’. (

please refer my query from visual studio code:

[Martijn_Wijffelaars]

Hey Nisha,

Each .sql file should result in one final select query that defines the new table in the data model. An insert/update statement will be derived from that automatically.

Your final table should be named the same as the .sql file, Case_Transformation_table in your case.

So for example you could add something like this to your select statement:

        case 
            when Cases_raw."Case_value" > 500 then "Big Invoice"
            else NULL 
        end as "Big invoice"

Please check this docs page for more details: https://docs.uipath.com/process-mining/automation-cloud/latest/user-guide/editing-transformations#data-transformations

[nisha.sonawane]

@Martijn_Wijffelaars

        case 
            when Cases_raw."Case_value" > 500 then "Big Invoice"
            else NULL 
        end as "Big invoice"

Hello @Martijn_Wijffelaars thank you so much for your reply, yes i have also come across the Case syntax trying it however it is giving me error:

also, to add my column name i s custom_case_text_1 and the value that i want to set to it is Big Invoice as per condition

Please suggest.

[nisha.sonawane]

@Martijn_Wijffelaars , please refer below image:

as suggested by you tried to update query, still showing error as given.

[nisha.sonawane]

Hello @Martijn_Wijffelaars very happy to share that, able to execute query successfully and also able to reflect same in SQL server, please refer attached screen shot:

thank you for support!

[Martijn_Wijffelaars]

Nice one! Thanks for sharing the result.

[Martijn_Wijffelaars]

Btw, you should be able to refer to the fields without the pm_utils functions. They only needed when you need to convert from one data type into another.

Cases_raw."Case_value" should work.

[nisha.sonawane]

Will try that too.

[nisha.sonawane]

Also, @Martijn_Wijffelaars now that I am able execute transformations successfully, but still after importing transformations to PM app, can not see newly added table with PM app, where and how will it be reflected.

Also, have tried to upload the data once again after import transformation

[Martijn_Wijffelaars]

Hey Nisha, for 23.4 the data model in Process Mining is fixed,
to add new fields to existing tables, please use the so-called “custom fields”, see https://docs.uipath.com/process-mining/automation-cloud/latest/user-guide/templateone-multifiles-input-fields#custom-case-fields.

PS. The feature “Flexible data model” which is targetted for 23.10 will make it possible to add new fields and tables to the data model of an app.

[nisha.sonawane]

Thank you so much, @Martijn_Wijffelaars for the updates, so If I am not wrong your saying with current version of PM automation cloud, it does not reflect the new added transformation (tables/feilds) in PM app post import, is this correct understanding?

and this will be reflected with upcoming version of PM app?

Also, for now if I want to see the reflection of Transformation with my PM App, what kind of transformation I can perform right now, if you could please give me any sample of transformation?

Many thanks for your kind support!

[Martijn_Wijffelaars]

That’s correct, the way to add new fields to your app now is to output them as Custom fields in existing tables.

Adjust the transformations of your app such the new inputs are outputted as the correct fields.

[nisha.sonawane]

ok, it means, if we added new transformations (new tables/ fields) through .sql file with dbt it won’t reflect now, as I have added new table with new sql file in dbt project and it properly reflected the same in my SQL Database schema however same is not reflecting with PM App.

And for now the only way is to use the Custom fields and manually map them for new transformations right?