Building List screens with FilterBoxes

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Building List screens with FilterBoxes

  • Comments 25
  • Likes
The code in this article has been corrected and updated (29th October 2009) to bring it into line with the coding convention introduced in Sage CRM 6.2.
 
Custom Pages that contain Search screens are fussy about block positioning.
The search block does not work correctly if it is included into the container following the list making it more difficult to create a screen with filterbox like behaviour. The type of Screen I am aiming at is shown here. This is a known issue.

The problem can be illustrated with some example code. For instance, the following code works:


var FilterBoxBlock = CRM.GetBlock("projectfilterbox");
var ProjectListBlock = CRM.GetBlock("ProjectList");
var myBlockContainer = CRM.GetBlock("Container");
with (myBlockContainer)
{
AddBlock(FilterBoxBlock);
AddBlock(ProjectListBlock);
}
ProjectListBlock.ArgObj = FilterBoxBlock;



BUT!!!!!

This code sample does not:


var FilterBoxBlock = CRM.GetBlock("projectfilterbox");
FilterBoxBlock.NewLine = false;
var ProjectListBlock = CRM.GetBlock("ProjectList");
var myBlockContainer = CRM.GetBlock("Container");
with (myBlockContainer)
{
AddBlock(ProjectListBlock);
AddBlock(FilterBoxBlock);
}
ProjectListBlock.ArgObj = FilterBoxBlock;


All that is different is that I have moved the search block to the right hand side of the List block.

To work round this I have had to control the building of the Argument that is passed to the list block. The following code is a partial work around for this issue.
You will still have to do some fine tuning but it should give you an idea.

The code is designed to be added to the Company tab group and call a list of projects belonging to that company.


if (CRM.Mode == View)
{
CRM.Mode = Edit;
}

var intRecordId = CRM.GetContextInfo("company","comp_companyid");
var strFilterButton = CRM.Button("filter", "filter.gif", "javascript:document.EntryForm.submit();");

var projectlistBlock = CRM.GetBlock("projectlist");

var FilterBoxBlock = CRM.GetBlock("projectfilterbox");
with (FilterBoxBlock)
{
NewLine = false;
AddButton(strFilterButton);
ButtonLocation = Bottom;
ButtonAlignment = Right;
}

var SuperContainer = CRM.GetBlock("Container");
with (SuperContainer)
{
AddBlock(projectlistBlock);
AddBlock(FilterBoxBlock);
DisplayButton(Button_Default) = false;
}

//Make sure fields in filter box are each on new line.
var strArg="proj_companyid="+intRecordId;
var myE = new Enumerator(FilterBoxBlock);
while (!myE.atEnd())
{
myEntryBlock = myE.item();
if (String(Request.Form(myE.item()))!='undefined')
{
strArg+=" and "+myE.item()+" like '"+Request.Form(myE.item())+"%'";
}
myEntryBlock.NewLine = true;
myEntryBlock.AllowBlank = false;
myE.moveNext();
}

projectlistBlock.ArgObj = strArg;

CRM.AddContent(SuperContainer.Execute());
Response.Write(CRM.GetPage());



Comments
  • The underlying cause of this problem will be resolved in the 6.2 e patch due 13/01/2010.  So no code changes like the ones described in this article will be required to make this work after applying patch 6.2 e.  

    The sample non-working code should work:

    var FilterBoxBlock = CRM.GetBlock("projectfilterbox");

    FilterBoxBlock.NewLine = false;

    var ProjectListBlock = CRM.GetBlock("ProjectList");

    var myBlockContainer = CRM.GetBlock("Container");

    with (myBlockContainer)

    {

    AddBlock(ProjectListBlock);

    AddBlock(FilterBoxBlock);

    }

    ProjectListBlock.ArgObj = FilterBoxBlock;

  • If I add date type fild to filter box and run filter container which includes filter box and list, it occurs error message like "Window.opner.parent " on Self-Service. But the filterbox without date type filed, it works fine. Is there anyone who knows about this problem? When I need to include date type filed at filter box, do I have to do something special?  Thank you in advance.

  • It appears this problem is still occurring in the product.  Here is a blog on how to get around it: community.sagecrm.com/.../building-list-screens-with-filterboxes-take-2.aspx

  • Thank you Jeff, for this very helpfull idea of builting filterboxes.

    It worked all fine in our testing area. After all we came to the point, where we added a multi-select field to the filterbox and look at this; it does not work anymore, after pressing "filter", the list is allways empty.

    It seems to be a problem underlying with the multi-check.

    Does anyone have an idea how to solve this?

    Thank you

    André

  • I believe I am also experiencing the same or very similar problem as OnConcept above me.

  • I did a little experimenting with a system field.  I added a column oppo_country to the opportunity entity as a mult-select to see how  the SQL was generated by a filter box

    It look like (if we ignore the security information)

    SELECT * FROM ( select * , ROW_NUMBER() over(ORDER BY  comp_name, Oppo_OpportunityId) AS rowranking  from vListOpportunity  WITH (NOLOCK)  WHERE Oppo_AssignedUserId=1 AND  oppo_status = N'In Progress' AND ((oppo_country LIKE N'%,AF,%' ESCAPE '|' or  oppo_country = N',AF,' or oppo_country LIKE N'AF,%' ESCAPE '|') OR (oppo_country LIKE N'%,DZ,%' ESCAPE '|' or  oppo_country = N',DZ,' or oppo_country LIKE N'DZ,%' ESCAPE '|'))  ) as A  WHERE rowranking > 0 and rowranking < 11

    So Filtering on Multiselects is always a logical 'OR'.

    You'll need to see whether the SQL generated by the ASP page that includes the Multi-select looks like that.  If it does not then you'll need to raise a case with you local support desk.

  • @Jeff

    How can i see the exact SQL statement, Sage CRM is generating?

  • You turn on full SQL Logging

    Administration -> System -> Logging

    Then you access the feature in CRM.  If you have set the SQL Logging Level to be "All queries over the threshold" then every SQL statement generated by the ASP page that uses the Sage CRM API will be recorded into the log.

    The log will be added here

    C:\Program Files\Sage\CRM\CRM\Logs

  • My SQL Statement looks like this:

    SELECT * FROM ( select * , ROW_NUMBER() over(ORDER BY  Pers_Lastname, Pers_FirstName, Pers_PersonId) AS rowranking  from vSearchListPerson  WITH (NOLOCK)  WHERE pers_companyid=4379 and pers_status like '2%' and pers_xtype like '8, 10%'  ) as A  WHERE rowranking > 0 and rowranking < 26

    The values in the Field "pers_xtype" are not logical "OR", they are "AND".

    So i think i have to raise a case with my local support desk.

    Thank you anyway for assistance!

  • Do someone have an idea why this Code does not work?

    var FilterBoxBlock = CRM.GetBlock("OpportunityFilterBox");

           FilterBoxBlock.NewLine = false;

           var OpportunityListBlock = CRM.GetBlock("OpportunityList");

           var myBlockContainer = CRM.GetBlock("Container");

           with (myBlockContainer)

           {

             AddBlock(OpportunityListBlock);

             AddBlock(FilterBoxBlock);

           }

           OpportunityListBlock.ArgObj = FilterBoxBlock;

    I have the List but not the Filterbox?!

    I am on 7.1.h.1...

  • My guess is that you either

    1) forgot to add fields into the OpportunityFilterbox - but since that is the default block I doubt that.

    2) In the line that has CRM.AddContent(xxxxx.Execute()) you are executing the OpportunityListBlock and not myBlockContainer.

  • I have the same problem that sroe, only the list block is displayed.

    The opportunityfilterbox has 3 fields, and i used the container:

    ...

    with (container)

    {

    AddBlock(List);

    AddBlock(Filter);

    DisplayButton(Button_Default) = false;

    }

    ...

    List.ArgObj= stringSQL;

    CRM.AddContent(container.Execute());

    ...

    The same version 7.1.h, but imposssible to update due to a Murano Integration.

  • I'm having trouble adding a filterbox to the documents tab on a new entity created with the advanced customization wizard in CRM 7.2b.

    I have added the following lines to the basic EntityLibrary.asp page -

    var strFilterButton = CRM.Button("filter", "filter.gif", "javascript:document.EntryForm.submit();");

    FilterBox=CRM.GetBlock("LibraryFilterBox");

    with (FilterBox)

    {

     NewLine = false;

     AddButton(strFilterButton);

     ButtonLocation = Bottom;

     ButtonAlignment = Right;

    }

    and further down:

    Container.AddBlock(FilterBox);

    List.ArgObj = FilterBox;

    This has added a filterbox to the screen, though it displays on the left of the file upload box instead of above it (a minor nuisance I could live with if need be, though it would be nice to be consistent with other document screens in CRM).

    The issue I have is that the filter button must be clicked twice (two page loads) for the filter to take effect. The first click simply refreshes the page but seems to do nothing else, the second click applies the filter.

    Anyone know what I'm overlooking?

  • Is your FilterBox defined as a search screen?  Is it associated with the correct list in Meta Data?

  • I was originally using the LibraryFilterBox, so I couldn't tell for certain whether or not it was defined as a search screen, so I created a new JobLibraryFilterBox, set it as a search screen and linked it to LibraryList (which is the list being called in the asp page) only to end up with the same result - I still need to click on the filter button twice for the list to actually apply the filter, and the file upload section is still to the right of the filter(s).