Error unknown column in field list on edit screen

[Keith-i]

I thought I was getting the hang of this but maybe not!

I have a form with three lookups on the edit screen. None of these lookups are currently working which is perplexing me. The debug shows roughly the same message for each lookup form.

Code: Select all

[0] : ===USER==========

globeadmin

===PDO MESSAGE===

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Property' in 'field list'

===SQL===========

SELECT
idProperties,
BuildingName,
Property Name
 FROM     tblProperties         LEFT JOIN tblRoads ON tblRoads.idRoads = tblProperties.id_Roads
WHERE
`idProperties` = ?

===BACK TRACE====

/var/www/html/rcsdb/core/nuform.php - line 792 (nuRunQuery)

/var/www/html/rcsdb/core/nuform.php - line 383 (nuGetLookupValues)

/var/www/html/rcsdb/core/nuapi.php - line 92 (nuGetFormObject)

The select statement seems to have an extra line 'Property Name' introduced which is not a field in my table.

Similarly Apllicant Name appears twice in the SQL for the Apllicant form again with a space introduced. The SQL on the actual form is just SELECT * FROM tblApplicants so I'm not quite sure how these errors are getting introduced.

Code: Select all

[0] : ===USER==========

globeadmin

===PDO MESSAGE===

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Applicant' in 'field list'

===SQL===========

SELECT
idApplicants,
ApplicantName,
Applicant Names
 FROM tblApplicants
WHERE
`idApplicants` = ?

===BACK TRACE====

/var/www/html/rcsdb/core/nuform.php - line 792 (nuRunQuery)

/var/www/html/rcsdb/core/nuform.php - line 383 (nuGetLookupValues)

/var/www/html/rcsdb/core/nuapi.php - line 92 (nuGetFormObject)

[kev1n]

Could you post the lookup's SQL as you entered it?

[Keith-i]

Morning kev1n

Here's the SQl in the main browse form

Code: Select all

SELECT
 tblInstructions.*,
    tblClients.*,
    tblApplicants.*,
    tblProperties.*,
    tblRoads.*

FROM
    tblInstructions
        LEFT JOIN tblClients ON tblClients.idClients = tblInstructions.id_Clients
        LEFT JOIN tblApplicants ON tblApplicants.idApplicants = tblInstructions.id_Applicants
        LEFT JOIN tblProperties ON tblProperties.idProperties = tblInstructions.id_Properties
        LEFT JOIN tblRoads ON tblRoads.idRoads = tblProperties.id_Roads

I have 3 lookups, namely Client , Property and Applicant. Here is the SQL in the form that the 'Client' Lookup is linked to

Code: Select all

SELECT * FROM tblClients

There is similar in the Applicant and Properties lookup forms.

[kev1n]

You must be doing something differently. I'm not able to replicate your issue:

lookup_form.png

Lookup Form:

client_lookup.png

SQL Lookup form:

Code: Select all

SELECT * FROM tblclients

[Keith-i]

Thanks for looking. I may delete the forms that my lookups are pointing to and recreate them to see if that makes any difference. Oddly I have a separate (but simpler) database with a very similar lookup arrangement which is set up excatly the same way and that works fine.

[kev1n]

The SQL statements that are output in nuDebug look completely different to

Code: Select all

SELECT * FROM tblclients

Are you sure the SQL in the lookup forms is not different?

[Keith-i]

Hi
Yes, absolutely positive they are the correct SQL on the forms. Before creating new forms I might just delete the lookup objects and recreate them. Is it possible there is some temporary table/view created which has somehow got some corrupted SQL saved in it?

[kev1n]

Temporary tables are just created if you use PHP in BB (Before Browse), otherwise not.

[Keith-i]

Found my error! I had not selected a correct field name under the 'Description' box for the lookup, I had just entered my own text. Apolgies for wasting your time