9 Replies Latest reply on Jan 23, 2009 1:09 PM by Joy Garon

    Update Lists

    Adam Meyer
      I was wondering if there is a way for a user to add to a list?

      For example, say we have a list of customers, and that list is used to define the customer variable for a project. If we get a new customer, is there a way to add that customer to the list. Or does the administrator have to do this?


      Thanks,

      Adam
        • Update Lists
          Joy Garon
          Hi Adam,

          You can change the users permission and select 'Can update card lists'.

          It does require going into the admin tool.

          Joy
          • Update Lists
            Adam Meyer
            Is there a way to have a template update a list? For example, a New Project template will add the project number to the list of project numbers?
            • Update Lists
              @adam

              One solution can be:

              1. Create a "Project List" with Data type: "From SQL Database", example code:

              Select distinct Vv.ValueCache
              From VariableValue Vv
              Where Vv.VariableID = ( select variableid
              from variable
              where Variablename like 'Project Number') And
              Vv.ValueCache != ''
              order by Vv.ValueCache ASC

              2. On data card, create a Combobox Dropdown field linked to variable 'Project Number'. This variable should have Special value as "Project List" which was created above.

              3. So when a user put a new value of Project Number in the field and save the card, this value will be recorded into the SQL database and later on appears in the list 'Project List'
                • Update Lists
                  Joy Garon
                  Hi Adam -

                  I think I misunderstood your original question :-)

                  When you design your data card you can use 'Combobox Dropdown' control which acts as both an 'Edit' control (user types in a new value) and as list control (display a list).

                  As Kim mentioned, you can then use a T-SQL Query to populate the list.
                  The sample from the book that Kim posted basically has a sub-query that obtains the 'VariableID' (which is a number) from the 'Variable' table where the 'VariableName' = myvariablename and then passes the 'VariableID' to the main query which reads the actual values from the 'ValueCache' column of the 'VariableValue' table where the 'VariableID' is the value passed by the sub-query.

                  Regards,
                  Joy
                • Update Lists
                  Adam Meyer
                  Just wanted to say thanks for the help. I got the list updating properly.

                  However, I've stumbled on another problem - once a value is entered in the database, how can I delete it?

                  Adam
                  • Update Lists
                    The only way I could think of is to go to the database and query a DELETE ..
                      • Update Lists
                        Joy Garon
                        Adam -

                        Be very careful here. Modifications (DELETE) to the database via a query are NOT supported.

                        A better method would be to filter the query using a where clause to exclude the undesired items on the list. This would keep the history intact.

                        Regards,
                        Joy