Tree View Display of Data

[zmansuri]

I have a SharePoint list consist of sales leads. It is possible that this leads will give reference for other leads. 

Name	Email	ReferredBy	ReferredByEmail
John	john@gmail.com	Mary	mary@gmail.com
Mary	mary@gmail.com
Mark	mark@gmail.com	John	john@gmail.com

Referred by is text type column in the database. But in the form i use combobox and combobox item property is collection of names from the same list.

As you can see from above list Mary has referred John. John has referred Mark. 

Now, for reporting purpose i need to create a tree like representation. Where i can see who has referred who and find root nodes. I have no idea where to begin. I appreciate any help. Thank you.

[v-bofeng-msft · Accepted solution]

Hi @zmansuri :

I've made a test for your reference:

1\I assume there is a table:

ClearCollect(
    TheCollection,
    {Name:"John",Email:"john@gmail.com",ReferredBy:"Mary",ReferredByEmail:"mary@gmail.com"},
    {Name:"Mary",Email:"Mary@gmail.com",ReferredBy:"",ReferredByEmail:""},
    {Name:"Mark",Email:"Mark@gmail.com",ReferredBy:"John",ReferredByEmail:"John@gmail.com"},
    {Name:"Jim",Email:"Jim@gmail.com",ReferredBy:"",ReferredByEmail:""},
    {Name:"Leo",Email:"leo@gmail.com",ReferredBy:"Jim",ReferredByEmail:"Jim@gmail.com"}
)

2\Add a timer control

OnSelect

ClearCollect(NewCollection,AddColumns(Filter(TheCollection,IsBlank(ReferredBy)),"FamilyID",0,"Level",1));ForAll(Filter(TheCollection,IsBlank(ReferredBy)) As A,Patch(NewCollection,LookUp(NewCollection,Email=A[@Email]),{FamilyID:First(Sort(NewCollection,FamilyID,Descending)).FamilyID+1}));Set(CountLoop,1)

OnTimerEnd

ForAll(Filter(NewCollection,Level=CountLoop) As A,With({TheRecord:LookUp(TheCollection,Upper(A[@Name])=Upper(ReferredBy))},If(!IsBlank(TheRecord),Collect(NewCollection,{Name:TheRecord.Name,Email:TheRecord.Email,ReferredBy:TheRecord.ReferredBy,ReferredByEmail:TheRecord.ReferredByEmail,FamilyID:A[@FamilyID],Level:CountLoop+1}))));Set(CountLoop,CountLoop+1);

Repeat

CountRows(TheCollection)>=CountRows(NewCollection)

Duration

1000

I added the FamilyID and Level, and then saved the result to a new collection, so that the root item can be easily found through the FamilyID and Level.

Best Regards,

Bof

[zmansuri]

Wow, That's perfect. Thank you so much.