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!
Hans de JonghAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.