Code: Select all
Select
m.*,
n.contacttype,
n.contactdetails
FROM db_addresses As m, db_addressescontacts As n
WHERE n.recordid = m.recordid
ORDER BY titlename ASC
While processing each record, I put the title, firstname, middlename and lastname into a ListIconGadget. What's the best way of gathering multiple contact data items for each record to display on the same line as each record with this sort of linked table query?
I want to display like this:
Code: Select all
(Field names: Title Firstname Middlename Lastname Home Work Fax Mobile Email)
Mr Harry M Houdini 20154 020202 8710 404129873 vanish@proton.net
Code: Select all
CurrentLine.i = #False
;--------------------------------------------------------------------------------------------------
; Contact data structure for passing to display routine etc
;--------------------------------------------------------------------------------------------------
Structure TempContactsData
contacttype.s
contactdata.s
EndStructure
NewList TempContacts.TempContactsData()
;--------------------------------------------------------------------------------------------------
; Fetch all database rows from the selected table
;--------------------------------------------------------------------------------------------------
NewList RecordNumbers.s()
QueryString.s = "SELECT recordid FROM db_addresses ORDER BY recordid"
If DatabaseQuery(Program\DatabaseHandle, QueryString.s) <> #DatabaseQueryFail
While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
CurrentRecordid.s = GetDatabaseString(Program\DatabaseHandle, 0)
If CurrentRecordid.s <> #Empty$
AddElement(RecordNumbers.s())
RecordNumbers.s() = CurrentRecordid.s
Else
; There was no current record id. Should never happen
EndIf
CurrentRecordid.s = #Empty$
Wend
FinishDatabaseQuery(Program\DatabaseHandle)
Else
; The database query failed to be executed or was empty
EndIf
;--------------------------------------------------------------------------------------------------
; Iterate through the linked list and fetch each record along with the associated contact data
;--------------------------------------------------------------------------------------------------
ForEach RecordNumbers.s()
;------------------------------------------------------------------------------------------------
; Fetch all address data from the db_addresses table for each recordid in the list
;------------------------------------------------------------------------------------------------
QueryString.s = "SELECT * FROM db_addresses WHERE recordid = '" + RecordNumbers.s() + "'"
If DatabaseQuery(Program\DatabaseHandle, QueryString.s) <> #DatabaseQueryFail
While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
CurrentRecordid.s = GetDatabaseString(Program\DatabaseHandle, 0)
If CurrentRecordid.s <> #Empty$
AddElement(RecordNumbers.s())
RecordNumbers.s() = CurrentRecordid.s
Else
; There was no current record id. Should never happen
EndIf
CurrentRecordid.s = #Empty$
Wend
FinishDatabaseQuery(Program\DatabaseHandle)
Else
; The database query failed to be executed or was empty
EndIf
;------------------------------------------------------------------------------------------------
; Fetch all contact data for each recirdid in the list that has linked contact data
;------------------------------------------------------------------------------------------------
QueryString.s = "SELECT contacttype, contactdata FROM db_addressescontacts WHERE recordid = '" + RecordNumbers.s() + "'"
If DatabaseQuery(Program\DatabaseHandle, QueryString.s) <> #DatabaseQueryFail
While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
CurrentContactType.s = GetDatabaseString(Program\DatabaseHandle, 0)
CurrentContactData.s = GetDatabaseString(Program\DatabaseHandle, 1)
If CurrentContactData.s <> #Empty$
AddElement(TempContacts.s())
TempContacts.s()\contacttype.s = CurrentContactType.s
TempContacts.s()\contactdata.s = CurrentContactData.s
Else
; There was no current record id. Should never happen
EndIf
CurrentRecordid.s = #Empty$
Wend
FinishDatabaseQuery(Program\DatabaseHandle)
Else
; The database query failed to be executed or was empty
EndIf
;------------------------------------------------------------------------------------------------
; Display the new string of address data, parse the list for each line
;------------------------------------------------------------------------------------------------
DisaplayAddressTitleString(CurrentLine.i, ShowContacts.TempContactsData)
;------------------------------------------------------------------------------------------------
CurrentLine.i + 1
;------------------------------------------------------------------------------------------------
Next