question

KevinFu-6427 avatar image
0 Votes"
KevinFu-6427 asked EmilyDu-MSFT commented

SharePoint Online: column validation with another lookup column

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?

office-sharepoint-online
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@KevinFu-6427

Thank you for your question. 

We are currently looking into this issue and will give you an update as soon as possible.

Thank you for your understanding and support.

0 Votes 0 ·
EmilyDu-MSFT avatar image
0 Votes"
EmilyDu-MSFT answered EmilyDu-MSFT commented

@KevinFu-6427

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.



1.png (19.9 KiB)
2.png (11.5 KiB)
3.png (5.4 KiB)
4.png (3.0 KiB)
5.png (16.6 KiB)
6.png (21.7 KiB)
1.gif (21.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It looks like quick edit is not working, the validation also works before JSON formatting.
141670-image.png


0 Votes 0 ·
image.png (16.9 KiB)

@KevinFu-6427

Apologies as I rushed writing the above and introduced confusion. In the quick edit, validation will also work before JSON formatting.

You could use PowerApps to customize New form, let Hide_Hospital column get values from Hospital column automatically. After that, validation will work when you save the newly created item. But even if the validation is working, when the validation fails, no new item can be added but no error message will appear.

I have update the answer, please check.

0 Votes 0 ·

@KevinFu-6427

I’m checking how the things are going on about this issue. Whether the answer helps you?

If there is any problem with this issue, feel free to let me know.

0 Votes 0 ·
KevinFu-6427 avatar image
0 Votes"
KevinFu-6427 answered EmilyDu-MSFT commented

@EmilyDu-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.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@KevinFu-6427

In the quick edit, there is no option to achieve this.

0 Votes 0 ·

OK, thanks for your help on this question, I will try on other options.

0 Votes 0 ·

@KevinFu-6427

You're welcome.

0 Votes 0 ·