Join workman_MAIN!lu_Catagory ; > ON JobContact.fk_Type_id = lu_Catagory.pk_id>
Create Sql View "JOBCONTACT_2" ; > AS Select AddressBook.Company, ; > Iif(Empty(AddressBook.Prefix), "", Alltrim(AddressBook.Prefix) + " ") + ; > Iif(Empty(AddressBook.FirstName), "", Alltrim(AddressBook.FirstName) + " ") + ; > IIF(Empty(AddressBook.MiddleName), "", Alltrim(AddressBook.MiddleName) + " ") + ; > IIF(Empty(AddressBook.LastName), "", Alltrim(AddressBook.LastName) + " ") + ; > IIF(Empty(AddressBook.Suffix), "", Alltrim(AddressBook.Suffix)) As ConName, ; > lu_Catagory.cDesc, JobContact.* ; > FROM workman_MAIN!JobContact ; > LEFT Outer Join workman_MAIN!AddressBook ; > ON JobContact.fk_ab_id = AddressBook.ab_id ; > Join workman_MAIN!lu_Catagory ; > ON JobContact.fk_Type_id = lu_Catagory.pk_id ; > WHERE JobContact.fk_job_id = ?Thisform.ncurid ; > ORDER By CompanyThe problem is with the ConName field. PADR the entire field so that it is always ends up being the same length. The field length is based off of the first record in the SELECT. As follows -
Create Sql View "JOBCONTACT_2" ; AS Select AddressBook.Company, ; PADR(Iif(Empty(AddressBook.Prefix), "", Alltrim(AddressBook.Prefix) + " ") + ; Iif(Empty(AddressBook.FirstName), "", Alltrim(AddressBook.FirstName) + " ") + ; IIF(Empty(AddressBook.MiddleName), "", Alltrim(AddressBook.MiddleName) + " ") + ; IIF(Empty(AddressBook.LastName), "", Alltrim(AddressBook.LastName) + " ") + ; IIF(Empty(AddressBook.Suffix), "", Alltrim(AddressBook.Suffix)), 120) As ConName, ; lu_Catagory.cDesc, JobContact.* ; FROM workman_MAIN!JobContact ; LEFT Outer Join workman_MAIN!AddressBook ; ON JobContact.fk_ab_id = AddressBook.ab_id ; Join workman_MAIN!lu_Catagory ; ON JobContact.fk_Type_id = lu_Catagory.pk_id ; WHERE JobContact.fk_job_id = ?Thisform.ncurid ; ORDER By CompanyDan