SharePoint Online: column validation with another lookup column

Question

question

KevinFu-6427 asked Oct 18, '21 EmilyDu-MSFT commented Oct 25, '21

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

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

EmilyDu-MSFT · Oct 19, 2021 at 02:09 AM

@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 ·

Answer 1 · 2021-10-19T09:27:39.89Z

EmilyDu-MSFT answered Oct 19, '21 EmilyDu-MSFT commented Oct 25, '21

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

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.

3.Click File tab, save and publish the form.

4.Result.

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

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

KevinFu-6427 · Oct 19, 2021 at 10:11 AM

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

0 ·

image.png (16.9 KiB)

EmilyDu-MSFT KevinFu-6427 · Oct 20, 2021 at 10:23 AM

@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 ·

EmilyDu-MSFT · Oct 25, 2021 at 09:21 AM

@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 ·

Answer 2 · 2021-10-20T11:08:41.087Z

KevinFu-6427 answered Oct 20, '21 EmilyDu-MSFT commented Oct 25, '21

@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

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

EmilyDu-MSFT · Oct 21, 2021 at 09:54 AM

@KevinFu-6427

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

0 ·

KevinFu-6427 EmilyDu-MSFT · Oct 25, 2021 at 09:28 AM

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

0 ·

EmilyDu-MSFT KevinFu-6427 · Oct 25, 2021 at 10:11 AM

@KevinFu-6427

You're welcome.

0 ·

question