The Sharepoint activities don't make sense

Feedback Activities
, ,
1

I need to “Get List Items”, add queue items, then update a field on each list item (set a flag from false to true so we know it has been processed by the automation).

Office 365 - Classic

Why does the Get List Items activity return a datatable? How are we supposed to use Update List Item when it requires an item of type Office365SharepointListItem, and we have a datatable? The Get List Items activity should return an array of Office365SharepointListItem just like the Get Email List activity returns array of Office365Message.

Office 365

The For Each List Item activity doesn’t give us the ability to edit the name of the “currentitem” variable. This could be problematic in cases where nested loops are necessary.

The Get List Items activity again returns a datatable, not an array of Office365SharepointListItem.

The Update Single List Item activity uses the “List item ID” column to determine which to update. What if the ID field has been renamed? Will it still know which column is the ID field? Why doesn’t this activity just take in Office365SharepointListItem like the classic activity?

It also returns an item of DataRow for the item that was updated. Weird. Why don’t you give us MicrosoftOffice365ListItem?! It says in the property description “to be used further” but having it as a datarow is useless for using it further.

The “Office 365 - Classic” Get List Items activity’s “Columns To Retrieve” uses the display names of the columns, but the “Office 365” Get List Items activity uses the internal column name. This causes confusion and means I have to map and change the column names in the output datatable to use the display names in the queue item if I use the Office 365 version of the activity.

3

I did not use Sharepoint package so far but this sounds scary :frowning:

4

It gets worse. I decided I can just use the For Each List Item activity since it gives each currentItem as Office365SharepointListItem.

But the Filter is one of those where you made it so you have to hard-code it and I can’t just paste in a string. So it can’t be a dynamically created filter, which is what I need.

And the “Office 365” Get List Items works the same way, so again I can’t do a dynamic filter. If I use the “Office 365 - classic” Get List Items, now the column list has to be the Display Names, which is a problem too. What a mess.

So I thought ok I can use the “Office 365 - classic” For Each List Item since then I can provide a dynamic filter…but it doesn’t have a “columns to retrieve” property.

I’m sorry, but somebody didn’t think through these activities and they need a major overhaul.

What would be phenomenal is if we could point the Get List Items or For Each List Item activities at a Sharepoint view so that the filter is defined in Sharepoint, not in the activity.

5

And it gets worse.

Using the “Office 365 - Classic” For Each List Item because it allows for an expression to be used as the filter, the ListItem it gives you back doesn’t allow anything like ListItem(“fieldname”) or ListItem.Fields(“fieldname”) - you can only do ListItem.Fields(fieldIndex) where you provide an integer representing the index of the field.

6

I remember trying to use Lists a couple of years ago and indeed finding them absolutely appalling to work with, however they were also awful from a Microsoft perspective, either way the inconsistencies mention are quite frustrating, I imagine this is one of the earliest Integration Service activity packs and seems like it wasn’t thought through very well and contains alot of technical debt. I agree that this should be looked at and refreshed.

7

Hey @Jon_Smith and @postwick,

Thank you for sharing your feedback with us. As we built multiple activities in the past, the latest ones are from: “Microsoft/Office 365/SharePoint Lists”. These are the ones where we are going to bring immediate improvements and new features.

We have decided for an ease of use that all the SharePoint List activities to work with Dynamic DataTables/DataRows in mind as they are easy to manipulate. This should easy help you to pass data around and back.

Further, I will answer to each of your points above:

This is a known limitation for almost all the new For Each X activity, which will be fixed in future releases.

This is by design in order to make it easy to pass the information to an Excel Range.

By design, we are always working with the Technical Column Name and not the Column Display Names in order to cover the scenarios where the Column Display names is changed.

The purpose of the current design of Update Single List item is to be able to easy specify what Columns do you want to update. This avoid data manipulation the Office365SharepointListItem object.

It returns the updated List Item as DataRow for the same principle as the one above.

In case of Display Names vs Technical Column Names, we decide for ease of use to show as much as possible the Display names, but behind the scenes we are always working with Technical Column Names for reliability purposes and to make sure Display name changes don’t impact the workflow.

Indeed, this is a known limitation that you cannot have a dynamic filter. On the other hand, we have tried multiple filter combinations and we’ve observed that you cannot put filters on many SP Column Types, so we tried to restrict only to the filterable column types.
Still, you can pass variables in the filters to make it dynamic.

We wanted to do this, but unfortunately, the Graph API doesn’t offer this capability.

I’ve created a simple video explaining how you can easily do your scenario.

I hope this makes a bit of light regarding our decisions.

Thank you,

8

It’s just as easy to use an array of ListItem. And because we are interacting with those items via the Sharepoint API it makes it easier to do that. How about making the activities so they have both an array of ListItem and datatable outputs, giving us the option to use either or both?

Hand-holding the developer is not a good reason to limit how we can use the activities. Not being able to simply enter a filter as a string is a HUGE limitation. We are developers, we can manage whether something is filterable or not.

Not good enough, as we won’t always want to filter on the same columns.

My scenario is a little more complicated than my earlier description. I am creating an automation where we can use arguments to specify the Site URL and List ID, the filter, and the columns to return. This automation will collect the items from the list (arguments provided via triggers) and create queue items, then mark a particular boolean field true to record that the item was already picked up.

The limitations above made it so I have to jump through some pretty big hoops - like providing a list of columns as JSON to one of the arguments - to get the data we need.

9

I agree, that not having the possibility pass the entire filter as Variable it is a limitation. We will look into this and this option to all our new activities.

In the matter of array of ListItem as output can you help me with some benefits that you are seeing or scenarios which you can achive with array of ListItems and you cannot using the DataTable option?

Thank you,

10

It makes updating the list item simpler. Just use an assign, multiple assign, etc to change the value inside the list item, then use the update list item activity and pass the list item object. We don’t just pick up the items, loop through them, and process them…we also have to update them to mark them complete in sharepoint so they aren’t picked up again. Think of it just like the Get Emails activities…you don’t give us a datatable of emails, you give us an array of Office365MailMessage. Same applies here, we should have an array of Office365ListItem.

1 Like
11

One interesting thing I noticed was that in one of the objects (I don’t remember which) on of the properties was a datatable. That’s a pretty slick idea, so maybe you could set up the Office365ListItem so one of the properties is a datarow. Then we could get the values by doing CurrentListItem.Table(“Some Column”).ToString

12

Thank you for your feedback. I don’t want to promise anything for now but, we will see how we can incorporate your ideas in the Product.

If you have other feedback don’t hesitate to tell us.

Thank you,

1 Like
13

By the way, and this is an overall request for the Office 365 Scope…it would be great if we could output the connection into a variable the way we can with other activities (Terminal Session, for example) so that it can be reused later without having to reconnect. This output connection for 365 should include all the connection settings such as Integrated Windows vs Interactive Token, Application ID, Tenant ID, etc.

As an example, I have an automation that gets queue items and adds items to a Sharepoint list. In the initial “read config” step I connect and get the info for the Sharepoint List using Office 365 Scope. Then for each transaction I have to use Office 365 Scope again and configure it again. This means that any time we need to switch from Interactive Token (for testing in Studio) to Integrated Windows (for unattended testing/production) we have to remember to change the authentication type for every Office 365 Scope activity.

Also, having these selections be hard-coded pulldowns in the activities is a pain. If they were expressions, then we could use logic to determine if the connection should be Interactive Token vs Integrated Windows, and then we don’t have to remember to manually change every Office 365 Scope before publishing.

14

Chiming in here → please do not design the activities like that (I know I know, that’s a hard line statement).
If you really want to make it easy to go from ICollection<SharepointListItem> to a DataTable, add an extension method .ToDataTable for that one. It’s a one-way transformation anyway.

This is in general for the activities - they should first and foremost work effortlessly in their own primary context, and the easiest and safest way to do that is to have dedicated types. And if there are common adjacent domains they need to work with, make transformation easy.
[Sidenote: If I could wave a magic wand I’d make working on Excel be on dedicated types (like they are underneath) with translations to/from datatables for csv and similar compatibility, or at least with a wrapper class that translates to the DT underneath. Sure, the learning curve is a bit steeper, but once you grasp it it’s sooo much easier than datatables, and you can then do things like .IsFormula, check formats and so on]

Let’s put it this way - if I’m working in a SharePoint context and doing anything more meaningful than reading the data out, I want to work with SharePoint-specific objects so that I know, and the compiler knows, when an operation is valid.

Above especially applies when thinking of sticking DataTables somewhere. They’re absolutely painful to work with whenever you cross any scope boundaries, and even for a generic “data container” they have a ton of functionality that simply does not apply or actively gets in the way when using them as DTO’s.
And they’re too easy to break.
The use case mentioned here is to make it easy to work with Excel Range - ok, we needed to do that plenty. But in 9 cases out of 10, putting the whole item would be a total waste (and confusing later in the process), and while trimming down the view is simple, it’s very easy to break the “we use sharepoint based on datatables” with adding columns - you can’t protect against it, since it’s just a datatable, and you can’t ignore extra columns as that’s going to just create more issues than it solves.

For additional feedback:
We’ve had the displeasure of working with SharePoint way more than we’d like to, and while some things can be blamed on how the SharePoint API itself works, the way the UiPath activities worked with it didn’t help much. I’m not even sure how to solve this aside of a ground-up overhaul of these activity interfaces and locking them down in a similar way as working with emails works right now. Yes, it’s “limiting”, but that model just works.

And I’ll always take needing an extra transformation workflow/function to translate a specific object to a datatable (or the other way around), then to be stuck with needing to deal with datatables everywhere and never actually knowing what’s inside of it until runtime.
Needing to be specific in transformations and type safety is not a bad thing. The thousands upon thousands of “help, why this doesn’t work?” in JS and Python help sections everywhere proves that (and are the reason for TypeScript and static typing enforcers in Python, and why pretty much every new language that gains any traction is statically typed).
UiPath is pretty much pure .Net, and actively side stepping the absolutely beastly powerful type system that it has and the myriad of issues that it just makes not happen is a very dubious design decision.
In my opinion :wink:

1 Like
15

This is getting beyond maddening. I’m trying to do a VERY simple thing - get list items with a particular filter and update each item. I’m trying to use the non-classic activities because, as you said, using display names could be dangerous since those could change.

So I have Get List Items (from Microsoft/Office 365/SharePoint Lists) and I’m trying to set the filters. No matter what I do I can’t get a filter on a date column to work properly. I’ve tried…

StartDate less than or equal Today
StartDate less than or equal Now.ToString("yyyy-MM-dd")
StartDate less than or equal "'" + Now.ToString("yyyy-MM-dd") + "'"

And none of them work. I keep getting this error: Get List Items: Specified argument was out of the range of valid values. (Parameter ‘op’)

If I use the classic activity where we can just enter the Graph API filter explicitly, this works fine:

"fields/Active eq 'No' AND fields/StartDate le '" + Now.ToString("yyyy-MM-dd") + "' AND fields/EndDate gt '" + Now.ToString("yyyy-MM-dd") + "'"

So how am I supposed to do this in the newer activity with the filter-picker? What’s the correct syntax for filtering on a date column?!