1

I've finally convinced management to let me build some decent apps with SharePoint Online. I am trying out the stock issue tracker and I have a question about Column Validation.

We have a column called Error. My boss asked if we could make a formula that if the Error is "Backorder", then make the Column called "Cost" (which is a currency field) to = $0.00.

I've tried to research proper formula building but can't make much sense of it.

The column validation option is under lists and advanced...and looks like this:

Formulas

I've tried this but it said I can't change another column:

=IF([Reason]="Backorder", "$0.00")

Any tips or suggestions? Thanks...

2

As James said, a Validation Formula is intended to prevent the save of an item.

Two options for what you are describing:

  1. Create a Calculated Column that "corrects" the cost. (Maybe name this column Final Cost.)

    =IF( [Reason]="Backorder", 0, [Cost] )

The Calculated Column would display either a zero or the value entered in the Cost column.

  1. Create a SharePoint Designer workflow that checks the Reason Column and sets the value of Cost to 0 if Reason equals "Backorder".
|improve this answer|||||
  • Number One worked for me. Thank you SO much. New to SharePoint Online but I do like what I see so far. I just have to convince management. – Brian Jul 24 '18 at 19:46
0

What you're looking for is not Column Validation, but actually using a calculated column.

Column validation cant set the value of columns. It can only block the saving of an item if the validation formula fails.

|improve this answer|||||
  • Excellent. Thank you for helping me clear this up. – Brian Jul 24 '18 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.