.csv contact import

Filippo

Hi there,

I'm running osTicket with this config:

Versione osTicket v1.12.2 (a5d898b)
Web Server Software Apache/2.4.29 (Ubuntu)
Versione MySQL 10.3.18
Versione PHP 7.3.9-1+ubuntu18.04.1+deb.sury.org+1

I've added a custom field to my contact form with a variable name ('microsite'), like this:

The value comes from a List I've built from the admin panel.

Now I'm trying to import a value for my 'microsite' field along with contact details (name, email) using a .csv file.
Name and email fileds are populated, but I'm not able to save a value for my custom field.
I've tried to use the the 'abbreviated' values of my list and the item ID, but no way.
The .csv file I use should be ok, it has three columns only (name, email, microsite) and only the 'microsite' column gets ignored.
Any idea/suggestion anyone?

Cheers,
Filippo

ntozier

Go to Agent panel -> Users
Click on Import.
Click on Upload.
select the file.
Click Import Users.

file format is described on the screen after you follow step 3.

Email,Name,Phone,Notes,test
aadvark.doe@osticket.com,Aavark Doe,1112223333X4444,test user,a value for test

Filippo

Hi Tozier!

Yes, that's what I did. Fields 'name' and 'email' are correctly populated, but my custom field is not.
If I modify the field from the admin panel, I see table ost_user__cdata getting populated as needed, but using my .csv file I found no way of doing the same, either using the key (ex. 'jumptravel') or the key ID (ex. 22) of my list's values (taken from table ost_list_items), like this:

Email,Name,microsite pippo@example.com,pippo,jumptravel

and this:

Email,Name,microsite pippo@example.com,pippo,22

Importing these two files results in these values saved in my DB (with different user_id, of course):

MariaDB [lost_db]> select * from ost_user where id=5492;
+------+--------+------------------+--------+-------+---------------------+---------------------+
| id | org_id | default_email_id | status | name | created | updated |
+------+--------+------------------+--------+-------+---------------------+---------------------+
| 5492 | 0 | 5492 | 0 | pippo | 2019-10-01 08:32:47 | 2019-10-01 08:32:47 |
+------+--------+------------------+--------+-------+---------------------+---------------------+
1 row in set (0.001 sec)`

So, I'm probably doing something silly here... but what?

Cheers,
Filippo

Filippo

Hi again,

further test, with all importing fields valued, like this:

email,name,microsite,phone,notes pippo@example.com,pippo,22,0123456789,No particular notes

Importing definitely works, except for my custom field :-(

`MariaDB [lost_db]> select * from ost_user where id=5502;
+------+--------+------------------+--------+-------+---------------------+---------------------+
| id | org_id | default_email_id | status | name | created | updated |
+------+--------+------------------+--------+-------+---------------------+---------------------+
| 5502 | 0 | 5502 | 0 | pippo | 2019-10-01 10:53:42 | 2019-10-01 10:53:42 |
+------+--------+------------------+--------+-------+---------------------+---------------------+
1 row in set (0.000 sec)

function addDynamicData($data) { error_log(implode(", ",$data)); return $this->addForm(UserForm::objects()->one(), 1, $data); }
This gets logged:
[Tue Oct 01 11:15:49.368654 2019] [php7:notice] [pid 28635] [client 192.168.1.123:47700] pippo@example.com, pippo, {"22":"Jump"}, 0123456789, No particular notes, referer: https://myrealservername/scp/users.php

This tells me data are correctly received... but not saved, at least my custom field.
What am I doing wrong?

Thank you,
Filippo

ntozier

custom fields are not stored in ost_user.

Did you go to Admin panel -> Manage -> Forms -> Contact Details: and add the custom field?

Filippo

Hi Tozier,
yes I did, It was in first post.
The field in fact exists in ost_user__cdata table (see above queries).

Thank you,
F.

ntozier

ost_user___cdata is a temporary table. The data is not stored there (although it may be in there at least temporarily). It is stored in ost_form_entry_values.

Filippo

Hi Tozier,

well, after your suggestion, I've also checked tables ost_form, ost_form_field, ost_form_entry and ost_form_entry_values.
First I've imported this file (a single line csv):
email,name,microsite,phone,address,zipcode,notes,city pippo@example.com,pippo,22,0123456789,Rue Orangerie 8,12345,No particular notes,Neuchatel

Then I queried above tables:
`
MariaDB [lost_db]> select * from ost_user where name="pippo";
+------+--------+------------------+--------+-------+---------------------+---------------------+
| id | org_id | default_email_id | status | name | created | updated |
+------+--------+------------------+--------+-------+---------------------+---------------------+
| 6369 | 0 | 6369 | 0 | pippo | 2019-10-02 14:28:44 | 2019-10-02 14:28:44 |
+------+--------+------------------+--------+-------+---------------------+---------------------+
1 row in set (0.003 sec)

MariaDB [lost_db]> select * from ost_form_entry where object_id=6369;
+------+---------+-----------+-------------+------+-------+---------------------+---------------------+
| id | form_id | object_id | object_type | sort | extra | created | updated |
+------+---------+-----------+-------------+------+-------+---------------------+---------------------+
| 7689 | 1 | 6369 | U | 1 | NULL | 2019-10-02 14:28:44 | 2019-10-02 14:28:44 |
+------+---------+-----------+-------------+------+-------+---------------------+---------------------+
1 row in set (0.005 sec)

MariaDB [lost_db]> select * from ost_form_entry_values where entry_id=7689;
+----------+----------+---------------------+----------+
| entry_id | field_id | value | value_id |
+----------+----------+---------------------+----------+
| 7689 | 3 | 0123456789 | NULL |
| 7689 | 4 | No particular notes | NULL |
| 7689 | 41 | NULL | NULL |
| 7689 | 42 | Rue Orangerie 8 | NULL |
| 7689 | 43 | 12345 | NULL |
| 7689 | 44 | Neuchatel | NULL |
+----------+----------+---------------------+----------+
6 rows in set (0.001 sec)

MariaDB [lost_db]> select * from ost_form_field where form_id=1;
+----+---------+--------+--------+----------------+-----------+---------------------------------------------+------+------+---------------------+---------------------+
| id | form_id | flags | type | label | name | configuration | sort | hint | created | updated |
+----+---------+--------+--------+----------------+-----------+---------------------------------------------+------+------+---------------------+---------------------+
| 1 | 1 | 489379 | text | Email Address | email | {"size":40,"length":64,"validator":"email"} | 1 | NULL | 2019-09-10 12:29:28 | 2019-09-10 12:29:28 |
| 2 | 1 | 489379 | text | Name | name | {"size":40,"length":64} | 2 | NULL | 2019-09-10 12:29:28 | 2019-10-02 08:37:44 |
| 3 | 1 | 13057 | phone | Phone Number | phone | NULL | 4 | NULL | 2019-09-10 12:29:28 | 2019-09-24 14:53:29 |
| 4 | 1 | 12289 | memo | Internal Notes | notes | {"rows":4,"cols":40} | 7 | NULL | 2019-09-10 12:29:28 | 2019-10-02 08:33:09 |
| 41 | 1 | 13057 | list-4 | Microsite | microsite | NULL | 3 | NULL | 2019-09-24 14:53:14 | 2019-09-24 14:53:29 |
| 42 | 1 | 13057 | text | Address | address | NULL | 5 | NULL | 2019-10-02 08:28:21 | 2019-10-02 08:33:09 |
| 43 | 1 | 13057 | text | CAP | zipcode | NULL | 6 | NULL | 2019-10-02 08:32:52 | 2019-10-02 08:33:09 |
| 44 | 1 | 13057 | text | City | city | NULL | 8 | NULL | 2019-10-02 08:37:11 | 2019-10-02 08:37:11 |
+----+---------+--------+--------+----------------+-----------+---------------------------------------------+------+------+---------------------+---------------------+
8 rows in set (0.000 sec)
`
As you can see, the problem arises only for the 'list' field, other text fields get populated correctly.
I'd think something's not working with csv-imported list values (only).
Or I'm doing it somehow wrong... but I'm running out of ideas about what to check or try.

Thank you,
Filippo

ntozier

I would agree with your assessment. Please open a Issue reports at:
https://github.com/osTicket/osTicket/issues/new

Be sure to include all this wonderful information here.

Filippo

Hi Tozier,

done: https://github.com/osTicket/osTicket/issues/5083

Thank you,
Filippo