1

I am trying to modify the new-entry form of a modern list in SharePoint Online by hiding or showing a field based on the value selected in a preceding field on the same form. Microsoft offers straightforward guidance on how to do this using a JSON snippet.

While they don't list a lookup column as an unsupported field type to use for this purpose, they don't provide guidance on how to use one in this way, either. And from my testing, you can't simply grab the value of a lookup field in the described way:

=if([$LookupField]=='Hourly', 'true', 'false')

If I use a choice field instead of a lookup field, this works fine. But doing some digging, in other contexts it looks like the value may be captured in a .lookupValue property, so I tried it this way:

=if([$LookupField.lookupValue]=='Hourly', 'true', 'false')

Still no luck. Does anyone know if it is possible to use the value from a lookup field in this way? And if so, what do I need to do to make the magic happen?

Improve this question

2 Answers 2

Reset to default
3

After debugging, I found that the validation function receives the lookup field values in this format: <Lookup ID>;#<Lookup Title>:

enter image description here

In your case, if the Hourly item has an id of 1, then the JSON formulata should be written this way:

=if([$LookupField]=='1;#Hourly', 'true', 'false')

Proof that it works

enter image description here

Update

If you want to avoid "hardcoding" specific lookup ID in the formula, you should use this formula to compare the lookup with a specific Text value:

=if(substring([$City],indexOf([$City],';#')+2,1000) == 'Toronto', 'true', 'false')
Improve this answer
3
  • Denis, I tried your recommendation and it does not seem to make any difference. Were you able to test this in practice, hiding and showing one form field based on the result returned by a lookup field? If so, I am stumped why it doesn't work for me.
    – Aylarja
    Commented Apr 15, 2021 at 19:00
  • Absolutely, I have tested my aswer before posting it.
    – Denis Molodtsov
    Commented Apr 16, 2021 at 22:37
  • 1
    Denis, I retried this and can confirm that you are absolutely correct. It does work. The need for the additional click may have given me some trouble, but I think I may have originally applied the wrong ID value to the ID/value combination. This is an impressive find. Thank you for sharing it.
    – Aylarja
    Commented Apr 19, 2021 at 16:25
1

Lookup fields are not supported in conditional formulas in SharePoint.

Source: can we show a column in a form based on a lookup field?

Update:

Lookup column examples are added to Microsoft official documentation now: Show or hide columns in a list or library form-Lookup column

Improve this answer
3
  • Thanks, Ganesh. Disappointing, but in alignment with what I am seeing. I have created a UserVoice entry to request this capability: sharepoint.uservoice.com/forums/329214-sites-and-collaboration/…
    – Aylarja
    Commented Apr 16, 2021 at 15:34
  • Please, see the screenshot in my answer.
    – Denis Molodtsov
    Commented Apr 16, 2021 at 22:37
  • 1
    Ganesh, after retesting, I can confirm that the approach Denis proposed does work. It does have a quirk of requiring an additional click outside the control in order to force the appearance of the hidden control. Ideally Microsoft will recraft this to allow a single click and the use of .lookupValue or .lookupId or something similar, but even as-is, this is a very useful capability.
    – Aylarja
    Commented Apr 19, 2021 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.