Hello Norske,

If the field_id is different means it is not the address custom field and you cannot manually update the datakey column. For example my Address field ID is 134, all the data of that ID will have 134 as a field ID even if the data column is empty. By the way, you can send the screenshot in the Optional Information field in the Site Details section below the reply form. Only Stackideas' support team will be able to view it.

OK, didn't know about the attachments being private. Here they are.
What should I do? Should I change the field ID of the bad records via SQL?

Hey Norske,

Some of these data could be added since 1.1.x or 1.2.x, and their datakey could be different but it's best that you run the SQL query below to get an idea of which app element and which profile type the field data is being associated to:

select a.*, e.element, d.title from jos_social_fields_data as a

inner join jos_social_fields as b

on a.field_id = b.id

inner join jos_social_fields_steps as c

on b.step_id = c.id

inner join jos_social_profiles as d 

on c.uid = d.id and c.`type` = 'profiles'

inner join jos_social_apps as e

on b.app_id = e.id;

OK, the result of the SQL query is attached (change to .csv extension). How should I use this? Can you give an example?

Hey Norske,

I am not entirely sure what you are trying to achieve here. The problem is that there's no way to really know which types of data is used as the datakey. The best way I would think is to request your users to edit the profile and save it again.

I was trying to achieve a SQL query that returned a username, first and last name, address, city, state, zip for a report on the site.
With address showing up in multiple fields I couldn't figure out how to create a query.
You are right that saving the record updated the field the address was stored in. I think I can make that work.

Hi Norske,

I am sorry for the delay of this reply.

Regarding the datakey for address field, by right when you upgraded your EasySocial to version 1.3, there is a maintenance script to convert the address data into multiple row format. Can you try re-run the maintenance script again? Go to your EasySocial backend under Maintenance -> Scripts and execute the script name 'Update address field data to multirow'. You should be able to find this script by filter with version 1.3.0. Please see: http://screencast.com/t/YwDa92uk

Please note: before you perform the above step, please backup your database first incase something unexpected happen, you still have the chance to revert back your database to initial state.

If running the maintenance scripts doesn't convert the address into multiple rows, then the only way to fix this is to request your users to re-save the address from their profile edit page.

Now, your user's address data should be in multiple row format. You can then retrieve these data based on the datakey. E.g.

select a.id, a.username, 

	(select d1.`data` from jos_social_fields_data as d1 where d1.uid = a.id and d1.type = 'user' and d1.datakey = 'first' limit 1) as `firstname`,

	(select d2.`data` from jos_social_fields_data as d2 where d2.uid = a.id and d2.type = 'user' and d2.datakey = 'last' limit 1) as `lastname`,

	(select d3.`data` from jos_social_fields_data as d3 where d3.uid = a.id and d3.type = 'user' and d3.datakey = 'address' limit 1) as `fulladdress`,

	(select d4.`data` from jos_social_fields_data as d4 where d4.uid = a.id and d4.type = 'user' and d4.datakey = 'city' limit 1) as `city`,

	(select d5.`data` from jos_social_fields_data as d5 where d5.uid = a.id and d5.type = 'user' and d5.datakey = 'state' limit 1) as `state`,

	(select d6.`data` from jos_social_fields_data as d6 where d6.uid = a.id and d6.type = 'user' and d6.datakey = 'zip' limit 1) as `zipcode`

	from jos_users as a

limit 10

;

Hope this help and have a nice day!