[CoE Starter Kit - BUG] Admin | Sync Template v3 (PVA Usage) Fail

[declawson]

Describe the issue

A binary operator with incompatible types was detected. Found operand types 'Edm.Guid' and 'Edm.String' for operator kind 'Equal'.

Expected Behavior

No response

What solution are you experiencing the issue with?

Core

What solution version are you using?

4.20

What app or flow are you having the issue with?

Admin | Sync Template v3 (PVA Usage)

Steps To Reproduce

Resubmit the failed run

Anything else?

[HenryJammes]

Hi @declawson, thank you for reporting this!

I didn’t experience this bug in my tests. Do you have more details on the error message? For example like documented here: https://learn.microsoft.com/en-us/power-automate/fix-flow-failures#identify-the-error

Do you have any PVA bots at all in your tenant?

[declawson]

The error message is in the description, A binary operator with incompatible types was detected. Found operand types 'Edm.Guid' and 'Edm.String' for operator kind 'Equal'.

Honestly, I am not sure if there are any and not sure where to look.

[declawson]

[HenryJammes]

Thank you - if you click into the action ‘box’ that is in error, can you copy the content of the body? Are there any input or output files you can download and share? I’d like to see if we can see query the connector tried to make that is resulting in an error.

[declawson]

[HenryJammes]

Thank you! Can you click on ‘show raw inputs’ and copy/paste the content?

[declawson]

[declawson]

'conversationstarttime',PropertyValue=1) and _bot_conversationtranscriptid_value eq '')/aggregate($count as statecode)"

[HenryJammes]

Thank you, this is very useful @declawson. I now understand what needs to be fixed in the cloud flow.

It looks like some of the PVA bots in your CoE environment don't have a value in "Bot Id" (admin_botid), and the Dataverse connector in the PVA Usage flow tries to filter using a null value. We could filter these bots out to make sure we don't hit that error, but we also should understand how these bots were created without a Bot Id value and fix this.

[declawson]

What do you need from me?

[HenryJammes]

Nothing more - thank you!

[bwieland86]

Glad this was reported, as I am seeing the same issue as well:

[HenryJammes]

Hi @bwieland86 and @declawson, This is timely, I just finished fixing the bug and the next release of CoE Starter Kit will include the fix.

The root cause is that we introduced a new "Bot Id" column on the "PVA Bot" table, but for pre-existing PVA Bots, it didn't get automatically filled with an Id. Luckily, the "Bot Id" should be the same value as the "PVA Bot" primary key ("admin_pvaid") and the same as the "Record GUID as String" column ("admin_recordguidasstring").

So, a quick way to fix this is copying the value from "Record GUID as String" to the "Bot Id" column for all of your PVA bots. Alternatively -- but that's only recommend for 'small' tenants without too much inventory data -- you can run the full inventory again, as documented here: https://learn.microsoft.com/en-us/power-platform/guidance/coe/faq#running-a-full-inventory

Here's a step by step of how to bulk update the "Bot Id" column of your PVA Bot in your CoE Starter Kit environment.

1. Launch the "Power Platform Admin View"
2. Navigate to the "Bots" view
3. Click on "Edit Columns"
4. Only leave "Name", and add "Record GUID as String", and "Bot Id" as columns
5. Apply

6. In the command bar, click on the "Export to Excel" dropdown, and select "Open in Excel Online"

7. In Excel, copy the content from the "Record GUID as String" column to the "Bot Id" column for all the rows.

8. Click on save and wait for the update to reflect. Once it's done, check the status of the next execution of the "Admin | Sync Template v3 (PVA Usage)" (normally the following day) to make sure it ran correctly.

[HenryJammes]

Also, if you'd like to create PVA Usage records for as long as you have PVA conversation transcripts data in your various environments (and not just for the previous day), there is a 'Run Once' flow available in this solution: https://github.com/microsoft/coe-starter-kit/blob/main/CenterofExcellenceResources/Release/Collateral/CenterofExcellencePVAUsageFirstRunAllData_1_0_0_1_managed.zip

[declawson]

Thanks,

I didn’t really need the usage data but once I populated the BotId I ran the one-time flow in the solution below and once I verified it was successful turned it back off.

Thanks for getting this fixed so quickly.

[https://webmail.atcc.org/owa/auth/image001.jpg] Debbie Clawson Senior IT Architect

10801 University Boulevard Manassas, VA 20110-2209 Tel: (703) 365-2700 ext. 2820 Fax: (703) 365-2701 Email: @.**@.>

From: Henry Jammes @.> Sent: Tuesday, December 13, 2022 11:04 AM To: microsoft/coe-starter-kit @.> Cc: Clawson, Debbie @.>; Mention @.> Subject: Re: [microsoft/coe-starter-kit] [CoE Starter Kit - BUG] Admin | Sync Template v3 (PVA Usage) Fail (Issue #4391)

Also, if you'd like to create PVA Usage records for as long as you have PVA conversation transcripts data in your various environments (and not just for the previous day), there is a 'Run Once' flow available in this solution: https://github.com/microsoft/coe-starter-kit/blob/main/CenterofExcellenceResources/Release/Collateral/CenterofExcellencePVAUsageFirstRunAllData_1_0_0_1_managed.ziphttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_microsoft_coe-2Dstarter-2Dkit_blob_main_CenterofExcellenceResources_Release_Collateral_CenterofExcellencePVAUsageFirstRunAllData-5F1-5F0-5F0-5F1-5Fmanaged.zip&d=DwMCaQ&c=dcFnI-8Ri3NVXdqjME8ydA&r=uUJhS812LgeeJCahNL0LAcMg66Qy9qmXSa-p4FzocmM&m=3FZjDLyWL-6xdB6iHeNmpIaN3pLSmnz9Y2ad60OixO3obp6fGKGy0Td8pDSxajXq&s=twbLqm10dRNmlREUn0K6oVyH_uo-L61EIZtcPoe30Os&e=

— Reply to this email directly, view it on GitHubhttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_microsoft_coe-2Dstarter-2Dkit_issues_4391-23issuecomment-2D1348890637&d=DwMCaQ&c=dcFnI-8Ri3NVXdqjME8ydA&r=uUJhS812LgeeJCahNL0LAcMg66Qy9qmXSa-p4FzocmM&m=3FZjDLyWL-6xdB6iHeNmpIaN3pLSmnz9Y2ad60OixO3obp6fGKGy0Td8pDSxajXq&s=ZHc1aMMZ1tY6EKXA7ikZGft0Zqzg54IQmC4GXPPfolM&e=, or unsubscribehttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_notifications_unsubscribe-2Dauth_ARXPZKLJPS2YDFL5OPISKNTWNCM6FANCNFSM6AAAAAASYUQNSM&d=DwMCaQ&c=dcFnI-8Ri3NVXdqjME8ydA&r=uUJhS812LgeeJCahNL0LAcMg66Qy9qmXSa-p4FzocmM&m=3FZjDLyWL-6xdB6iHeNmpIaN3pLSmnz9Y2ad60OixO3obp6fGKGy0Td8pDSxajXq&s=jT3PciJQe9RMI1dzpf-8gglW2rfJufSAmiz6NjRBYH4&e=. You are receiving this because you were mentioned.Message ID: @.**@.>>

[cr0max]

Hi, we face the same issue and are interrested in the sessions/conversations count to activate the alerts. We did have session counts in our previus data, but this is cleared with the update... However, we have a large tenant and do wan't to avoid a full inventory run as this will take weeks.

Will this issue be fixed in the January release? Or is there some other way to fix it without running a full inventory?

[HenryJammes]

Hi @cr0max,

The solution I shared (quoted below) fixes the issue without needing to 1/ do a full inventory or 2/ wait for the next release of the CoE Starter Kit. So I recommend that you do this:

Here's a step by step of how to bulk update the "Bot Id" column of your PVA Bot in your CoE Starter Kit environment.

1. Launch the "Power Platform Admin View"
2. Navigate to the "Bots" view
3. Click on "Edit Columns"
4. Only leave "Name", and add "Record GUID as String", and "Bot Id" as columns
5. Apply

6. In the command bar, click on the "Export to Excel" dropdown, and select "Open in Excel Online"

7. In Excel, copy the content from the "Record GUID as String" column to the "Bot Id" column for all the rows.

8. Click on save and wait for the update to reflect. Once it's done, check the status of the next execution of the "Admin | Sync Template v3 (PVA Usage)" (normally the following day) to make sure it ran correctly.

Once done, I also recommend that you do this as well so that historical data gets rebuilt:

Also, if you'd like to create PVA Usage records for as long as you have PVA conversation transcripts data in your various environments (and not just for the previous day), there is a 'Run Once' flow available in this solution: https://github.com/microsoft/coe-starter-kit/blob/main/CenterofExcellenceResources/Release/Collateral/CenterofExcellencePVAUsageFirstRunAllData_1_0_0_1_managed.zip

Henry

[declawson]

For what it’s worth, the steps provided below did resolve the issue in my tenant so I would recommend. Although I think I just imported the excel file back in once the column was updated with data.

[https://webmail.atcc.org/owa/auth/image001.jpg] Debbie Clawson Senior IT Architect

10801 University Boulevard Manassas, VA 20110-2209 Tel: (703) 365-2700 ext. 2820 Fax: (703) 365-2701 Email: @.**@.>

From: Henry Jammes @.> Sent: Thursday, January 5, 2023 9:01 AM To: microsoft/coe-starter-kit @.> Cc: Clawson, Debbie @.>; Mention @.> Subject: Re: [microsoft/coe-starter-kit] [CoE Starter Kit - BUG] Admin | Sync Template v3 (PVA Usage) Fail (Issue #4391)

Hi @cr0maxhttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_cr0max&d=DwMCaQ&c=dcFnI-8Ri3NVXdqjME8ydA&r=uUJhS812LgeeJCahNL0LAcMg66Qy9qmXSa-p4FzocmM&m=GzCX30Huryzr2n-eHBbxNbRpaSCpJimK6WVZaQEQdYfe_2_3QXurTu0CpYC5sBQN&s=SG9Ib4aLG1tQ4AkKSkz5ym7h8A4eAr75kC2qsrKVr-c&e=,

The solution I shared (quoted below) fixes the issue without needing to 1/ do a full inventory or 2/ wait for the next release of the CoE Starter Kit. So I recommend that you do this:

Here's a step by step of how to bulk update the "Bot Id" column of your PVA Bot in your CoE Starter Kit environment.

1. Launch the "Power Platform Admin View"
2. Navigate to the "Bots" view
3. Click on "Edit Columns"
4. Only leave "Name", and add "Record GUID as String", and "Bot Id" as columns
5. Apply

[image]https://urldefense.proofpoint.com/v2/url?u=https-3A__user-2Dimages.githubusercontent.com_37898885_207366086-2D05e665b7-2D3c4b-2D4a4f-2Db451-2D610673f5c8f4.png&d=DwMCaQ&c=dcFnI-8Ri3NVXdqjME8ydA&r=uUJhS812LgeeJCahNL0LAcMg66Qy9qmXSa-p4FzocmM&m=GzCX30Huryzr2n-eHBbxNbRpaSCpJimK6WVZaQEQdYfe_2_3QXurTu0CpYC5sBQN&s=QEdu7gUf-H2InOGbE2dsyjt1wAidbwL7CW9D2xiLK34&e=

1. In the command bar, click on the "Export to Excel" dropdown, and select "Open in Excel Online"

[image]https://urldefense.proofpoint.com/v2/url?u=https-3A__user-2Dimages.githubusercontent.com_37898885_207366567-2Dc4d4c71a-2D9e91-2D4914-2D9d4c-2Df3e048f1c85d.png&d=DwMCaQ&c=dcFnI-8Ri3NVXdqjME8ydA&r=uUJhS812LgeeJCahNL0LAcMg66Qy9qmXSa-p4FzocmM&m=GzCX30Huryzr2n-eHBbxNbRpaSCpJimK6WVZaQEQdYfe_2_3QXurTu0CpYC5sBQN&s=pRB3odwi0xh65qGsknrED_NzT17MXPleUbZ6_Y9uIxM&e=

1. In Excel, copy the content from the "Record GUID as String" column to the "Bot Id" column for all the rows.

[image]https://urldefense.proofpoint.com/v2/url?u=https-3A__user-2Dimages.githubusercontent.com_37898885_207367267-2D2fbee09c-2Dd463-2D4098-2D8ac8-2Dce2deeb597a8.png&d=DwMCaQ&c=dcFnI-8Ri3NVXdqjME8ydA&r=uUJhS812LgeeJCahNL0LAcMg66Qy9qmXSa-p4FzocmM&m=GzCX30Huryzr2n-eHBbxNbRpaSCpJimK6WVZaQEQdYfe_2_3QXurTu0CpYC5sBQN&s=uLOYyGUxgbCGLRf5XDeu4r5xxqTCa-MiuEPgo-tqDxA&e=

1. Click on save and wait for the update to reflect. Once it's done, check the status of the next execution of the "Admin | Sync Template v3 (PVA Usage)" (normally the following day) to make sure it ran correctly.

Once done, I also recommend that you do this as well so that historical data gets rebuilt:

Also, if you'd like to create PVA Usage records for as long as you have PVA conversation transcripts data in your various environments (and not just for the previous day), there is a 'Run Once' flow available in this solution: https://github.com/microsoft/coe-starter-kit/blob/main/CenterofExcellenceResources/Release/Collateral/CenterofExcellencePVAUsageFirstRunAllData_1_0_0_1_managed.ziphttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_microsoft_coe-2Dstarter-2Dkit_blob_main_CenterofExcellenceResources_Release_Collateral_CenterofExcellencePVAUsageFirstRunAllData-5F1-5F0-5F0-5F1-5Fmanaged.zip&d=DwMCaQ&c=dcFnI-8Ri3NVXdqjME8ydA&r=uUJhS812LgeeJCahNL0LAcMg66Qy9qmXSa-p4FzocmM&m=GzCX30Huryzr2n-eHBbxNbRpaSCpJimK6WVZaQEQdYfe_2_3QXurTu0CpYC5sBQN&s=5qRESnAxqC2I8SWr1AyPr6Xwv0gOMRxNx9IaNm_Y_Tw&e=

Henry

— Reply to this email directly, view it on GitHubhttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_microsoft_coe-2Dstarter-2Dkit_issues_4391-23issuecomment-2D1372249883&d=DwMCaQ&c=dcFnI-8Ri3NVXdqjME8ydA&r=uUJhS812LgeeJCahNL0LAcMg66Qy9qmXSa-p4FzocmM&m=GzCX30Huryzr2n-eHBbxNbRpaSCpJimK6WVZaQEQdYfe_2_3QXurTu0CpYC5sBQN&s=gAyDOyefmzdGXvRyA8nNcEcFzuR9z7yekmCC4ZRK7sI&e=, or unsubscribehttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_notifications_unsubscribe-2Dauth_ARXPZKLGTIRGB6VMZG2YSF3WQ3HYPANCNFSM6AAAAAASYUQNSM&d=DwMCaQ&c=dcFnI-8Ri3NVXdqjME8ydA&r=uUJhS812LgeeJCahNL0LAcMg66Qy9qmXSa-p4FzocmM&m=GzCX30Huryzr2n-eHBbxNbRpaSCpJimK6WVZaQEQdYfe_2_3QXurTu0CpYC5sBQN&s=GuhoAT8kg1kJF4_p9iA25THbs1uw6hoVDwafZrpWjUY&e=. You are receiving this because you were mentioned.Message ID: @.**@.>>

[cr0max]

Thanks for the feedback, we did not run the 'run once' solution. But now that we have, it works and the conversations are getting built.

[m3ngi3]

Hi @HenryJammes, Thank you for sharing a temporary workaround! What if the Record GUID as String column is also empty?

[HenryJammes]

Hi @m3ngi3, that's odd, it shouldn't be either. Anyway, there is another workaround: if you go to step #7 of my previously shared solution, you can unhide column A "(Do Not Modify) PVA" and copy its content to the "Bot Id" column:

[m3ngi3]

Wow the UNHIDE columns is a nice thing to know 💖 I already checked a previous Sync Run of the PVA Flow where the ID's are in the list bots action so sorry to bother you, but I am glad I did because this tip is very valuable 💪👍

[HenryJammes]

@m3ngi3 thank you for the feedback! I'll have a look at the sync flow to understand how this column may have ended empty :)

[HenryJammes]

So, I had a quick look, and the "Record GUID as String" should always be populated, as it's done in the same step as the PVA primary key (we just unhid 🥷)

It might be a legacy issue, if that column / flow step was introduced in a later version of the CoE Starter Kit. Your workaround should be permanent :)

[m3ngi3]

Hi @HenryJammes, my first guess is that these PVA's were added as records in MAY22 when an older version of the CoE Kit was installed. Then these ID/GUID columns remained empty or maybe not even present --> I updated the CoE Kit last week with the latest version Kit --> the new sync flow does not update existing PVA records (if they are not modified) so the columns remained empty causing the usage sync flow to fail 😁

[Jenefer-Monroe]

Hi folks. Yes the RecordGuidAsString column was added sometime late last summer / early fall I believe.

There is a flow called CLEANUP - Admin | Sync Template v3 (Delete Bad Data) which is charged with fixing up things like this, to make sure your tables are normalized. Please be sure it is turned on and running, along with all the other Cleanup flows.

Thank you!

[CoEStarterKitBot]

@declawson This has been fixed in the latest release. Please install the latest version of the toolkit following the instructions for installing updates. Note that if you do not remove the unmanaged layers as described there you will not receive updates from us.