locked
Calculated Column Formula Help RRS feed

  • Question

  • Hi im trying to create a formula for a calculated column which Ive called "Customer" taking its valve from combining a  "First Name" and "Last Name" column OR a "Company" column if its value is filled in. What I would like to happen is when the Company field is used for the "Customer" field to be filled in with the "Company" field, if no company is filled in then I would like "First Name" + "Last Name" to be what the "Customer" field reads.

    Finally if its possible I also would like the "First Name" and Last Name" fields not to be be required (Optional) to be filled in if the company field is used.

    Any help on this would be greatly appreciated,

    Chris L

    Friday, March 19, 2010 3:54 AM

Answers

  • Ok, it just needs a concatenate function in the part where you combine first name and last name:

    =IF([Customer]>0,[Customer],(CONCATENATE([FirstName]," ",[LastName])))

    It worked in my system.


    SharePoint Architect || My Blog
    • Marked as answer by Chrisls Wednesday, March 24, 2010 4:43 AM
    Wednesday, March 24, 2010 3:24 AM

All replies

  • You might be able to do the conditional required fields by using the new built-in column validation, but I haven't tried that yet.  If it doesn't do that, then you'll need to go straight to a custom list form with InfoPath or maybe with a custom ASPX form.  With a custom InfoPath list form, you can add data validation conditions to say "If Customer is blank and First Name is Blank, then error," but doing this requires SharePoint Server 2010 Enterprise.

    As for the calculated value, it will probably work with an IF statement - something like this:

    IF([Customer]>0,[Customer],([FirstName]" "[LastName]))

    I don't think IF statements can do "is not equal to," but if you know how then use that instead of ">0" but basically the formula is saying that if Customer is not empty, use Customer, else use FirstName and LastName with a space between them.


    SharePoint Architect || My Blog
    Friday, March 19, 2010 2:38 PM
  • I used a formula like this in a SharePoint 2007 project:

    =IF([Customer]="",[FirstName] [LastName],[Customer])

    Be aware that this formula might have to be different based on the locale of the site. For instance if the locale of the site would be Dutch the , should be ;.


    Mirjam
    sharepointchick.com
    Sunday, March 21, 2010 12:48 AM
  • Same formula in reverse, isn't it?


    SharePoint Architect || My Blog
    Sunday, March 21, 2010 12:52 AM
  • Yeah, just using ="" instead of >0. I have no idea if it makes a difference...
    Mirjam
    sharepointchick.com
    Sunday, March 21, 2010 12:53 AM
  • Both give a syntax error when I try to use them,

    Thanks for the help so far though,

    Chris L

    Tuesday, March 23, 2010 11:17 PM
  • Did you copy and paste what we gave, or did you build it manually in your environment to ensure the field names were correct?  If it's truly OUR syntax and not the field names, then you can test it by using Excel and replacing the field names with cell names (i.e. A1, B1, C1).  The syntax that works for Excel will work in SharePoint calculated columns - you just have to switch out the variables.
    SharePoint Architect || My Blog
    Wednesday, March 24, 2010 12:33 AM
  • Yes I put in my own field names and I know that is not the issue as I got a "Syntax Error" not a "The formula refers to a column that does not exist" error.

    Chris L

    Wednesday, March 24, 2010 3:05 AM
  • Ok, it just needs a concatenate function in the part where you combine first name and last name:

    =IF([Customer]>0,[Customer],(CONCATENATE([FirstName]," ",[LastName])))

    It worked in my system.


    SharePoint Architect || My Blog
    • Marked as answer by Chrisls Wednesday, March 24, 2010 4:43 AM
    Wednesday, March 24, 2010 3:24 AM
  • Thanks for the help, worked great

    Chris L

    Wednesday, March 24, 2010 4:00 AM
  • Question answered?
    SharePoint Architect || My Blog
    Wednesday, March 24, 2010 4:08 AM
  • Aye sorry, marked now
    Wednesday, March 24, 2010 4:44 AM