cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Looping Through Columns to Create New Rows

Hello,

 

I've got a Sharepoint checklist of 38 items. All items consist of a Yes/No field and a text field for required actions. When a new row is added in that list, I want to add a new row in another list for every required action text field that is not blank. So if there are 20 required action fields that have data, I want 20 rows created in my other list. Is there a way to somehow loop through each column without doing a huge IF statement for all 38 columns?

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate IV
Advocate IV

Hi @StevenF19;

 

please find attached a sample of how to iterate through the fields of a listitem:

My idea is to use the SharePoint API that give you access to nearly all artefacts.

 

1. Get Fields of the SP-List:

 

The Api-Call to get all fields of a list is like that:

_api/web/Lists/getbytitle('Testliste')/fields

Iterate Columns Get Fields.png

 

The JSON-Schema:

The JSON-Schema:
{
    "type": "object",
    "properties": {
        "d": {
            "type": "object",
            "properties": {
                "results": {
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "__metadata": {
                                "type": "object",
                                "properties": {
                                    "id": {
                                        "type": "string"
                                    },
                                    "uri": {
                                        "type": "string"
                                    },
                                    "type": {
                                        "type": "string"
                                    }
                                }
                            },
                            "DescriptionResource": {
                                "type": "object",
                                "properties": {
                                    "__deferred": {
                                        "type": "object",
                                        "properties": {
                                            "uri": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            },
                            "TitleResource": {
                                "type": "object",
                                "properties": {
                                    "__deferred": {
                                        "type": "object",
                                        "properties": {
                                            "uri": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            },
                            "AutoIndexed": {
                                "type": "boolean"
                            },
                            "CanBeDeleted": {
                                "type": "boolean"
                            },
                            "ClientSideComponentId": {
                                "type": "string"
                            },
                            "ClientSideComponentProperties": {},
                            "ClientValidationFormula": {},
                            "ClientValidationMessage": {},
                            "CustomFormatter": {},
                            "DefaultFormula": {},
                            "DefaultValue": {},
                            "Description": {
                                "type": "string"
                            },
                            "Direction": {
                                "type": "string"
                            },
                            "EnforceUniqueValues": {
                                "type": "boolean"
                            },
                            "EntityPropertyName": {
                                "type": "string"
                            },
                            "Filterable": {
                                "type": "boolean"
                            },
                            "FromBaseType": {
                                "type": "boolean"
                            },
                            "Group": {
                                "type": "string"
                            },
                            "Hidden": {
                                "type": "boolean"
                            },
                            "Id": {
                                "type": "string"
                            },
                            "Indexed": {
                                "type": "boolean"
                            },
                            "InternalName": {
                                "type": "string"
                            },
                            "JSLink": {},
                            "PinnedToFiltersPane": {
                                "type": "boolean"
                            },
                            "ReadOnlyField": {
                                "type": "boolean"
                            },
                            "Required": {
                                "type": "boolean"
                            },
                            "SchemaXml": {
                                "type": "string"
                            },
                            "Scope": {
                                "type": "string"
                            },
                            "Sealed": {
                                "type": "boolean"
                            },
                            "ShowInFiltersPane": {
                                "type": "integer"
                            },
                            "Sortable": {
                                "type": "boolean"
                            },
                            "StaticName": {
                                "type": "string"
                            },
                            "Title": {
                                "type": "string"
                            },
                            "FieldTypeKind": {
                                "type": "integer"
                            },
                            "TypeAsString": {
                                "type": "string"
                            },
                            "TypeDisplayName": {
                                "type": "string"
                            },
                            "TypeShortDescription": {
                                "type": "string"
                            },
                            "ValidationFormula": {},
                            "ValidationMessage": {}
                        },
                        "required": [
                            "__metadata",
                            "DescriptionResource",
                            "TitleResource",
                            "AutoIndexed",
                            "CanBeDeleted",
                            "ClientSideComponentId",
                            "ClientSideComponentProperties",
                            "ClientValidationFormula",
                            "ClientValidationMessage",
                            "CustomFormatter",
                            "DefaultFormula",
                            "DefaultValue",
                            "Description",
                            "Direction",
                            "EnforceUniqueValues",
                            "EntityPropertyName",
                            "Filterable",
                            "FromBaseType",
                            "Group",
                            "Hidden",
                            "Id",
                            "Indexed",
                            "InternalName",
                            "JSLink",
                            "PinnedToFiltersPane",
                            "ReadOnlyField",
                            "Required",
                            "SchemaXml",
                            "Scope",
                            "Sealed",
                            "ShowInFiltersPane",
                            "Sortable",
                            "StaticName",
                            "Title",
                            "FieldTypeKind",
                            "TypeAsString",
                            "TypeDisplayName",
                            "TypeShortDescription",
                            "ValidationFormula",
                            "ValidationMessage"
                        ]
                    }
                }
            }
        }
    }
}

2. Iterate through Fields:

 

The Api-Call to get the value of a field named 'title' is like that:

_api/web/Lists/getbytitle('Testliste')/items/getbyid(1)/Title

Iterate Columns Get Fields2.png

3. Parse dynamic JSON:

The result of the last operation is a JSON with the Fieldname as key:

{
 "d": {
      "File_x0020_Type": null
       }
}

I'm using some string operations to get extract the values:

 

Iterate Columns Get Fields3.png

 

Step one:
Delete the Text before the field name

 

Step two:
Delete the Text behind the field value

 

Step three:
extract the value

 

I'm sure that there will be some other issues while implementing the complete solution.

There are some internal fields that cannot be read in that way, so you have to implement some kind of error handling, but i hope you can start with that.

 

If this post was helpfull, it would be nice if you accept it as a solution for your problem.

 

Best regards
Stefan

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Hi @StevenF19 

 

You could use the ODATA Filter to retreive only the values are not null. Once you retrieved the rows then add a loop to insert the rows logic.

 

filter1.png

Here my column name is UNR which I am checking not equal to null.

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up.

 

Thanks

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Advocate IV
Advocate IV

Hi @StevenF19 ,

 

just to clarify. Your list has 38 items or 38 columns?

 

Stefan

Highlighted
Community Support
Community Support

Hi @StevenF19 ,

 

Sorry for I'm afraid that there is any easy way to achieve the requirement and you could only create 38 if statement for all 38 columns.

 

Best regards,

Alice       

 

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

Highlighted
Advocate IV
Advocate IV

Hi @StevenF19;

 

please find attached a sample of how to iterate through the fields of a listitem:

My idea is to use the SharePoint API that give you access to nearly all artefacts.

 

1. Get Fields of the SP-List:

 

The Api-Call to get all fields of a list is like that:

_api/web/Lists/getbytitle('Testliste')/fields

Iterate Columns Get Fields.png

 

The JSON-Schema:

The JSON-Schema:
{
    "type": "object",
    "properties": {
        "d": {
            "type": "object",
            "properties": {
                "results": {
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "__metadata": {
                                "type": "object",
                                "properties": {
                                    "id": {
                                        "type": "string"
                                    },
                                    "uri": {
                                        "type": "string"
                                    },
                                    "type": {
                                        "type": "string"
                                    }
                                }
                            },
                            "DescriptionResource": {
                                "type": "object",
                                "properties": {
                                    "__deferred": {
                                        "type": "object",
                                        "properties": {
                                            "uri": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            },
                            "TitleResource": {
                                "type": "object",
                                "properties": {
                                    "__deferred": {
                                        "type": "object",
                                        "properties": {
                                            "uri": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            },
                            "AutoIndexed": {
                                "type": "boolean"
                            },
                            "CanBeDeleted": {
                                "type": "boolean"
                            },
                            "ClientSideComponentId": {
                                "type": "string"
                            },
                            "ClientSideComponentProperties": {},
                            "ClientValidationFormula": {},
                            "ClientValidationMessage": {},
                            "CustomFormatter": {},
                            "DefaultFormula": {},
                            "DefaultValue": {},
                            "Description": {
                                "type": "string"
                            },
                            "Direction": {
                                "type": "string"
                            },
                            "EnforceUniqueValues": {
                                "type": "boolean"
                            },
                            "EntityPropertyName": {
                                "type": "string"
                            },
                            "Filterable": {
                                "type": "boolean"
                            },
                            "FromBaseType": {
                                "type": "boolean"
                            },
                            "Group": {
                                "type": "string"
                            },
                            "Hidden": {
                                "type": "boolean"
                            },
                            "Id": {
                                "type": "string"
                            },
                            "Indexed": {
                                "type": "boolean"
                            },
                            "InternalName": {
                                "type": "string"
                            },
                            "JSLink": {},
                            "PinnedToFiltersPane": {
                                "type": "boolean"
                            },
                            "ReadOnlyField": {
                                "type": "boolean"
                            },
                            "Required": {
                                "type": "boolean"
                            },
                            "SchemaXml": {
                                "type": "string"
                            },
                            "Scope": {
                                "type": "string"
                            },
                            "Sealed": {
                                "type": "boolean"
                            },
                            "ShowInFiltersPane": {
                                "type": "integer"
                            },
                            "Sortable": {
                                "type": "boolean"
                            },
                            "StaticName": {
                                "type": "string"
                            },
                            "Title": {
                                "type": "string"
                            },
                            "FieldTypeKind": {
                                "type": "integer"
                            },
                            "TypeAsString": {
                                "type": "string"
                            },
                            "TypeDisplayName": {
                                "type": "string"
                            },
                            "TypeShortDescription": {
                                "type": "string"
                            },
                            "ValidationFormula": {},
                            "ValidationMessage": {}
                        },
                        "required": [
                            "__metadata",
                            "DescriptionResource",
                            "TitleResource",
                            "AutoIndexed",
                            "CanBeDeleted",
                            "ClientSideComponentId",
                            "ClientSideComponentProperties",
                            "ClientValidationFormula",
                            "ClientValidationMessage",
                            "CustomFormatter",
                            "DefaultFormula",
                            "DefaultValue",
                            "Description",
                            "Direction",
                            "EnforceUniqueValues",
                            "EntityPropertyName",
                            "Filterable",
                            "FromBaseType",
                            "Group",
                            "Hidden",
                            "Id",
                            "Indexed",
                            "InternalName",
                            "JSLink",
                            "PinnedToFiltersPane",
                            "ReadOnlyField",
                            "Required",
                            "SchemaXml",
                            "Scope",
                            "Sealed",
                            "ShowInFiltersPane",
                            "Sortable",
                            "StaticName",
                            "Title",
                            "FieldTypeKind",
                            "TypeAsString",
                            "TypeDisplayName",
                            "TypeShortDescription",
                            "ValidationFormula",
                            "ValidationMessage"
                        ]
                    }
                }
            }
        }
    }
}

2. Iterate through Fields:

 

The Api-Call to get the value of a field named 'title' is like that:

_api/web/Lists/getbytitle('Testliste')/items/getbyid(1)/Title

Iterate Columns Get Fields2.png

3. Parse dynamic JSON:

The result of the last operation is a JSON with the Fieldname as key:

{
 "d": {
      "File_x0020_Type": null
       }
}

I'm using some string operations to get extract the values:

 

Iterate Columns Get Fields3.png

 

Step one:
Delete the Text before the field name

 

Step two:
Delete the Text behind the field value

 

Step three:
extract the value

 

I'm sure that there will be some other issues while implementing the complete solution.

There are some internal fields that cannot be read in that way, so you have to implement some kind of error handling, but i hope you can start with that.

 

If this post was helpfull, it would be nice if you accept it as a solution for your problem.

 

Best regards
Stefan

View solution in original post

Highlighted

Thanks for the replies. Looks like there's no easy way to go about doing this but I'll give some of the solutions a shot and worst case scenario I'll have to use IF statements.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (8,215)