Survey123 & Power Automate - auto increment unique ID for survey response to avoid duplicates of edited survey responses

2168
5
10-14-2020 03:03 PM
SummerKoszti
New Contributor III

Hi Team, 

I need some help with the below and would appreciate any help / advice. 

I have a created a survey whereby our personnel collect information from around the community using mostly the Survey123 mobile phone application. 

When a survey response is submitted the information is displayed in a webmap, and using power automate it is also collated to an excel spreadsheet - this all works fine.

However, when a survey response undergoes an edit, currently it duplicates, adding another row with all attributes, including the edited attribute to the excel spreadsheet. 

What I want to do is update the row of the initial survey response - not add a row. 

The solution I had is to ensure each survey response has a unique ID that is automatically generated (I have no idea how to do this, or if I can use an object ID etc) ....

and then set a condition in power automate that once a survey response is submitted it checks to see if the Unique ID is already in the excel spreadsheet:

If it IS NOT, it adds a row

If it IS, it updates the attributes that requires updating

So my question is - will this work? and if so, what is the best way to go about creating a unique ID that automatically generates for each survey submitted? Or can I use the GlobalID somehow to do this? 

Or if any one has come across a similar issue how did you go about fixing the solution. 

Thanks, 
Summer 

0 Kudos
5 Replies
SummerKoszti
New Contributor III

I found a solution using the uuid() in the calculation field in the xlsx form for the survey. (messy number but no problem as it is hidden).

Now I need assistance with the flow in power automate

Thanks

0 Kudos
IsmaelChivite
Esri Notable Contributor

Hi. Microsoft Automate allows you to update existing records in a spreadsheet. You will want to make sure that you have a column in your spreadsheet that uniquely identifies each record. I think the easiest identifier you can use is the ObjectID, or you can also use the globalid.

In this thread there is some info about updating rows in Automate:  Solved: Re: Help using Flow to update row in Excel - Power Platform Community 

0 Kudos
SummerKoszti
New Contributor III

Thanks Ismael. 

I get the following error for the workflow: 

The API 'survey123' returned an invalid response for workflow operation 'When_a_survey_response_is_submitted_for_Intelligence_Noting' of type 'OpenApiConnectionWebhook'. Error details: 'The API operation 'AddData' requires the property 'body/feature/result/objectId' to be of type 'String' but is of type 'Integer'.'

Is there a way I can change the ObjectId to string? (I would need a step by step if there was as I would have no idea where to go to change it?)   

0 Kudos
AnthonyJonesRSK
Occasional Contributor II

Hi Ismael,

I have seen the same error as Summer regarding passing the objectid to an excel spreadsheet, in my case I am using the "Add a row to a table" action in Power Automate. For now I've just removed the objectid from my spreadsheet but I'm also getting the error below with another of my survey fields.

ResponseSwaggerSchemaValidationFailure. The API 'survey123' returned an invalid response for workflow operation 'When_a_survey_response_is_submitted' of type 'OpenApiConnectionWebhook'. Error details: 'The API operation 'AddData' requires the property 'body/feature/attributes/<Field Name>' to be of type 'Integer' but is of type 'Float'.'

I'm not quite sure why I'm getting this error as the field in question is a Double field so not sure why it's required to be an integer. Is this something you have come across before?

Any assistance would be much appreciated.

Kind Regards

Anthony

QPWS_SpatialServices
New Contributor II

This may be an old thread but it is not resolved. Still seeing the same issue in PowerAutomate. Either there was an update to PowerAutomate or an update to S123 but it breaks the flow. So much so that we are republishing our survey to use "one apple", "two apples" rather than 1, 2, 3 as choices for our drop down in a text question.

QPWS_SpatialServices_0-1629440480168.png

 

0 Kudos