cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rocky_Dev
Regular Visitor

Check if record exists in Dataverse from a MySQL DB

Hi There. I have a MySQL DB with a `Countries` table where I need to make sure that the same data exists in Dataverse.
I have build a simple flow to pull the data from MySQL, loop over every record and make a lookup with Dataverse.

But I get the following error and I am not to sure where it goes wrong?

Screenshot 2021-11-12 at 09.40.35.png

 

The entire flow look like this:
Screenshot 2021-11-12 at 09.35.40.png

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
rampprakash
Super User
Super User

Hello @Rocky_Dev,

 

Thats strange, i tried from my end and its working as expected

 

rampprakash_0-1636718436240.png

 

 

rampprakash_1-1636718453173.png

 

 

Query in if Condition : 

length(outputs('List_rows_2')?['body/value'])
 

Please mark as Answer if it is helpful and provide Kudos


Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

 

View solution in original post

rampprakash
Super User
Super User

Hello @Rocky_Dev,

 

Simple try  using ' ' (Quotes in Country ID)

 

iu_integration_key ne 'CountryID'

 

rampprakash_0-1636722575271.png

 

 

Thats it 🙂

 

Please mark as Answer if it is helpful and provide Kudos


Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

View solution in original post

5 REPLIES 5
rampprakash
Super User
Super User

Hello @Rocky_Dev,

 

Points to be Noted.

 

rampprakash_0-1636708899346.png

You are passing Country ID here as ROW ID, Country ID is UniqueIdentifier in DataVerse, and am not sure how it will be in SQL. hence you got the error.

 

Suggesstion:

 

Use List Rows to Countries in DataVerse Flows then use filter countryname eq 'CountryNameFromSQL'

 

then check If Condition in Flows with 

 

@length(body('Get_rows')?['value'])  equals Zero(0)

 

then Create else do your operation

 

 

Please mark as Answer if it is helpful and provide Kudos


Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

This almost works!
However in myql I have the following data

 

CountryIDCountry
1United Kingdom
2Denmark
3Non Inland UK
4Sweeden

 

And in Dataverse I got:

 

iu_integration_keyCountry
1United Kingdom
2Denmark

 

And as you said(almost),

  1. Get List Rows to Countries in Dataverse
  2. Get List Rows to Countries from Mysql
    1. Filter with `iu_integration_key ne CountryID`

But for some reason is `Denmark` duplicated in the outut

[
  {

    "CountryID": 2,
    "Country": "Denmark",
    "CurrencyID": 3,

  },
  {

    "CountryID": 3,
    "Country": "Non Inland UK",
    "CurrencyID": 1,
   
  },
  {

    "CountryID": 4,
    "Country": "Sweden",
    "CurrencyID": 4,
    
  }
]
rampprakash
Super User
Super User

Hello @Rocky_Dev,

 

Thats strange, i tried from my end and its working as expected

 

rampprakash_0-1636718436240.png

 

 

rampprakash_1-1636718453173.png

 

 

Query in if Condition : 

length(outputs('List_rows_2')?['body/value'])
 

Please mark as Answer if it is helpful and provide Kudos


Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

 

I might think I made an error, sry! You initial thought was correct.

But I am having trouble convering the `CountryID` from the external database to a string:
```
A binary operator with incompatible types was detected. Found operand types 'Edm.Int32' and 'Edm.String' for operator kind 'Equal'.
```

rampprakash
Super User
Super User

Hello @Rocky_Dev,

 

Simple try  using ' ' (Quotes in Country ID)

 

iu_integration_key ne 'CountryID'

 

rampprakash_0-1636722575271.png

 

 

Thats it 🙂

 

Please mark as Answer if it is helpful and provide Kudos


Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (1,865)