Foxpro DBF, Joining a table with multiple columns that can return more than 1 record and projecting it into a list(Of
-
Well I had a simple table that contained 2 contact records, in which I was able to do a join and get the 2 contacts to send emails to. Th entire program was based in this everywhere Then the customer wanted to be able to do 3, 4 and 5 contacts to send emails to, So I said OK to make it unlimited. I created a new table with 1 contact record. So now I have an invoice that is a single record, and a list of contacts that can be multiple. I forgot about the double record if my join has 2 contacts, so that's out the window. I have no clue what would be the best solution, perhaps start again on the design; maybe someone can point me in the right direction here. My last resort may be loading all the contacts in a 2nd query, and using linq to match and populate.
queryString += _
" UNION ALL " & _
" SELECT " & _
" h.FINVNO " & _
", h.FCUSTNO " & _
", h.FCOMPANY " & _
", h.FSALESPN " & _
", h.FSHIPVIA " & _
", h.FORDDATE " & _
", h.FSHIPDATE " & _
", h.FNTAXAMT " & _
", (SELECT a.FADDR1 FROM ARCUS01.dbf a WHERE h.FCUSTNO = a.FCUSTNO) AS FADDR1 " & _
", (SELECT j.FPRINT FROM ARCEMJN.dbf j WHERE h.FINVNO = j.FINVNO) AS FPRINT " & _
", (SELECT j.FSENT FROM ARCEMJN.dbf j WHERE h.FINVNO = j.FINVNO) AS FSENT " & _
", (SELECT p.FBATCH FROM ARPRINT.dbf p WHERE h.FINVNO = p.FINVNO) AS FBATCH " & _
", (SELECT ce.FID, ce.FCUSTNO, ce.FCHECK, ce.FNAME, ce.FEMAIL FROM ARCEM02.dbf ce WHERE FCUSTNO = h.FCUSTNO) AS CONTACTS " & _
" FROM ARINV01H.dbf h " & _
" WHERE h.FSHIPDATE = @startDate "Here, I started using List to return the records, so I can do further processing I made a model with the contacts as a list. I'm in uncharted waters here, and I'm not sure if I do get the SQl to work, that I can populate the contacts using this method.
pResults.Add(New model_invoice_index() With {
.FINVNO = If(reader.IsDBNull(0), String.Empty, reader.GetString(0)),
.FCUSTNO = If(reader.IsDBNull(1), String.Empty, reader.GetString(1)),
.FCOMPANY = If(reader.IsDBNull(2), String.Empty, reader.GetString(2)),
.FSALESPN = If(reader.IsDBNull(3), String.Empty, reader.GetString(3)),
.FSHIPVIA = If(reader.IsDBNull(4), String.Empty, reader.GetString(4)),
.FORDDATE = If(reader.IsDBNull(5), New DateTime(89, 1, 1), reader.GetValue(5)),
.FSHIPDATE = If(reader.IsDBNull(6), New DateTime(89, 1, 1), reader.GetValue(6)),
.FNTAXAMT = If(reader.IsDBNull(7), 0.0, reader.GetValue(7)),
.FADDR1 = If(reader.IsDBNull(8), String.Empty, reader.GetString(8)),