sharepoint online – Update People picker column using REST API in Power Automate

Using REST API, You can update People or Group field by setting field with user or group ID.

Considering field name is AssignedTo, the format for user field value:

Single-valued user field: 'AssignedToId' : 1

Multi-valued user field: 'AssignedToId' : { 'results': [1, 2, 3] }

Where 1, 2, 3 are ID of users/groups.

Example:

enter image description here

References:

  1. Update a people field in a SharePoint List using REST API using Power Automate
  2. HOW TO UPDATE THE SHAREPOINT PEOPLE PICKER FIELD USING POWER AUTOMATE

Sharepoint Column Formatting – SharePoint Stack Exchange

Stack Exchange Network


Stack Exchange network consists of 176 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Sharepoint Online List column link to folder in document library

I’m trying to have a column in my list that links to a folder in my document library which has a name that matches the Quote number of the item.

So my list has the columns ‘Name’, ‘Customer’, ‘Quote Number’
and inside my document library, inside a folder called Quotes, I have a folder for each quote number. How can I link the two together?

python – Speed up updating column in SQL command

I am trying to speed this code up. I have a table (Table1) that has over 2.5 million lines in it. I am trying to use regex to extract a specific value from the NoteText column. If it finds a match, it then pulls the rest of the info, and searches through another and then updates the value in that column by either appending it or just setting the column value as it. Is there anyway to speed up these SQL commands?

masterDBConn = sqlite3.connect('MasterDB.db')
masterCursor = masterDBConn.cursor()


masterCursor.execute("SELECT PartTypeNbr, IntchNbr, AlphaExtension, NoteText FROM Table1 ORDER BY PartTypeNbr ASC")
data = masterCursor.fetchall()
regex = r'(?<!S)(?=w{5})(^Wd)*dw*(?!S)'

start_time = time.time()
for row, i in enumerate(data):
    val = re.search(regex, i(3))
    print('1---%s seconds ---' % (time.time() - start_time))
    if val:
        val = val.group()
        partType = i(0)
        inchNbr = i(1)
        alpha = i(2)
        value1 = str(partType).strip() + '-' + str(inchNbr).strip() + alpha.strip()
        print('2---%s seconds ---' % (time.time() - start_time))
        masterCursor.execute("SELECT VAL FROM MasterV2 WHERE InventoryNbr LIKE ?", (value1 +'%', ))
        print('3---%s seconds ---' % (time.time() - start_time))
        checker = len(masterCursor.fetchall())
        if checker > 0:
            print('5---%s seconds ---' % (time.time() - start_time))
            masterCursor.execute("UPDATE MasterV2 SET VAL = VAL || ', ' || ? WHERE InventoryNbr = ? AND VAL NOT LIKE ?", (val, value1+'%', '%'+val+'%'))
            print('6---%s seconds ---' % (time.time() - start_time))
        else:
            print('7---%s seconds ---' % (time.time() - start_time))
            masterCursor.execute("UPDATE MasterV2 SET VAL = ? WHERE InventoryNbr = ?", (val, value1+'%'))
            print('8---%s seconds ---' % (time.time() - start_time))
    if row % 1000 == 0:
        print(str(row) + '---%s seconds ---' % (time.time() - start_time))
masterDBConn.commit()

Here is the output of my print statements, so you can see the speed.

1---0.0 seconds ---
2---0.0 seconds ---
3---0.001999378204345703 seconds ---
5---0.12900185585021973 seconds ---
6---0.12900185585021973 seconds ---
1---0.12900185585021973 seconds ---
2---0.12900185585021973 seconds ---
3---0.13000202178955078 seconds ---
5---0.2569997310638428 seconds ---
6---0.2569997310638428 seconds ---
1---0.2569997310638428 seconds ---
2---0.2569997310638428 seconds ---
3---0.2579991817474365 seconds ---
5---0.3839995861053467 seconds ---
6---0.38499951362609863 seconds ---
1---0.38499951362609863 seconds ---
2---0.38499951362609863 seconds ---
3---0.3859994411468506 seconds ---
5---0.5159988403320312 seconds ---
6---0.5159988403320312 seconds ---

2013 – Unable to reference a column by its Internal name. Column does not exist. It may have been deleted by another user

Today I created a new site column named “Business Category” inside my issue tracking list on SharePoint 2013 server. The column type is a dropdown list. Now the internal name for the column is “Business_x0020_Category”, as shown on the URL where the encoded name is “Business%5Fx0020%5FCategory” :-
enter image description here

Now I try running this command to update the value of this column to all the items inside the list:-

$web = get-spweb "http://****/***/**/"
$list2 = $web.lists("Tracking")
foreach ($i in $list2.items)
{
$a = $i("ID");

$sourceItem = $list2.items.GetItemById($a);

$sourceItem("Business_x0020_Category") = "User Delivery";

$sourceItem.Systemupdate();


}

but I got this error inside the PowerShell command:-

Column 'Business_x0020_Category' does not exist. It may have been deleted by
another user.
At line:5 char:1
+ $sourceItem("Business_x0020_Category") = "User Delivery";
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) (), ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException

so can anyone advise on this, please ?

and when I run the following command

$web = Get-SPWeb  $web = get-spweb "http://****/***/**/"
$list = $web.lists("Tracking")
$list.fields | select InternalName, StaticName | sort StaticName | ft -AutoSize

the column Business_x0020_Category is listed in the list:

PS C:Windowssystem32> $list.fields | select InternalName, StaticName | sort StaticName | ft -AutoSize

InternalName                     StaticName
------------                     ----------
_CopySource                      _CopySource
_EditMenuTableEnd                _EditMenuTableEnd
_EditMenuTableStart              _EditMenuTableStart
_EditMenuTableStart2             _EditMenuTableStart2
_HasCopyDestinations             _HasCopyDestinations
_IsCurrentVersion                _IsCurrentVersion
_Level                           _Level
_ModerationComments              _ModerationComments
_ModerationStatus                _ModerationStatus
_UIVersion                       _UIVersion
_UIVersionString                 _UIVersionString
AppAuthor                        AppAuthor
AppEditor                        AppEditor
AssignedTo                       AssignedTo
AssignID                         AssignID
Attachments                      Attachments
Author                           Author
BaseName                         BaseName
Business_x0020_Category          Business_x0020_Category
Business_x0020_Development_x0020 Business_x0020_Development_x0020_Tracking_x...
businessfunctionsInitials        businessfunctionsInitials
Category                         Category
Comment                          Comment

so can anyone advise what is going on?

list manipulation – Replacing a random ith row and column from a matrix

Currently I am trying to delete a randomly-chosen $i^{th}$ row and column from a square $n times n$ matrix $A$. So far I come up with the following code:

Drop[A, {RandomInteger[{1, 400}]}, {RandomInteger[{1, 400}]}]

The problem with this command is that the random integer for {i} is not the same as the random integer for {j}.

Is there a way of making them consistent, so that I drop the ith row and corresponding column while maintaining the randomness of selecting $i$?

And if the aim was to not delete the row and column entirely but to replace all their elements with, say 0, how would you go about it?

Thank you.

materialized view – What is the correct way to do a computed column with complex logic with performant sorting and filtering at scale in MS SQL Server?

I have the Quantity indexed on both tables because I obviously wish
to sort and filter on this column, but of course the index doesn’t
help if I want to sort and filter on vwThingAugmented.

Based on your view and the comment on filtering I am going to assume queries like this:

SELECT * 
FROM  dbo.vwThingAugmented
WHERE Quantity = 5;

Where there is a filter operator late in the execution plan:

enter image description here

One of the things you could try, depending on the size of the tables and the type of filters you do would be to create a view with a UNION an INNER JOIN and a NOT EXISTS instead of a LEFT JOIN

CREATE VIEW dbo.vwThingAugmented_2
AS SELECT t.ID
, Quantity = t.Quantity + a.Quantity
, AdditionGroupID
FROM dbo.Thing t
INNER JOIN dbo.AdditionGroup a ON  t.AdditionGroupID = a.ID
UNION ALL
SELECT t.ID
, Quantity = t.Quantity
, AdditionGroupID
FROM dbo.Thing t
WHERE NOT EXISTS (SELECT * FROM dbo.AdditionGroup a WHERE  t.AdditionGroupID = a.ID);

You do get two accesses to dbo.Thing and dbo.AdditionGroup in this case, YMMV depending on the type of query you run against the view. You could try running the same query again:

SELECT * 
FROM dbo.vwThingAugmented_2
WHERE Quantity = 5;

This removes the filter operator but you do have double index access with a concatenate.
enter image description here

And you still have residual predicates on both parts of the query,

Because the nonclustered index is not covering:

DROP INDEX IX_Thing_Quantity ON dbo.Thing;
CREATE INDEX IX_Thing_Quantity ON dbo.Thing (Quantity, ID) 
INCLUDE (AdditionGroupID);

Where now the bottom part is improved to use the nonclustered index (without a key lookup) instead of the clustered index :

enter image description here

Lastly, as mentioned by you, you could add an indexed view to the first part of the view. Remember to use NOEXPAND for best performance, even in Enterprise edition.

sql – Match column name with data from other table

I have dynamically created a temp table #temp1 that create column dynamically based on a json string. The table will look like as below

#temp1

| ID | FRUIT | VEGE |
|:---- |:------:| -----:|
| 1  | A   | B |
| 2  | C   | D |

I would like to fill in the value column in result table based on the #temp1 table

result table

| ID | type_id | value |
|:--- |:---:| ---:|
| 1  | 1   | A |
| 2  | 2  | B |
| 3  | 1   | C |
| 4  | 2   | D |

where the type_id can get from another table that specified the type and its id

type table

| ID | type_id | type |
|:--- |:---:| ---:|
| 1  | 1   | FRUIT |
| 2  | 2  | VEGE |

Please help!

sharepoint online – The hidden ‘Order’ column in Tasks list not being migrated

So for classic Tasks list, you are able to manual sort and indent items. This sorting is persistent and not personal. List View sorting is set to None.

Manual Sort Buttons

Performing a migration using SP Migration Tool from SP2013 to SPO, classic task list functionality is still fully available.

  • Manual indent of items is migration
  • Manual sort order of items is NOT migrated (it defaults to sorting by list ID)

I have found a hidden ‘Order’ column which isn’t visable anywhere other than opening another column settings on the list and changing the URL to &Field=Order

  • this Order column is not part of any content type or visible via SPD
  • this column is present in the migrated list, but it not populated with data from the source

Anyone know any more about this column, can I extract data somehow?