8 Replies Latest reply on Sep 25, 2013 7:43 AM by Brucer

    Good ways to use list data in a Web Form?

    MarXtar ITSMMVPGroup

      I'm creating some web forms and one of the fields will be a drop-down that contains a fixed list of values. This is nice and easy because I can choose to 'Create a List' and then add the values to the list in the web form definition.

       

      However, I would also like to use this list in a different form without having to type in the information again creating two lists to maintain. This would make me think I should use the 'Link to a table in the database' option. This is where things start go wrong for me.

       

      I can't see a simple way of creating and maintaining a list in a table without having to manually create a table in the database (outside of LDDA) and building some way of keeping it up to date via an import rule using another source such as a CSV.

       

      In short, is there a standard way of building a table, keeping that up to date, and then referencing it from a web form?

       

      A perfect example of this is the 'Map IP Gateways To Locations' web form. This allows me to update the contents of a table and reference this as many times as I like but this table already exists. I'd like the same functionality of list creatin/maintenance but without manual modification of the database as that will be difficult for my customer to maintain long-term.

       

      Mark McGinn

      MarXtar Ltd/MarXtar Corporation

      http://landesk.marxtar.co.uk

      LANDesk Expert Solution Provider

       

       

      The One-Stop Shop for LANDesk Enhancements

      - Wake-On-WAN - Distributed Wake-On-LAN, Scheduled Power Down, and SWDist Sequencing

      - State Notifier - Real-Time Device & User State Inventory Updating & Alerting

      Update - New Stand-Alone State Notifier Console for Service Desk Operators

      Update - State Notifier now detects machine and user Idle states

      Update - WoW & State Notifier now integrate for even more functionality

        • 1. Re: Good ways to use list data in a Web Form?
          Apprentice

          Mark, I'm interested in doing this too, have you gotten any further on this?

          • 2. Re: Good ways to use list data in a Web Form?
            Tom Farrugia SupportEmployee

            This is information a lot of people will need.  <nudge>ing this in hopes of a response from LANDesk.

            • 3. Re: Good ways to use list data in a Web Form?
              bnelson Employee

              IN LDDA Create a new rule in run SQL Command. Name it something that denotes you are creating a table of information. In the example provided we are creating a table named ORG_TableData and a column named location and populating with listed data (Shelf-Wash, etc) When asked for the command to run use the following format:

               

              CREATE TABLE ORG_TableData (location varchar(255))

               

              INSERT INTO ORG_TableData

               

              VALUES ('Shelf - Wash')

               

              INSERT INTO ORG_TableData

               

              VALUES ('Shelf - 1600')

               

              INSERT INTO ORG_TableData

               

              VALUES ('Shelf - HQ')

               

              INSERT INTO ORG_TableData

               

              VALUES ('Build')

               

              INSERT INTO ORG_TableData

               

              VALUES ('Deploy')

               

              INSERT INTO ORG_TableData

               

              VALUES ('Dispose')

               

              • Now when selecting attributes to display on your web form you will select the "Use list of values" box.
              • Select "Link to a table in the database"
              • Provide table name
              • Provide column name
              • Point to the attribute this data should be linked to

               

              All done.

              1 of 1 people found this helpful
              • 4. Re: Good ways to use list data in a Web Form?
                Tom Farrugia SupportEmployee

                Much appreciated!

                • 6. Re: Good ways to use list data in a Web Form?
                  MarXtar ITSMMVPGroup

                  Hi, while that works, it wasn't really what I was after as it requires someone to have LDDA rights and to be trusted to edit SQL scripts.  As a full admin that is a bit better than modifying a source file, but if an end user is involved (such as HR providing a managed list of department names in the absence as one example) then I would probably still use a reference import file.

                   

                  I think an ER for a web form option that allows us to modify the values in that table along the lines of the web page for maintaining the address list would be better for production purposes.

                   

                  Appreciate the response though.

                  • 7. Re: Good ways to use list data in a Web Form?
                    Apprentice

                    Although also having the option of a non-ldda admin manage this list, I believe this may work for me.  Many thanks for providing this!

                     

                    -Allen

                    • 8. Re: Good ways to use list data in a Web Form?
                      Rookie

                      Hi,

                       

                      Thought I'd share what I'd implemented for a customer recently.

                       

                      The basic Idea is to have a folder on the sql server that is shared out, the folder contains a csv with your lists that some user may edit. You then have scheduled task to run sql from LDDA to drop and recreate the table. Just make sure that your ldms sqluser has at least the sql bulkadmin role.

                       

                      I've attached a zip with a working example in LDDA_Lists.zip

                       

                      In the zip there is a folder "LDDA_Lists", containing "Table1.csv". The folder should be copied to the base of C: of your SQL server. "Add Dell System with location webform.xml" is a webform which will use the table for a lookup and "Table1 Sql.xml" which will add as a ldda sql command. The xml may be imported as a configuration in DTS.

                       

                      Your look up table to use the lists is Table1 and the columns are Col1 for Site and Table1 and Col2 for Location.

                       

                       

                      This is the SQL in the command with notes.

                       

                      ----------------------------------------------------

                      --Drop the table to clear it out, this errors at first time it is run as the table does not exist yet.

                       

                      DROP TABLE Table1

                       

                      --Create table whatever columns you need, in this case Col1, Col2.

                       

                      CREATE TABLE Table1

                      (

                      Col1 VARCHAR(80),

                      Col2 VARCHAR(80),

                      )

                       

                      --Insert the data, the drive path for the csv location is relative to the SQL server and your ldms sqluser needs at least the bulkadmin role.

                       

                      BULK

                      INSERT Table1

                      FROM 'C:\LDDA_Lists\Table1.csv'

                      WITH

                      (

                      FIELDTERMINATOR = ',',

                      ROWTERMINATOR = '\n'

                      )

                      ----------------------------------------------------

                       

                      the csv for table1.csv has to columns with

                      Site1,Room1

                      Site1,Room2

                      Site2,Room1

                      Site2,Room1a

                      Site2,Room2

                      Site3,Room1

                       

                      The LDDA SQL command should be scheduled nightly and the folder with the CSV shared out to whoever should be looking after the list…

                       

                      There is probable better ways to do this but at the moment it seems the simplest that a standard user may maintain.

                       

                      Cheers

                      Bruce