Conditionally show/hide field based on content of field in EXTENDED lookup column

Question

Problem: I want to show/hide a field based on the content of a field in an EXTENDED lookup column.

I have the following two lists:

LIST 1: "*Opgaver*"
 COLUMN: "*Opgave*"
 COLUMN: "*Område*"

LIST 2: "*Tidsregistrering*"
 COLUMN: "Aktivitet"
 COLUMN: "Underområde"
 COLUMN: "Opgave" (lookup)
 COLUMN: "Opgave: Område" (extended lookup based on lookup)

I use Power Automate to ensure that all involved columns are available in text format (just a sidenote to avoid confusion).

In list 2 I only want to show the field pertaining to "Underområde" if the field in column "Opgave: Område" in list 2 has a certain content. I have tried to use the following formular from Microsofts learning material:

=if(substring([$Opgave: Område],indexOf([$Opgave: Område],';#')+2,1000) == 'Toronto', 'true', 'false')

But this results in the error message "Use valid condition". I am unable to figure out if this is caused by a faulty reference ($Opgave: Område) or it is due to the fact that I simply cannot use an extended lookup column for conditional logic.

Please help - TIA

Answer 1

Hi @David Lund,

Firstly, substring() and indexOf() are not valid functions in SharePoint. All supported functions are listed in this article: Examples of common formulas in lists. You can use functions like Left() or Right() to intercept strings and use Find() function instead of indexof().

Additionally, we need use internal name instead of display name in the SharePoint formula. In this case, the internal name of "Opgave: Område" column should be "Opgave_x003a__x0020_Omr_x00e5_de". So we should use [$Opgave_x003a__x0020_Omr_x00e5_de] instead of [$Opgave: Område]. About how to retrieve the internal name of a column, you can refer to: How to Get the Internal Field Name in SharePoint Online.

Lastly, as per my test even you use the valid formula to conditional show/hide column, this still doesn't work if you use the additional lookup field in the formula. This is by design in SharePoint. Additional lookup field cannot be displayed in the form and also its value cannot be recognized by the formula.

Hope I explained it clearly to you. Please let me know if you still have any question.

---

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.