cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
agneum
Advocate IV
Advocate IV

Can't get ItemInternalId field from loop

I have an array of data that gets passed into a foreach block in my flow.

Here's how the structure looks like when I download to preview the output:



[{"@odata.etag":"W/\"JzEsNTYzNzE0NDU3Nic=\"",
"ItemInternalId":"ce8f4afe-16cb-4b7d-a4f5-b23b029e8bf3","Locator":"TestA","DirPartyTable_PrivateForParty_PartyNumber":"","IsInstantMessage":"No","IsMobilePhone":"No","CountryRegionCode":"","LogisticsLocation_FK_LocationId":"000000912","ElectronicAddressRoles":"","LocatorExtension":"","IsPrimary":"No","Description":"","Type":"None","IsPrivate":"No"},
{"@odata.etag":"W/\"JzEsNTYzNzE0NDU3Nyc=\"","ItemInternalId":"2d702861-6901-4e11-a182-0debfb52f382","Locator":"TestB","DirPartyTable_PrivateForParty_PartyNumber":"","IsInstantMessage":"No","IsMobilePhone":"No","CountryRegionCode":"","LogisticsLocation_FK_LocationId":"000000913","ElectronicAddressRoles":"","LocatorExtension":"","IsPrimary":"No","Description":"","Type":"None","IsPrivate":"No"}.. etc etc etc.


I want to read every row using the for each and insert this data into a SQL table. I use a dynamic expression to get the fields.

 

This one works fine:
items('Apply_to_each')?['Locator'] 

 

But for some reason

 

items('Apply_to_each')?['ItemInternalId'] returns nulls, as if the values aren't there.

 

Why is this? Does anyone know what I could try? 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@v-litu-msft 

I could get the fields out if I mapped the value to another column:

test.png

I've dropped and created the Table multiple times, and all the columns are of type "varchar(max)". So it makes absolutely no sense to me. 


The only thing I can think of is that the "Insert Row (V2)" action is bugged, in that it maybe assumes the table definition and keeps it in some metadata. Perhaps at one point I created the SQL-table without this ItemInternalId column, and recreated the table at a later stage, but the flow still thinks the old definition for insert should be used:

I.e. the part of the code that says "Insert into Table (Locator, DirParty... etc etc)"
is using the old column definition when it should be " Insert into Table (ItemInternalId, Locator , etc etc etc)


Looks like a bug to me.

 

View solution in original post

4 REPLIES 4
agneum
Advocate IV
Advocate IV

Bump!

v-litu-msft
Community Support
Community Support

Hi @agneum,

 

Your expression is not a problem, I can use it to extract the ItemInternalId of each item:

 

Annotation 2020-05-19 112128.jpg

 

Annotation 2020-05-19 112149.jpg

 

It may be caused by the value in the Apply to each action is null, or you could use Parse JSON action, put these data into the content field, then use the following Schema, the dynamic content of ItemInternalId could be used directly in the following steps:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "Locator": {
                "type": "string"
            },
            "DirPartyTable_PrivateForParty_PartyNumber": {
                "type": "string"
            },
            "IsInstantMessage": {
                "type": "string"
            },
            "IsMobilePhone": {
                "type": "string"
            },
            "CountryRegionCode": {
                "type": "string"
            },
            "LogisticsLocation_FK_LocationId": {
                "type": "string"
            },
            "ElectronicAddressRoles": {
                "type": "string"
            },
            "LocatorExtension": {
                "type": "string"
            },
            "IsPrimary": {
                "type": "string"
            },
            "Description": {
                "type": "string"
            },
            "Type": {
                "type": "string"
            },
            "IsPrivate": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.etag",
            "ItemInternalId",
            "Locator",
            "DirPartyTable_PrivateForParty_PartyNumber",
            "IsInstantMessage",
            "IsMobilePhone",
            "CountryRegionCode",
            "LogisticsLocation_FK_LocationId",
            "ElectronicAddressRoles",
            "LocatorExtension",
            "IsPrimary",
            "Description",
            "Type",
            "IsPrivate"
        ]
    }
}

Annotation 2020-05-19 112742.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


Still doesn't work. My Create Table  SQL definition is as following:

def.png


The flow looks like this:def0.png

End result in the SQL table:

def2.png.jpg


@v-litu-msft 

I could get the fields out if I mapped the value to another column:

test.png

I've dropped and created the Table multiple times, and all the columns are of type "varchar(max)". So it makes absolutely no sense to me. 


The only thing I can think of is that the "Insert Row (V2)" action is bugged, in that it maybe assumes the table definition and keeps it in some metadata. Perhaps at one point I created the SQL-table without this ItemInternalId column, and recreated the table at a later stage, but the flow still thinks the old definition for insert should be used:

I.e. the part of the code that says "Insert into Table (Locator, DirParty... etc etc)"
is using the old column definition when it should be " Insert into Table (ItemInternalId, Locator , etc etc etc)


Looks like a bug to me.

 

View solution in original post

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (47,027)