SAC App designer - dropdown list shows all members of a hierarchy

[JBARLOW]

Hopefully a simple one to answer, how do I populate a dropdown list with values from a specific level in a hierarchy?

Example:
Dimension: "Cost Centre"

Structure might be:

lvl1: Region
lvl2: - North
lvl3: - North East
- North West
lvl2: - South
lvl3: - South West
- South East

Currently my dropdown shows a list of all values
(Region, North, North East, North West, South, South West, South East)

What I want to see in the dropdown is only 2 values from Level 2 in the hierarchy (North & South)

My oninitialization script is:

var cc = tbl_Lookup.getDataSource().getMembers("Cost_Centre");

for (var i=0;i<cc.length; i++)
{ Dropdown.addItem(cc[i].id,cc[i].description);
}

I've tried setting the hierarchy level (which I can do on a table) but no idea how to force the dropdown to only show values from level 2 in the hierarchy 😞

[former_member45674 · Accepted solution]

Hi James,

There is no dedicated API for this.

Not very graceful, but doable like below:

Table_1.getDataSource().setHierarchyLevel("Location_4nm2e04531", 1); 

var m = Table_1.getDataSource().getResultSet();
console.log(m);
for (var i = 0; i < m.length; i++){

var id = m[i]["Location_4nm2e04531"].id;
var description = m[i]["Location_4nm2e04531"].description;

Level1.addItem(id, description);

}
Table_1.getDataSource().setHierarchyLevel("Location_4nm2e04531", 2);
 m = Table_1.getDataSource().getResultSet();
console.log(m);
for ( i = 0; i < m.length; i++){

 id = m[i]["Location_4nm2e04531"].id;
 description = m[i]["Location_4nm2e04531"].description;
var parentId =  m[i]["Location_4nm2e04531"].parentId;

//skip its parent node (level 1). For level 2, can check whether its parent id is NULL. But when you populate Level 3, need to write a function to skip Level 2 memebers
if (parentId) {
Level2.addItem(id, description);
}

}

[JBARLOW · Accepted solution]

Cheers Jason,

I'd totally forgotten about using the getResultSet - works well.
You're right it is a bit of a extra code, so hopefully won't impact performance too much.
I plan on setting the table Hierarchy Level to 2 > adding the items to the Dropdown, then after that setting the table back to level 3.
So potentially an extra hit when using a live connection.

[former_member617562 · Accepted solution]

Hi Jason,

Thanks for your explanation.

I was trying to use the same logic in my requirement, but i have question

what is Level1 & Level2 ,Here are you using to 2 Dropdown selections. My requirement is to add all the levels of Hierarchy to single Dropdown.

Level1.addItem(id, description);

Level2.addItem(id, description);

Can you please clarify my doubt. waiting for your reply.

Regards,

Venkat

[JBARLOW · Accepted solution]

To add members to a single dropdown just use the code I originally posted

var cc = tbl_Lookup.getDataSource().getMembers("Cost_Centre");

for (var i=0;i<cc.length; i++)
{ Dropdown.addItem(cc[i].id,cc[i].description);
}