Populate Dropdown list with data from a database or a file csv

Submitted by frosso on 2018-10-24

Hi,

I need to know if it's possible and how it's possible to populate a dropdown list with a query from a database and/or by reading a csv file.

Example: a user should be able to select from an employee list that should always update with a table on the database or with a csv file that contain the list of users.

If it's possible by coding I just need to know where should I look in the source code and what you suggest to change to do what I need to achive.

Thank you.

You would have to update the definition of the dropdown attribute. It is stored in the attr_def column of the attr_types table. The definition has the following format:

ENUM items={"one","two","three"}

Regards,
Michał

Ok I tried to find dropdown attr_def and attr_types o webissues folder and I found many query that I should be able to change. Problem is that I'm not sure what file I should modify for a dropdown list.

I think it may be:

issuemanager.inc.php

historyprovider.inc.php

or

typemanager.inc.php

But I'm not 100% sure. Of course I will try to understand it today but if in the meantime I decided to just ask here.

The modifyAttributeType() method in typemanager.inc.php updates the attibute definition, so you can use that.

Regards,
Michał

Ok I tried but this is not what I really need. I need to change the select query done during the Add issues/Modify issues that select all attr_def based on the attr_name. I want to change it so that if I name the dropdown list with a specific attr_name I should be able to do a query (I don't want to do it by updating the table used by webissues, I already have one and I want to use it so that it will always be updated. After I receive data I can always make a text with the correct format.

I already have an idea how to do all that, it's just difficult for me to understand where the query is done (I don't have lot's of experience with php debugging).

Thank you.

 

PS. I think the function that I need is getAttributeType but I'm not sure because it use the attr_id and even if I select a static attr_id nothing change.

I don't think that changing the attr_def every time you read it from the database is a good idea. A simpler solution would be to synchronize the attr_types table with the external data source every few minutes using cron/scheduled task. Then you can create a simple script which does this synchronization and you don't have to modify any other code.

Regards,
Michał

Ok, I will just do that, thank you.

Small fast question, why standard user see all issues in a project folder? It's possible to limit standard users to only see issues created by them or assigned to them? (at least do not show All Issues view to standard users. I can create a only me default view and a All issues view for my admin, but I don't know how to lock/delete the default All issues view)

Currently it's not possible, though I'm planning to extend the roles and permissions in the future.

Regards,
Michał

In the end I was able to update my dropdown list with a simple service in C# that automatically execute every day.

for those who need something similar (records is a List of Dipendenti... the data that I got from a simple select in my database):

using (SqlConnection myConnection = new SqlConnection(con)){
  string dbUserListUpdate = "ENUM items={";
  bool firstC = true;
  foreach(Dipendenti dipendente in records){
    dbUserListUpdate = firstC ? dbUserListUpdate + "\"" + dipendente.Cognome.Trim().Replace("'", "''") + " " + dipendente.Nome.Trim().Replace("'", "''") + "\"" : dbUserListUpdate + ",\"" + dipendente.Cognome.Trim().Replace("'", "''") + " " + dipendente.Nome.Trim().Replace("'", "''") + "\"";
    firstC = false;
  }
  dbUserListUpdate = dbUserListUpdate + "}";
  string oString = "update Prova001_attr_types set attr_def = '"+ dbUserListUpdate +"' where attr_id=11;";
  SqlCommand oCmd = new SqlCommand(oString, myConnection);
  myConnection.Open();
  oCmd.ExecuteNonQuery();

  myConnection.Close();
}

 

For the standard users issues problem the only solution I can think of is to just create a new project for each user (each project will just be accessible the admin and a single standard user).

Of course I will wait for the extension of permission update. I think a way to just hide the All issues view to standard users and change the default view with a personal one could be a simple solution.

Anyway, now I just need to understand if my project manager approve webissues or I will need to try another software.