Dynamics Community Forum Thread Details

Hello,

I have created a custom provider for order intake. However when my order intake flow runs. IOM transformation flow is failed please see the screenshots.

Error:

{

"@odata.context": "">totalitysolutions.crm.dynamics.com/.../$metadata,

"Result": "{\"ErrorMessage\":\"Failed to evaluate transformation. Error: TransformDataRequest failed with '500 Internal Server Error': {\\\"error\\\":\\\"Response status code does not indicate success: 400 (Bad Request).\\\"}\",\"Payload\":{\"transformation_Source\":\"Walmart Customer\",\"transformation_Destination\":\"Dataverse Account\",\"dataverseInsertion_EntityId\":null}}"

}
My transformation:

shared TransformSourceData =
let
customer = List.First(Source[shippingInfo]),
stateMap = Record.FromTable(Table.FromRecords({
[Name = "Alabama", Value = "AL"],
[Name = "Alaska", Value = "AK"],
[Name = "American Samoa", Value = "AS"],
[Name = "Arizona", Value = "AZ"],
[Name = "Arkansas", Value = "AR"],
[Name = "Armed Forces Africa", Value = "AE"],
[Name = "Armed Forces Americas", Value = "AA"],
[Name = "Armed Forces Canada", Value = "AE"],
[Name = "Armed Forces Europe", Value = "AE"],
[Name = "Armed Forces Middle East", Value = "AE"],
[Name = "Armed Forces Pacific", Value = "AP"],
[Name = "California", Value = "CA"],
[Name = "Colorado", Value = "CO"],
[Name = "Connecticut", Value = "CT"],
[Name = "Delaware", Value = "DE"],
[Name = "District of Columbia", Value = "DC"],
[Name = "Federated States Of Micronesia", Value = "FM"],
[Name = "Florida", Value = "FL"],
[Name = "Georgia", Value = "GA"],
[Name = "Guam", Value = "GU"],
[Name = "Hawaii", Value = "HI"],
[Name = "Idaho", Value = "ID"],
[Name = "Illinois", Value = "IL"],
[Name = "Indiana", Value = "IN"],
[Name = "Iowa", Value = "IA"],
[Name = "Kansas", Value = "KS"],
[Name = "Kentucky", Value = "KY"],
[Name = "Louisiana", Value = "LA"],
[Name = "Maine", Value = "ME"],
[Name = "Marshall Islands", Value = "MH"],
[Name = "Maryland", Value = "MD"],
[Name = "Massachusetts", Value = "MA"],
[Name = "Michigan", Value = "MI"],
[Name = "Minnesota", Value = "MN"],
[Name = "Mississippi", Value = "MS"],
[Name = "Missouri", Value = "MO"],
[Name = "Montana", Value = "MT"],
[Name = "Nebraska", Value = "NE"],
[Name = "Nevada", Value = "NV"],
[Name = "New Hampshire", Value = "NH"],
[Name = "New Jersey", Value = "NJ"],
[Name = "New Mexico", Value = "NM"],
[Name = "New York", Value = "NY"],
[Name = "North Carolina", Value = "NC"],
[Name = "North Dakota", Value = "ND"],
[Name = "Northern Mariana Islands", Value = "CM"],
[Name = "Ohio", Value = "OH"],
[Name = "Oklahoma", Value = "OK"],
[Name = "Oregon", Value = "OR"],
[Name = "Palau", Value = "PW"],
[Name = "Pennsylvania", Value = "PA"],
[Name = "Puerto Rico", Value = "PR"],
[Name = "Rhode Island", Value = "RI"],
[Name = "South Carolina", Value = "SC"],
[Name = "South Dakota", Value = "SD"],
[Name = "Tennessee", Value = "TN"],
[Name = "Texas", Value = "TX"],
[Name = "Utah", Value = "UT"],
[Name = "Vermont", Value = "VT"],
[Name = "Virgin Islands", Value = "VI"],
[Name = "Virginia", Value = "VA"],
[Name = "Washington", Value = "WA"],
[Name = "West Virginia", Value = "WV"],
[Name = "Wisconsin", Value = "WI"],
[Name = "Wyoming", Value = "WY"]
})),
external_id = customer[phone],
address1 = if List.Count(customer[postalAddress][address1]) > 0 then customer[postalAddress][address1]{0} else null,
address2 = if List.Count(customer[postalAddress][address2]) > 1 then customer[postalAddress][address2]{1} else null,
account_base = [
name = Record.FieldOrDefault(customer, "name"),
telephone1 = Record.FieldOrDefault(customer, "phone")

],
account_with_addr1 = if address1 <> null then Record.Combine({account_base, [
address1_line1 = Record.FieldOrDefault(address1, "address1"),
address1_line2 = Record.FieldOrDefault(address1, "address2"),
address1_city = Record.FieldOrDefault(customer[postalAddress][city], "city"),
address1_stateorprovince =
if Record.FieldOrDefault(customer[postalAddress][state], "state") = null then null else
if Record.FieldOrDefault(stateMap, Record.FieldOrDefault(customer[postalAddress][state], "state")) = null then Record.FieldOrDefault(customer[postalAddress][state], "state") else
Record.FieldOrDefault(stateMap, Record.FieldOrDefault(customer[postalAddress][state], "state")),
address1_postalcode = Record.FieldOrDefault(customer[postalAddress][postalCode], "postalCode"),
address1_country = Record.FieldOrDefault(customer[postalAddress][country], "country"),

]}) else account_base,
account_final = if address2 <> null then Record.Combine({account_with_addr1, [
address2_line1 = Record.FieldOrDefault(address2, "address1"),
address2_line2 = Record.FieldOrDefault(address2, "address2"),
address2_city = Record.FieldOrDefault(address2, "city"),
address2_stateorprovince =
if Record.FieldOrDefault(address2, "province") = null then null else
if Record.FieldOrDefault(stateMap, Record.FieldOrDefault(address2, "province")) = null then Record.FieldOrDefault(address2, "province") else
Record.FieldOrDefault(stateMap, Record.FieldOrDefault(address2, "province")),
address2_postalcode = Record.FieldOrDefault(address2, "zip"),
address2_country = Record.FieldOrDefault(address2, "country"),
address2_telephone1 = Record.FieldOrDefault(address2, "phone")
]}) else account_with_addr1
in Text.FromBinary(Json.FromValue(account_final));

My Json:

"shippingInfo": {
"phone": "9898359449",
"estimatedDeliveryDate": 1677614400000,
"estimatedShipDate": 1677211200000,
"methodCode": "Standard",
"postalAddress": {
"name": "William Noel",
"address1": "1115 Eastman Ave",
"address2": null,
"city": "Midland",
"state": "MI",
"postalCode": "48640",
"country": "USA",
"addressType": "RESIDENTIAL"
}
}

It always means your transformation is wrong :( Also, it may have been intentional to only paste one of the records from the JSON, but what you pasted is incorrect as a property "shippingInfo" can not be a root level item. You would need to encapsulate that in an object {} or an array [] for it to be valid, but your transformation is suggesting it should be an object.

Heres how you debug (Use #7 per my screenshot)

Open Excel
Open a new Blank Workbook
Navigate to Data > Get Data > From Other Sources > Blank Query
Click on New Source > File under New Query
Import a JSON file that has the data format you expect
Use the Power Query editor to test out your transformations and see the Power Query language required to perform your transformation
Alternatively use the Convert to Table step, and then use the Advanced Editor to enter your power query directly.
Under “Applied Steps” on the right, click on each selection and see the Power Query Transformation that was applied
After you have built your Power Query, use it in an IOM Provider Definition Transformation

Community site session details

IOM transformation flow failure

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates