I have a SharePoint list consist of sales leads. It is possible that this leads will give reference for other leads.
Name | 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.
Solved! Go to 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
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
Wow, That's perfect. Thank you so much.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
User | Count |
---|---|
260 | |
128 | |
86 | |
85 | |
68 |