Forms Designer Forum

Posted: **09 Oct 2017**

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?

Posted: **09 Oct 2017**

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.

Forms Designer Forum

Show all related items and those with no relationship

Show all related items and those with no relationship

Re: Show all related items and those with no relationship