Need a help in Export SharePoint Relationship into Excel.
I have created relationship database in SharePoint. I want to export the both (ParentList and ChildList) data in one click.
But, here scenario is - In parent list have two records.
1) Record1
2) Record2
In child list have four records. First two records are corresponding to Record1 in ParentList and last 2 records are corresponding to Record2.
Like below.
1) ChildRecord1 --> Record 1
2) ChildRecord2 --> Record 1
3) ChildRecord3 --> Record 2
4) ChildRecord4 --> Record 2
While exporting these data into excel. As of now I'm facing issue. Like It export Parent List data and In parent list have only two records. So, Its created two rows in excel file.
But, I want these data in four different rows. Like below.
1) Record1 --> ChildRecord1
2) Record1 --> ChildRecord2
3) Record2 --> ChildRecord3
4) Record2 --> ChildRecord4
Is there any way to achieve this one.
Below are collections I have created on set it on OnSelect of Export button.
ClearCollect(ColCollection_1,ShowColumns(Gallery1.AllItems,"Title","Initiative_Type","Function","Owners","Target_Date","Completed_Date","Impact","Status","ID"));
ClearCollect(ColCollection_2,ShowColumns(Task,"Title","HowToMeasure","Dependency_Other_Stakeholders","Status","Target_Date","Actual_Completion_Date","Ref_ID"));
ClearCollect(ColCollection_3,AddColumns(ColCollection_1,"SubTaskTitle",Filter(ColCollection_2,Ref_ID=ID).Title));
ForAll(ColCollection_3,
Collect(
colDataExported1,
{
A_Title_Initiative : ThisRecord.Title,
B_Initiative_Type : ThisRecord.Initiative_Type.Value,
C_Function : ThisRecord.Function,
D_Owner : ThisRecord.Owners,
E_Impact : ThisRecord.Impact.Value,
F_Target_Date : ThisRecord.Target_Date,
G_Expected_Complition_Date : ThisRecord.Completed_Date,
H_Status : ThisRecord.Status.Value,
I_ID : ThisRecord.ID,
J_SubTaskTitle: ThisRecord.SubTaskTitle
}
)
);
If(CountRows(colDataExported1)>0,Set(
varFileLink,
ExportToExcelNew.Run(
JSON(
colDataExported1,
JSONFormat.IncludeBinaryData & JSONFormat.IgnoreUnsupportedTypes
)
).filelinkn
);
Launch(varFileLink);
18
