Help with automation that uses a SharePoint list value in a Column called Status to proceed the automation

Help Activities
, ,
1

Can anyone confirm how SharePoint lists columns are ordered?

I have an automation that loops through a SharePoint list and if the value of the column called Status is NEW then it processes the data for that row into a website portal.

The issue that i’m encountering if the IF statements is checking field using a column index - ListItem.Fields(22).Value.ToString = “NEW” - for this check. But when I loop through this list, this column index does not always refer to the Status column. Sometimes it reads the value 1 column or even 2 columns before Status column in the list.

I have no idea how i can make this statement more reliable.

This is the list on the SharePoint site:

image
image1377×41 3.63 KB

This is the contents of the variable that the SharePoint list is populated into:

image
image833×501 76.7 KB

In a test workflow I read this same SharePoint list and logged the value of field 22 and 23 to see what they contained, and this is where i found that the values don’t always contains the same value.

image
image795×703 28 KB

The first and second log messages contain this statement:
ListItem.Fields(22).DisplayName.ToString + " " + ListItem.Fields(22).Value.ToString
ListItem.Fields(23).DisplayName.ToString + " " + ListItem.Fields(23).Value.ToString

and the IF statement condition is:
(ListItem.Fields(23).Value.ToString.Equals(“New”)) Or (ListItem.Fields(22).Value.ToString.Equals(“New”))

This is the output of this test (ignore the Error and warning, I used them for the colour) :
04/12/2024 11:44:12 => [Info] TestWorkflow execution started
04/12/2024 11:49:15 => [Info] Completion Date and Time (*required) 01/05/2024 17:00:00
04/12/2024 11:49:18 => [Info] Status New
04/12/2024 11:49:30 => [Warning] Yes
04/12/2024 11:49:33 => [Info] Completion Date and Time (*required) 01/03/2024 17:00:00
04/12/2024 11:49:34 => [Info] Status Failed
04/12/2024 11:49:48 => [Error] No
04/12/2024 11:49:50 => [Info] Completion Date and Time (*required) 01/05/2024 17:00:00
04/12/2024 11:49:51 => [Info] Status Failed
04/12/2024 11:49:52 => [Error] No
04/12/2024 11:49:54 => [Info] Completion Date and Time (*required) 01/08/2024 21:00:00
04/12/2024 11:49:54 => [Info] Status Failed
04/12/2024 11:49:56 => [Error] No
04/12/2024 11:49:59 => [Info] Completion Status (*required) E-LEARNING-COMPL
04/12/2024 11:50:00 => [Info] Completion Date and Time (*required) 10/04/2023 17:00:00
04/12/2024 11:50:24 => [Error] No
04/12/2024 11:50:26 => [Info] Completion Status (*required) E-LEARNING-COMPL
04/12/2024 11:50:27 => [Info] Completion Date and Time (*required) 10/04/2023 17:00:00
04/12/2024 11:50:37 => [Error] No
04/12/2024 11:50:47 => [Info] Completion Status (*required) ILT-COMPLETE
04/12/2024 11:50:47 => [Info] Completion Date and Time (*required) 01/16/2024 15:30:00
04/12/2024 11:50:59 => [Error] No
04/12/2024 11:51:01 => [Info] Completion Date and Time (*required) 12/12/2023 08:00:00
04/12/2024 11:51:02 => [Info] Status New
04/12/2024 11:51:03 => [Warning] Yes
04/12/2024 11:51:06 => [Info] Completion Status (*required) E-LEARNING-COMPL
04/12/2024 11:51:06 => [Info] Completion Date and Time (*required) 12/29/2023 19:00:00
04/12/2024 11:51:40 => [Error] No
04/12/2024 11:51:48 => [Info] Completion Status (*required) ILT-COMPLETE
04/12/2024 11:51:48 => [Info] Completion Date and Time (*required) 03/06/2024 11:00:00
04/12/2024 11:51:48 => [Error] No
04/12/2024 11:51:48 => [Info] Completion Status (*required) ILT-COMPLETE
04/12/2024 11:51:48 => [Info] Completion Date and Time (*required) 03/11/2024 10:00:00
04/12/2024 11:51:48 => [Error] No
04/12/2024 11:51:48 => [Info] Completion Status (*required) E-LEARNING-COMPL
04/12/2024 11:51:48 => [Info] Completion Date and Time (*required) 11/30/2023 19:00:00
04/12/2024 11:51:48 => [Error] No
04/12/2024 11:51:48 => [Info] Completion Date and Time (*required) 02/06/2024 17:00:00
04/12/2024 11:51:48 => [Info] Status New
04/12/2024 11:51:48 => [Warning] Yes
04/12/2024 11:51:48 => [Info] Completion Status (*required) E-LEARNING-COMPL
04/12/2024 11:51:48 => [Info] Completion Date and Time (*required) 03/20/2024 12:20:00
04/12/2024 11:51:48 => [Error] No

Am I missing something obvious, could I use the column name instead of the index? Or is there a better way of making sure that its always the Status column value always checked?

2

@ParkerDH,

Instead of iterating through all the lists try to filter only New one by setting property Filter= "fields/Status eq 'New'"

This should give you New status lists only as you required.

Documentation here: Activities - For Each List Item

Thanks,
Ashok :slight_smile:

3

Thanks Ashok, I’ll give this a try and let you know the outcome.

This is an automation was developed by a colleague so i’ve been trying to understand their logic along with fixing the an issue with the code. Then i encountered this issue.

1 Like
4

Thanks you for everyone’s help. I Found a statement that works and dynamically finds the column heading Status and then retrieves the value of the column. This approach avoids relying on the field index and after much testing works really well.

ListItem.Fields.First(x => x.DisplayName.Equals(“Status”)).Value.ToString().Equals(“New”)

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.