none
Outlook Custom Form Validation Forumla Assistance RRS feed

  • Question

  • Hello All,

    I need some assistance with Outlook Custom Form - Field Validation Formula.  I've put quite a bit of time into developing the form and I'm 99% complete with it, however I seem to have some issue with one or two validation formula that may be a little more complex than what most users utilize.

    Given that the individual validations seem to be linear in nature (executed in order), I may encounter an additional problem once I have the first one ironed out, although during my preliminary testing, it is only the one formula that is giving me problems.  I'll provide as much detail as possible, hopefully someone has a quick insight as to where I've gone wrong.

    The form overall is designed to be a data validation tool for our staff to check new product pricing against existing product pricing, so they manually collect data from 2 sources and the form is confirming whether the changes are within acceptable range.  We are using an Outlook form because we need to assign/monitor these as tasks.

    The first validation that I'm having a problem with is this:
    The form contains (among others) 3 fields to calculate a purchase-price variance.
    The form also contains 2 check-boxes indicating if the data is new or an update.
    The first 2 fields, ([old cost] and [new cost]) are both filled in manually.
    The next field is not editable and calculates the variance between the first 2 fields:  The value displayed is of type [Formula] and is calculated as: Format(([NewCost] - [OldCost]) / [OldCost], "Percent") - this correctly returns the % variance between the 2 numbers entered in fields 1&2.
    The validation on this field needs to return TRUE based on the following logic:
    --If the 'New Data' checkbox is checked, return True  OR  If the 'Update' checkbox is checked, the calculated value in this field must be between -15% and 15%.

    ([IsNew] = True) or (([IsUpdate] = True) and (([Variance] >= -15) and ([Variance] <= 15)))

    Since the value formula for this field includes the 'Format' function, I'm assuming the formatting as a percentage is not applicable to the validation, so the validation only focuses on the numeric value and does not include the %.  I've tried this both ways and still seem to have problems.

    I'm hoping that I'm just making a simple mistake with the validation formula and this can be easily corrected.  I've thought of a few other ways to approach the validation, all of which become more complex.  Hopefully someone can pick out quickly what I'm doing wrong and assist.

    Much appreciate any insight,

      Thank you in advance...


    The solution is always the last thing you look at... -M

    Friday, September 29, 2017 4:59 PM

All replies

  • Hi JustUnplugIt,

    Welcome to the Microsoft Office for IT Professionals Outlook forum. This forum is for general questions and feedback related to Outlook. Since your query is about developing issues involve Outlook, I'll move the thread to the following dedicated MSDN forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=outlookdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Monday, October 2, 2017 7:03 AM
  • Thank you, I was unsure as to where would be appropriate, there wasn't a forum for Outlook Programming...

    That said, I may have made some headway with the actual validation logic, however I'm still running into an issue when the form is loaded, the validation seems to kick off immediately and pop-up my validation messages multiple times.  I'm thinking that it may have something to do with the fact that some fields required for the validation calculation are blank initially.  I'll try putting in a zero value into those fields as an initial value, but I may need to be able to have the validation logic work around the fact that some initial field values may be NULL.

    I've read a bunch online about validation logic but not seen anywhere this type of thing mentioned, so I'm not sure it's possible...

    Thanks,


    The solution is always the last thing you look at... -M

    Monday, October 2, 2017 4:20 PM
  • Hello,

    How do you apply "([IsNew] = True) or (([IsUpdate] = True) and (([Variance] >= -15) and ([Variance] <= 15)))"?

    Do you have two field named as IsNew/IsUpdate? 

    Variance has been formatted as string using Format function, so I think we could not use ">= -15" to compare it.

    >>the validation seems to kick off immediately and pop-up my validation messages multiple times. 

    Where do you add the validation? I fail to get any validation error.

    Please share detail repro steps here.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 4, 2017 6:18 AM
    Moderator