cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zmansuri
Super User
Super User

Tree View Display of Data

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.

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

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

vbofengmsft_0-1634180728644.png

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

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

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

vbofengmsft_0-1634180728644.png

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

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

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,609)