Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

Creating Smartlists in Dexterity

Posted on by 125

Good Afternoon,

I have a dex app and think that it is appropriate to include smartlists for the custom tables.

I have two problems...

1.  How do I include fields from two or more related tables.  I can get the fields to display, but can't get the data to populate from anything but the primary table?  What step am I missing?

2.  How do I include fields that are array fields?  How do I reference field _1 vs _2, etc.  Retrieve the proper data and give them custom labels.

Thanks,

Douglas

*This post is locked for comments

  • Verified answer
    Douglas Meade Profile Picture
    Douglas Meade 125 on at
    RE: Creating Smartlists in Dexterity

    Thank You....

    I had that part of the smartlist coding figured out... I mistakenly thought that when I put in the field information that included the table, smartlist would know that information across the board.  I since added code into some of the other smartlist table defining procedures and pointed the joined fields to the other tables.  Everything seems to work now. -- Thanks.

    On the other subject of arrays, it seems to be a bit of a design limitation that the feature to have array fields in GP available, but not be able to use them in a consistent manner in other parts.  At least I've confirmed that I'm not crazy.

    Thanks for the help.

    Douglas

  • Suggested answer
    Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: Creating Smartlists in Dexterity

    1. To add in field from additional tables, add the Explorer_Get_SQL_Join_Info procedure to your dictionary. This procedure allows you to establish the relationship between the objects within your own dictionary or in other dictionaries. This is a quick template for this proc:

    in	integer			IN_Object_Dict_ID;
    in	integer			IN_Object_Type;
    in	Explorer_INT_List	IN_Field_Dict_ID;
    in	Explorer_INT_List	IN_Field;
    inout 	integer                 Doc_Type;
    
    local	string	l_From_Table, l_From_Field, l_To_Table, l_To_Field;
    local	integer	l_From_Table_Dict_ID, l_To_Table_Dict_ID, l_Join_Type, l_Exp_Error;
    
    if IN_Object_Dict_ID = <Your_Product_ID> then
      case IN_Object_Type
        in [Your_Object_Type] 
    	l_From_Table = technicalname(table <Your_Table_Name>);
    	l_From_Table_Dict_ID = <Your_Product_ID>;
    	l_From_Field = "<Source_Field>";
    	l_To_Table = technicalname(table <Destination_Table>;
    	l_To_Table_Dict_ID = <Destination_Product_ID>;
    	l_To_Field = "<Destination_Field>";
    	l_Join_Type = SMARTLIST_JOINTYPE_LEFTOUTER; 
    
    	call with name "Explorer_Set_SQL_Join_Info" in dictionary SMARTLIST,
    	  l_From_Table,
    	  l_From_Table_Dict_ID,
    	  l_From_Field,
    	  l_To_Table,
    	  l_To_Table_Dict_ID,
    	  l_To_Field,
    	  l_Join_Type,
    	  l_Exp_Error;
       end case;
    end if;
    

    Take a look at the Dexterity Integration Guide (IG.PDF), page 747 for a list of the existing join type constants. I will also assume that you have added the Explorer_Get_Table_Name procedure to your dictionary and that it is properly retrieving your table names.

    2. The problem with array fields is they all share the same resource ID and technical name. You will have to perhaps create a Dexterity Virtual Table to create aliases for the array fields first, then expose the virtual table to SmartList. That's the best I can think of. I don't know if Dex creates the SQL view for the virtual table, but I guess that's the least of your concerns :-) 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,228 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans