Generate word doc from two entities with a one-to-many relationship

May 30, 2014 at 11:32 PM
I am really struggling to work out how to use the Office Integration Pack for LightSwitch to generate a word document from two entities with a one-to-many relationship.

I have two tables ClientSelect (Parent) & JobSummary (Child) and all I want to be able to do is map the columns and fields from JobSummary when a Particular 'Client' is selected from the ClientSelect entity. Unfortunately I can only use the columns and fields from the ClientSelect entity using Beth Massi's example here

Please can someone help me to modify my code below to enable me to generate a word document using the JobSummary entity too? I have seen a solution in c# here but I do not know how to apply this to my example below.

Thanks in advance, Graeme
Private Sub GenerateDoc_Execute()

            Dim Filename As String = Me.TemplateLibrary.DocumentFileName
            Dim WordFile = "C:\Document Templates\" & Filename

            If File.Exists(WordFile) = False Then
                Me.ShowMessageBox("Template is not available", "Generate Document", MessageBoxOption.Ok)
                If File.Exists(WordFile) Then

                    Dim custFields As New List(Of OfficeIntegration.ColumnMapping)
                    custFields.Add(New OfficeIntegration.ColumnMapping("CFullname", "CFullname"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("CRef", "CRef"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("CAddress1", "CAddressLine1"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("CAddress2", "CAddressLine2"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("CTownCity", "CTownCity"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("CCountry", "CCountry"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("CPostCode", "CPostCode"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("CAdditionalInfo", "CAdditionalInfo"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("CHPhone", "CHPhone"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("CMPhone", "CMPhone"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("CEmail", "CEmail"))

                    'this section needs to bring data in from the JobSummary entity.....

                    custFields.Add(New OfficeIntegration.ColumnMapping("JSAddress1", "JSAddressLine1"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("JSAddress2", "JSAddressLine2"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("JSTownCity", "JSTownCity"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("JSCountry", "JSCountry"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("JSPName", "JSPName"))
                    custFields.Add(New OfficeIntegration.ColumnMapping("JSPDescription", "JSPDescription"))

                    Dim doc As Object = OfficeIntegration.Word.GenerateDocument(WordFile, Me.ClientDetails, custFields)

End If 
End if
End Sub
Jun 2, 2014 at 8:16 AM
Maybe you could flatten the entities with a SQL JOIN in a view ; and then process it as single table in the query and a loop in your code?