Putting

[pundip]

I have created a MySQL view using the below query:

SELECT Suburb, Year, SUM(IncidentsRecorded), COUNT(IncidentsRecorded) FROM fabrik_crime
GROUP BY Year, Suburb

What is the best way to create a list from this? Obviously it will be a read only list that needs to be searched. Is there any way this is possible?

I keep on getting error 500 everytime I try it. I have set "Alter field types" to No and added an internal id field ID and no luck.

 

[Sophist]

You cannot create an internal id on a view.

I would create a concatenated Year + Suburb field in the view to be a unique field, and set that as the Primary Key for the list.

You should also set the List / Advanced / Alter field types to "No" to reflect that this is a readonly list that cannot be changed by Fabrik.

 

[cheesegrits]

Typically you should be able to include the PK from the source table in the view, like ...

SELECT id, Suburb, Year, SUM(IncidentsRecorded), COUNT(IncidentsRecorded) FROM fabrik_crime
GROUP BY Year, Suburb

... and then designate that as the "primary key" in the settings for the list you build from the view.

-- hugh