SharePoint Online: column validation with another lookup column

Shirong Fu (付仕荣) 46 Reputation points
2021-10-18T09:32:56.267+00:00

I have a SharePoint list which have two columns named Province and Hospital, Province is a single text column and Hospital is a lookup column.

The request: When Hospital is empty, Province must has value.

For now, I tried to create another single text column Hide_Hospital and using the Json formatting with below Json to get the value of Hospital.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"flex-wrap": "wrap",
"display": "flex"
},
"txtContent": "[$Hospital.lookupValue]"
}

And then use formula in validation settings with below formula:
IF(ISBLANK([Hide_Hospital]),IF(ISBLANK(Province),FALSE,TRUE),TRUE)

But when I create a new item in the list, the validation was did before getting the value of Hospital, is there any suggestion here?

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,600 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emily Du-MSFT 41,771 Reputation points Microsoft Vendor
    2021-10-19T09:27:39.89+00:00

    anonymous user

    Per my test, I could reproduce the issue in my end.

    When you add new items in the New form, validation will work before JSON formatting.

    As a workaround, you could add new items through quick edit, validation will work after JSON formatting in the quick edit.

    1.Intergrate -> PowerApps -> Customize forms.
    142092-1.png
    2.Select the DateCardValue of Hide_Hospital column -> Advanced -> Unlock to change properties -> Set default as DateCardValue.Selected.Value. Note: In this formula DateCardValue.Selected.Value, you should replace the DateCardValue name of Hospital column. In my end, this formula should be set as DateCardValue3.Selected.Value. DateCardValue3 is the name of DateCardValue of Hospital column.
    142093-2.png

    141980-3.png

    142045-4.png
    3.Click File tab, save and publish the form.
    142112-5.png
    142076-6.png
    4.Result.
    142046-1.gif

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Shirong Fu (付仕荣) 46 Reputation points
    2021-10-20T11:08:41.087+00:00

    @Emily Du-MSFT

    Thanks for your answers, but it looks like Powerapps is not working for me, I need use quick edit as well. Do you have any other suggestions on this? Much appreciate.

    My original request is:

    1. There are 2 columns named Province and Hospital.
    2. Hospital is lookup column.
    3. If Hospital is blank, Province is required.
    4. All of create, update and quick edit should be working.