none
Matching an integer field to a list of acceptable values in SharePoint RRS feed

  • Question

  • This InfoPath form has a Whole Number field that I want to validate against a SharePoint list of ID numbers, so that if someone enters a 'junk' value like 00000 or 12345, the form won't accept it. Is there a way to do that when the field uses manual input? Any user who fills out the form ought to know their own ID number, and I'm concerned that a drop-down field might end up being inconveniently long.

    Wednesday, May 15, 2019 2:27 PM

All replies

  • Hi Daniel,

    You can add rules to a particular input box with InfoPath.

    1. Open your list form with InfoPath, select the Number filed.

    2. Select "Add Rule" in PROPERTIES tab on the ribbon.

    3. Select a condition for this field and click "Show Validation Error" in the Actions menu. In this scenario, I selected "If Is Equal To...".

    4. Type a number in the input box, e.g. 12345, then click OK.

    5. Modify Conditions and ScreenTip under Rules. 

    6. Save and publish the form. The form will return a message when user types an invalid number in this field.

    Here’s your reference.

    Add rules for validation.

    https://support.office.com/en-us/article/add-rules-for-validation-fa530b5c-419f-4c03-a85f-d7f96cb9de95

    Best regards,

    Chelsea Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Thursday, May 16, 2019 8:10 AM
  • Hi Chelsea,

    The issue is that people are entering random numbers into the ID field to bypass its required-ness instead of a correct ID number. I can't predict what sort of values they'll enter like that -- it might be 12345, it might be 0000, it might be a value like 53289 or 48232, which I came up with by randomly rolling my fingers across the numpad. Most valid IDs start with 02, but not all of them, so I can't use that as a rule either.

    My thought is to create a list of valid IDs and compare whatever they enter to the list. Can that be done, or is there another way to do it?

    Thanks,

    Daniel


    • Edited by Daniel Hwu Thursday, May 16, 2019 1:06 PM
    Thursday, May 16, 2019 1:00 PM
  • Hi Daniel,

    Your request is achievable with Lookup column.

    1. Create a list of valid IDs. In this scenario, I created a list called “Identity” and a column named “Number” in this list to store the ID values. 

    2. In the list which requires ID information, create a Lookup column and set it to get information from List “Identity” and Column “Number”.

    3. When a user type ID in this Lookup column, if the value cannot be found in “Number” column of “Identity” List, the field will show an error message and prevent the item from being saved.

    Best regards,

    Chelsea Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Friday, May 17, 2019 7:56 AM
  • Hi Daniel,

    Is there any progress on this issue?

    If you find any replies helpful to you, please remember to mark then as answers. 

    If you have solved the problem yourself, you can reply the solution and mark it as the answer. 

    It will help others who meet the similar question in this forum. 

    Thank you for your understanding.

    Best regards

    Chelsea Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Monday, May 20, 2019 1:41 AM
  • Chelsea,

    Is it possible to tie the lookup column to an external SharePoint list? That way it will be possible for someone to maintain it without needing to edit the form itself.

    Thanks,

    Daniel

    Monday, May 20, 2019 2:33 PM
  • Hi Daniel,

    It is possible to use lookup column to get data from External SharePoint List. 

    You need to create an External Content Type and then create an External List. Create a new lookup column and set it to get information from the External List.

    Here’s your reference.

    Create External List in SharePoint 2013.

    Best regards,

    Chelsea Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Tuesday, May 21, 2019 9:47 AM
  • That will work. Thank you.
    Wednesday, May 22, 2019 1:20 PM
  • Hi Daniel,

    I'm glad it works for you.

    If you find any replies helpful, please remember to mark them as answers. It will help others with the same request in this forum.

    Thank you for your understanding.

    Best regards,

    Chelsea Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Thursday, May 23, 2019 1:21 AM