"Leads" list page does not show list: bad server response

[olamborelle]

Hi,
New user to EspoCRM, and new to forum,

I have created at least one Lead, but the Leads overview page does not work. "Bad Server Response":



Thank you for any hint or suggestion in trying to resolve this,

Kind regards,

Olivier.

[tanya]

Hi,
check EspoCRM log (data/logs) and server error log, please

[olamborelle]

Thanks, we will (I need our IT guys to do it).
It seems my image above was not posted. The browser console says:

Server side error 200: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DESC LIMIT 0, 50' at line 3

[tanya]

If list view of other entities works, press Reset button on the Leads Search panel

[olamborelle]

Thanks,
Yes, list view other entities work.
Reset gives the same error / javascript console message.

[tanya]

this error was from the beginning or after some customization?

[olamborelle]

As far as I know, from the beginning. Leads were not used so far; I started to test them and added them to the UI. Accessing one specific Lead works (for example from my activity stream, where it tells me I created a Lead).

[tanya]

add here (in your files) https://github.com/espocrm/espocrm/b.../Base.php#L255 the code

if ($entityType == "Lead") $GLOBALS['log']->error($sql);

[olamborelle]

Thanks, will check our IT guys.
Will that increase the logging also in the browser console or only on the server side logs?

[tanya]

only server log.
after you get this sql, remove this string

[olamborelle]

OK our IT guy could collect the following information:

(just as a reminder: although Leads exist in the database, the Leads page returns empty and shows "bad server response")

The requested url from the leads page looks like:

_/api/v1/Lead?[COLOR=#f79232]maxSize=50&offset=0[/COLOR]&[COLOR=#d04437]sortBy[/COLOR]=organisation&[COLOR=#14892c]asc=false[/COLOR]_

You can see the parameters after the question mark. They will be appended to a SQL query that will retrieve all leads (I added a color-mapping from parameters to SQL):

_WHERE lead.deleted = '0' [COLOR=#FF0000][B]ORDER BY[/B][/COLOR] [COLOR=#14892c][B]DESC[/B][/COLOR] [COLOR=#f79232]LIMIT[/COLOR] [COLOR=#f79232]0, 50[/COLOR];_

If we remove the 'ORDER BY DESC' part, the query will work. The reason is the missing sortBy value (organisation) from the request url between ORDER BY and DESC. The fix is:

[I]WHERE lead.deleted = '0' ORDER BY [B]organisation[/B] DESC LIMIT 0, 50;[/I]

Does that help? It seems a word is not transferred properly from the URL to the SQL How could we fix this?

[tanya]

Yes, thanks.
Administration > Entity Manager > Lead > Edit - what is in Default Order (field)?
also you can check custom/Espo/Custom/Resources/metadata/entityDefs/Lead.json > collection > sortBy

[olamborelle]

Yes, thanks.
Administration > Entity Manager > Lead > Edit - what is in Default Order (field)?

"Account"

(by the way we do not use Account)

[tanya]

Set needed field here and save

[olamborelle]

It works!!
Ah, thanks a lot....
I guess Account is the default but does not work if we do not use account? Although the code looks like "organisation" is the problematic parameter.