Show all related items and those with no relationship

[Jaydius]

I have a Cross-Site lookup im wanting to show all items that relate to an id, but also show in the dropdown all items that have no association. This is the code im currently using, which works well to filter all items relating to the client and project:

Code: Select all

function (term, page) {

  // Getting the selected client and project
  var clientId = fd.field('Client').value();
  var ProjectId = fd.field('PID').value();  
  
  if (!clientId) {
    clientId = 0;
  }

  // No client filter
  if (clientId == 11) {

 if (!term || term.length == 0) {
        return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Rate,Pay_x0020_Rate,Unit,Archive,Prospec,Client,RelatedProjects&$orderby=Prospec DESC,{LookupField}&$top=40";
     }

   return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Rate,Pay_x0020_Rate,Unit,Arcive,Prospec,Client,RelatedProjects&$orderby=Prospec DESC,{LookupField}&$filter=substringof('" + encodeURIComponent(term) + "', {LookupField})&$top=40";
 }
  
  // Filtering by the selected client
  if (!term || term.length == 0) {
    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Rate,Pay_x0020_Rate,Unit,Archive,Prospec,RelatedProjects/ID,RelatedProjects/ProjectID,Client/Id&$orderby={LookupField}&$expand=Client/Id,RelatedProjects/ProjectID&$filter=Client/Id eq " + clientId + " and RelatedProjects/ID eq " + ProjectId + " and Archive eq 0&$top=40";
  }
  return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Rate,Pay_x0020_Rate,Unit,Archive,Prospe,RelatedProjects/ID,RelatedProjects/ProjectID,Client/Id&$orderby={LookupField}&$expand=Client/Id,RelatedProjects/ProjectID&$filter=substringof('" + encodeURIComponent(term) + "', {LookupField}) and Archive eq 0 and RelatedProjects/ID eq " + ProjectId + " and Client/Id eq " + clientId + "&$top=40";
}

I essentially need the following to include items that relate to the Project, and all records that don't relate to any project.

RelatedProjects/ID eq " + ProjectId + " OR null?

[Nikita Kurguzov]

Hello, Jaydius!
This is absolutely possible. Let me give you an example code:

Code: Select all

function (term, page) {

  // Getting the selected client and project
  var clientId = fd.field('Client').value();
  var ProjectId = fd.field('PID').value();  
  
  if (!clientId) {
    clientId = 0;
  }

  // No client filter
 if (clientId == 11) {
     if (!term || term.length == 0) {
        return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Rate,Pay_x0020_Rate,Unit,Archive,Prospec,Client,RelatedProjects&$orderby=Prospec DESC,{LookupField}&$top=40";
     }

     return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Rate,Pay_x0020_Rate,Unit,Arcive,Prospec,Client,RelatedProjects&$orderby=Prospec DESC,{LookupField}&$filter=substringof('" + encodeURIComponent(term) + "', {LookupField})&$top=40";
 }
  
  // Filtering by the selected client
  if (!term || term.length == 0) {
    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Rate,Pay_x0020_Rate,Unit,Archive,Prospec,RelatedProjects/ID,RelatedProjects/ProjectID,Client/Id&$orderby={LookupField}&$expand=Client/Id,RelatedProjects/ProjectID&$filter=(Client/Id eq " + clientId + " and RelatedProjects/ID eq " + ProjectId + " and Archive eq 0) or RelatedProjects/ID eq null &$top=40";
  }
  return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Rate,Pay_x0020_Rate,Unit,Archive,Prospe,RelatedProjects/ID,RelatedProjects/ProjectID,Client/Id&$orderby={LookupField}&$expand=Client/Id,RelatedProjects/ProjectID&$filter=substringof('" + encodeURIComponent(term) + "', {LookupField}) and (Archive eq 0 and RelatedProjects/ID eq " + ProjectId + " and Client/Id eq " + clientId + ") or RelatedProjects/ID eq null &$top=40";
}

This is just an example, so you might need to adjust the code for your case, if I didn't get what you wanted correctly.