Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 113
  • Last Modified:

Calculated field sharepoint based on 2 fields

Hello,

I need to create a status if an list item is ready for invoicing.


I need a calculated field (field3) with the following characteristics

If field1 has a value then field3 should be 0
if field2 = "Cancelled" then field3 should be 0
If field1 has no value then field3 should be 1

so calculated field3 should be either 0 or 1

thanks!
0
Hans de Jongh
Asked:
Hans de Jongh
  • 2
2 Solutions
 
Hans de JonghAuthor Commented:
oeps sorry :)
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Oeps, what? Why Powershell? Why Excel?

You can create an IF statement following syntax similar to Excel.

=if(isblank(field1),1,if(or(field2="cancelled,not(isblank(field1))),0,"something else"))

cheers, teylyn
0
 
Raheman M. AbdulCommented:
Using powershell to update the field3 value based on the values of field1 and field2:

Replace the listname in the code:

#Add SharePoint PowerShell Snapin which adds SharePoint specific cmdlets
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
 
#Variables that we are going to use for list editing
$webURL = <a href="http://portal.contoso.com">http://portal.contoso.com</a>
$listName = "Demo List"
 
#Get the SPWeb object and save it to a variable
$web = Get-SPWeb $webURL
 
#Get the SPList object to retrieve the "Demo List"
$list = $web.Lists[$listName]

#Get all items in this list and save them to a variable
$items = $list.items
 
#Go through all items
foreach($item in $items)
{
if(   ($item["field1"] -ne $null) -or ($item["field2"] -eq "Cancelled" ))
{
$item["field3"] = 0
}
else
{
$item["field3"] = 1
}
 
#Update the item
$item.Update()
}

Open in new window

0
 
Hans de JonghAuthor Commented:
thanks both, my question was not clear enough thats why i split the points..
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now