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 Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (3,764)