cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StevenF19
Level: Powered On

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
StefanR
Level 8

Re: Looping Through Columns to Create New Rows

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
Super User
Super User

Re: Looping Through Columns to Create New Rows

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!



StefanR
Level 8

Re: Looping Through Columns to Create New Rows

Hi @StevenF19 ,

 

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

 

Stefan

Community Support Team
Community Support Team

Re: Looping Through Columns to Create New Rows

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.

StefanR
Level 8

Re: Looping Through Columns to Create New Rows

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

StevenF19
Level: Powered On

Re: Looping Through Columns to Create New Rows

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 142 members 5,468 guests
Please welcome our newest community members: