csv export created by NAME

đź’¬ General Discussion
1

Hi to all, there is a way to see in the field “created by” the username instead of the id of the user?

thanks all

2

Where exactly is this, which module?

Normally there are two different fields available, one is id, the other is name. At least I’ve seen this on some Email templates regarding the Assigned user, which is a similar case.

3

Here it is
(column R and S)

4

I was asking where you’re producing the list from, not for the actual list. I would need to know which screens produce this result, so I can look for the code, etc.

Any way, try this tip:

https://community.sugarcrm.com/thread/21054

5

oh sorry, misunderstand. I will try it.

Btw is the phonecall extraction

btw i think all export work like that

6

The solution is right, but not for me. because there is already a left join for the users to get the assigned_user_id, so i could not get the assigned_by username from that simple query, i have to create a view and edit all the “core” of the module, actually i don’t like the idea so i think it will stay like that.

Thanks btw

7

If you already have the JOIN to the users table, better. All you need to do is add assigned user name to the column list!

SELECT users.user_name, users.first_name, users.last_name, (etc)

8

Sadly nope, because the join is between accounts.assigned_user_id = users.id

and i need accounts.created_by=users.id

because i need the username that created the record

9

The JOIN clause only instructs MySQL how to relate the two tables. It is always based on the keys.

Once they’re related, trust me, you can get all fields from both tables on the SELECT clause. So leave the JOIN as it is, and add the fields you need to the SELECT clause.

10

No, because the join go to select the user assigned for the information.
If i have a call assigned to “user A” but created by “user C”, the join will be always for the “user A” so i can’t get the info of the “user C”

11

Ok, I understand now.

Can you give me the current complete SQL Query? I’ll try and get you a working query in phpMyAdmin and then you can put it in PHP code.

12

mine is like that


SELECT calls.*, users.user_name as assigned_user_name FROM calls 
LEFT JOIN users ON calls.assigned_user_id=users.id 

where calls.id in ('bc65a6de-c4c1-e54e-c0fb-5ae19a7d027e','be932c7c-577f-6dfe-438e-5af05dfe62c8','ebc54f73-ab1f-9025-af3f-5afadff7c4ed','78847c70-a931-181d-1c37-5b320dbbaf7a','2569119f-a007-5945-42df-5afae27fb148','25d780e2-0dc0-9ea7-ac30-5af04f6d35b8','2851280b-03ab-9285-5739-5ae1982db569','a84d87cd-333f-de2b-b197-5af060bc3bc0','12d059b5-e908-1122-1a9e-5acb2fc7d593','193a1d69-f361-dbea-583f-5a3a8d9d74d8','8407733b-e49d-f2a9-0158-5a3b908bd1a6','8bfc69d7-f3ff-5651-dea2-5af16b045bb2','9834b289-5885-c089-74a8-5abe5a8436fc','395cd4c8-0f2c-b817-f84a-5afa96373c01','4ff00621-3139-8bde-af04-5ac9d08f18ab','6f095765-b6ad-274f-e428-5a96d7308600','75c102be-de43-a35e-2f69-5afd8eda6f3d','76e50169-310c-d47f-b2cd-5ae19a306bf7','293abc24-417d-f719-4add-5abe5bb9a416','29c4e494-032b-c21d-014a-5ad9ed29bb0b') AND calls.deleted=0 ORDER BY calls.name
13 

SELECT calls.*, u1.user_name as assigned_user_name ,
u2.user_name  as created_user_name, calls.*
FROM calls 
LEFT JOIN users u1 ON calls.assigned_user_id=u1.id 
LEFT JOIN users u2 ON calls.created_by=u2.id 

where calls.id in ('bc65a6de-c4c1-e54e-c0fb-5ae19a7d027e','be932c7c-577f-6dfe-438e-5af05dfe62c8','ebc54f73-ab1f-9025-af3f-5afadff7c4ed','78847c70-a931-181d-1c37-5b320dbbaf7a','2569119f-a007-5945-42df-5afae27fb148','25d780e2-0dc0-9ea7-ac30-5af04f6d35b8','2851280b-03ab-9285-5739-5ae1982db569','a84d87cd-333f-de2b-b197-5af060bc3bc0','12d059b5-e908-1122-1a9e-5acb2fc7d593','193a1d69-f361-dbea-583f-5a3a8d9d74d8','8407733b-e49d-f2a9-0158-5a3b908bd1a6','8bfc69d7-f3ff-5651-dea2-5af16b045bb2','9834b289-5885-c089-74a8-5abe5a8436fc','395cd4c8-0f2c-b817-f84a-5afa96373c01','4ff00621-3139-8bde-af04-5ac9d08f18ab','6f095765-b6ad-274f-e428-5a96d7308600','75c102be-de43-a35e-2f69-5afd8eda6f3d','76e50169-310c-d47f-b2cd-5ae19a306bf7','293abc24-417d-f719-4add-5abe5bb9a416','29c4e494-032b-c21d-014a-5ad9ed29bb0b')
 AND calls.deleted=0 ORDER BY calls.name

You just need to do a second JOIN to the same table, and you need to use aliases (u1, u2) to make that possible.

1 Like
14

Wow thanks a lot! i will try for sure

15

Hi,
When i export the calls module Related to field give id in csv file, but i want when user export calls module then it give name at the place of id.
How we write the code for this and in which path.