Restricting Lookup List items - Restricting Lookup List items based on a column value in the lookup list

ArdeviaForum

• Created August 11, 2016 14:43

Original Post by Sandy (Imported from Ardevia Forum)

Hi,

 

I would like to restrict the items available for selection in a lookup list to those with particular attributes.

 

Invoice List

 

Account

Description

Amount

 

 

Account List

 

Account Number

Account Type (expense, revenue, general, other)

 

 

The Account column on the Invoice list has a lookup to the Account Number column on the Account List. However, I only want to be able to select items from the Accounts List that have a type of expense or other.

 

How do I restrict this?

 

Thanks

Sandy

0

• 

  ArdeviaForum

  • August 11, 2016 18:08

  Original Post by Taras Lozynskyy (Imported from Ardevia Forum)

  Hi Sandy

   

  1. Select Account field

  2. Go to Behaviuor tab

  3. Open Visible expression dialog

  4. Paste the code below

   

  var ids = [[@Web.GetValuesForQuery('Account', '<Where><Or><Eq><FieldRef Name="Account_x0020_Type"/><Value Type="Text">expense</Value></Eq><Eq><FieldRef Name="Account_x0020_Type"/><Value Type="Text">other</Value></Eq></Or></Where>', 'Account_x0020_Number')]];
  if(ids){
   var select = document.getElementById('Account_ce77824a-8210-4905-aa9b-346268070df7_$LookupField');
          var accounts = jQuery(select).find('option');
          accounts.each(function() {
            var id = $(this).attr('value');
            var present = false;
            for (var i = 0; i < ids.length; i++){
              if(id == ids[i]){
                present = true;
                break;
              }
            }
            if (!present) {
                $(this).remove();
            }
          });
  }
  return true;
  

   

  5. Change field ids and html select element lookup id

  6. Save the page

  0
• 

  ArdeviaForum

  • August 11, 2016 18:10

  Original Post by Sandy (Imported from Ardevia Forum)

  Hi,

   

  And if that Account field was in a Sublist, what would be the process. I can't see the lookup id on the form.

   

  Thanks

  Sandy

  0
• 

  ArdeviaForum

  • August 11, 2016 18:12

  Original Post by iryna (Imported from Ardevia Forum)

  You can try to set this calculated expression on the sublist column.
  But the ID would be smth like "jsgrid_comboboxspgridcontainer_WPQ4" and
  jQuery(select).find('option') should be changed to jQuery(select).find('jsgrid-control-text').

  And $(this).remove() changed to $(this).parent().remove()

  0
• 

  ArdeviaForum

  • August 11, 2016 18:17

  Original Post by Sandy (Imported from Ardevia Forum)

  Hi,

   

   

  This is the code that I have now but I am only getting results in the ids var.

   

  var ids = [[@Web.GetValuesForQuery('REF-Accounts', '<Where><Eq><FieldRef Name="Account_x0020_Type"/><Value Type="Text">Expense</Value></Eq></Where>', 'Title')]];
  if(ids){
  var select = document.getElementById('jsgrid_comboboxspgridcontainer_WPQ5');
  var accounts =  jQuery(select).find('jsgrid-control-text');
    accounts.each(function() {
            var id = $(this).attr();
            var present = false;
            for (var i = 0;i < ids.length; i++){
              alert(id) if(id == ids[i]){
                present = true;
                break;
              }
            }
            if (!present) {
                $(this).parent().remove();
            }
          })
  }
  return true;
  

   

  Thanks Sandy

  0

Please sign in to leave a comment.