BUG: lookupfield does nog work correctly with calculated field

bartplessers · August 29, 2024, 1:29pm

Hi everyone,

I have a SharePoint source list "Users" that contains FirstName, LastName and UserID
In that list, I have

a calculated field "FriendlyName" that concatenates those fields.
a txt field "FriendlyName_Txt" that has the same values as "FriendlyName" (just for testing purposes, see further)

Thus something like

Than I have another list "Results" where I have

a lookup-field "User" pointing to the (calculated) field "FriendlyName"
a lookup-field "User_Txt" pointing to the (txt) field "FriendlyName_Txt"

Now the strange thing is that the Plumsail lookup-field interface does NOT work with "User":
if you type in something, the lookup field, it should act as a search box and filter the options in the dropdown, but this doesn't work at all:

no results
dropdown is not expanded
if you click beside the lookupfield, the dropdown expands, but options are not filtered

If you do the same for "User_Txt", behaviour is as expected:

Can somebody reproduce this for me?
I assume this is a bug?
If yes: when will this be resolved?

kind regards,
bartplessers

IliaLazarevskii · August 30, 2024, 3:04pm

Hi @bartplessers,

This is the expected result, lookup fields don't support calculated fields. Try this workaround.

bartplessers · September 3, 2024, 10:28am

Hi @IliaLazarevskii
Thanx for pointing me to that link.

However, suggested solution by @mnikitina are

creating a custom displaytemplate
As an option, you can change the field from which the lookup field is getting value and set up view results for the lookup field. You can find the instructions here:
https://plumsail.com/docs/forms-sp/how-to/lookup-view.html

I tested this, but the search-functionality only works on the primary field of your lookup. So if you add extra fields, they are NOT searchable, even if they are displayed in the custom display template.

create a custom filter
It's something that can help and is a value tip (thanx @mnikitina !), but this has other behavior.
In the sample provided, you search in all the individual fields.
But I want to look for an item were the concatenated value is someting like your searchterm, than you don't get results.
i.e.
My concatenated field is "Lastname Firstname"
and I want to search specific for "Lastname Firstn*"
This can be done by searching in the contactenated field, but not if you search in the individual fields "Lastname" OR "Firstname" as none of them contains the searchterm...

So in that case, I think the only thing you can do is download all the fields, create a new array with the concatenated value and do a find in this array.
However, this is not a good idea for large lists as you have to get all your items first....

Any other suggestions?

kind regards,
Bart

bartplessers · September 3, 2024, 2:29pm

Hi @IliaLazarevskii , @mnikitina

Is it possible to filter a lookupfield by a CAML filter?

If I add a "list of library control", I can set the filter by a CAML filter, and apparently a calculated field can be filtered too.

i.e.

filterTerm = "2422"
filterResults = "Dossiers"
dataTable = fd.control(filterResults)

myFilter = ""
myFilter += "<Or>"
myFilter += "<Or>"
myFilter += "<Or>"
myFilter += "<Or>"
myFilter += "<Contains><FieldRef Name='Doctorandus_achternaam'/><Value Type='Text'>" + filterTerm + "</Value></Contains>"
myFilter += "<Contains><FieldRef Name='Doctorandus_voornaam'/><Value Type='Text'>" + filterTerm + "</Value></Contains>"
myFilter += "</Or>"
myFilter += "<Contains><FieldRef Name='Doctorandus_studnr'/><Value Type='Text'>" + filterTerm + "</Value></Contains>"
myFilter += "</Or>"
myFilter += "<Contains><FieldRef Name='Promotor'/><Value Type='Text'>" + filterTerm + "</Value></Contains>"
myFilter += "</Or>"
myFilter += "<Contains><FieldRef Name='MY-CALCULATED-FIELD'/><Value Type='Text'>" + filterTerm + "</Value></Contains>"
myFilter += "</Or>"

dataTable.filter = myFilter

It would be an added value if the internal logic for a lookupfield was done with a CAML filter instead of the api that does not allow filtering on calculated fields:

/_api/web/lists('GUID')/items?$select=Id,Title&$filter=substringof('filterTerm', MY_CALCULATED_FIELD)&$top=36

It would be great if this was also possible with lookup field!
Can this be done?

kind regards,
bartplessers

StepanS · September 4, 2024, 12:46pm

Hi @bartplessers ,

I would like to help you, but what is the main goal?
To have a dropdown where you can fill in letters and should that work as search bar?

What about - on source list - to use Power Automate flow to check if a new or edited item has been aded/edited and run it. In the Power Automate flow concatenate what you need and insert it to the Text Field in SharePoint column?

I am not sure what your goal is
If you can explain it more, I will probably help you to find a way how to solve this issue.

Have a nice day
Stepan

bartplessers · September 5, 2024, 11:58am

Hi @StepanS ,

thanx for taking a look at this

exactly that!

This is an option, but

this takes resources
and above all: this takes time

The latter is especially annoying: the user enters something, and only a few minutes later the power machine has done its job

So yes indeed: I just need a dropdown that works as a search bar.

No I'm using

a list/library control (1)
that is filtered by a custom txt field (2)

2024-09-05_13-52-451674×653 79.4 KB

(filtering is done by a CAML query filter, this works on calculated fields)

Works perfect, but takes some space on my canvas.
The same can be achieved with just one lookup-control that takes less space

kind regards
bartplessers

StepanS · September 5, 2024, 12:25pm

Hello @bartplessers ,

I understand a bit more right now
The goal is to have a TXT field which should serve as a Search Bar and when you write down a letter "d" somehow down under this TXT field vLookup column should only show you records that meets the requirements - vLookup (calculated column) contains "d" or start With?

Hmm, I am thinking. I tried something on my side.
I have a Title column named by a city "Prague" and when I create a new calculated column with this formula

=[Title]

It shows me the same value - what is basically correct.

When I use:

pnp.sp.web.lists.getByTitle("Seznam Oblastí").items.filter(`calcColumn eq 'Prague'`).getAll()

The records are not returned with an error that we cannot filter on Calculated column. So I tried this:

pnp.sp.web.lists.getByTitle("Seznam Oblastí").getItemsByCAMLQuery({
  ViewXml: `<View><Query><Where><Eq><FieldRef Name='calcColumn'/><Value Type='Text'>Prague</Value></Eq></Where></Query></View>`
})
  .then(items => {
    console.log(items);
  })
  .catch(error => {
    console.error("Error fetching items:", error);
  });

It returned me one record which is correct.
That means - the solution:

Whenever someone write down "d" you can execute a query: (Contains)

pnp.sp.web.lists.getByTitle("Seznam Oblastí").getItemsByCAMLQuery({
  ViewXml: `<View><Query><Where><Contains><FieldRef Name='calcColumn'/><Value Type='Text'>B</Value></Contains></Where></Query></View>`
})
  .then(items => {
    console.log(items);
  })
  .catch(error => {
    console.error("Error fetching items:", error);
  });

The same approach with Begins:

pnp.sp.web.lists.getByTitle("Seznam Oblastí").getItemsByCAMLQuery({
  ViewXml: `<View><Query><Where><BeginsWith><FieldRef Name='calcColumn'/><Value Type='Text'>B</Value></BeginsWith></Where></Query></View>`
})
  .then(items => {
    console.log(items);
  })
  .catch(error => {
    console.error("Error fetching items:", error);
  });

Send query to get all items (I do not know how many of them you have) and store them in Object and work with these data afterwards when "TXT field" is changed and some letter is being wrote down, execute the script

Did I help you a bit?
Stepan

bartplessers · September 6, 2024, 8:00am

Hi @StepanS

No, that's not the goal. It's the workaround

That's basically what a standard lookupfield in Plumsail already does when you start typing in it.Just check the network traffice in the devtools of your browser. If you type something in a lookup field, you will see appear something like:

/_api/web/lists('myListID')/items?$select=Id,myLookupField&$filter=substringof('mySearchTerm', 'myLookupField')&$top=36

So my suggestion was that the source code of Plumsail maybe could be modified in a way that the out-of-the box query that is made when you type something in a lookupfield is altered and that is uses the CAML query in stead of the standard api that is used now.

This would be an "upgrade" of the functionalities of a standaard lookupfield, and than there is no need to add an extra "search box".

kind regards
bartplessers

StepanS · September 7, 2024, 10:13am

Hi @bartplessers ,

Ok, what's next? How can I help you?

I think sometimes looking for workarounds is just job that must be done.

Anytime I cannot figure out some situations, I am looking up the "workaround" because no solution exists. And I always find a path how to handle critical or difficult scenarios.

Yes, and I love it, that's why I love designing in Plumsail Designer with SharePoint

Regards
Stepan