9 Replies Latest reply: Apr 18, 2010 9:10 PM by KRM240* RSS

    Database connection and what is the best method

    KRM240*

      Hi - I am new at this, so hopefully someone can point me in the right direction.

       

      looking at a project costing form where specific users need to complete a form with some prepopulated data expense data

      The data is in a local Access database at the moment, with plans on moving it to a web sql tables.

       

      Overview

      I would like to be able to open a main form, select various criteria

      (ie select Project Number)

      push button to load data the specific project data

      push button to extend reader functionality and save as with a new name based on project number

      After review, push button to send PDF to user by email (email address is in the loaded data).

      User is able to save data locally on his PC (save capability with the extended reader functions)

      When user finished, he can send pdf back to me for data upload to the database

       

      Question 1:

      I am looking at the ODBC connection strings and javascript to do this.

      Is this possible? If so, what is the best way to do this?

       

      Question 2:

      Once the "extend features" has been used, can javascripts be used to load different data or extended the Reader functions can only be done after the data is loaded?

       

      Any help or assistance as to where to look would be greatly appreciated.

        • 1. Re: Database connection and what is the best method
          pguerett techies

          As long as your database provides an ODBC driver then it can be used to access the DB. Please see my answers below for the rest of your queries.

           

          Paul

           

          I would like to be able to open a main form, select various criteria (ie select Project Number) push button to load data the specific project data

          <answer> This is doable. There are samples published but note that it will involve some javascript coding to accomplish

           

          push button to extend reader functionality and save as with a new name based on project number

          <answer> This woudl have to be done through Acrobat and woudl involve some manual intervention. Do you intend on sending the form with the data connection information to them? I woudl recommend that a separate form be used for this. The data connection may cause problems for them when they open the form (remember that they will not have access to the DB).

           

          After review, push button to send PDF to user by email (email address is in the loaded data).

          <answer> not an issue

           

          User is able to save data locally on his PC (save capability with the extended reader functions)

          When user finished, he can send pdf back to me for data upload to the database

          <answer> The form that they have will not be able to do DB connections so I woudl send back just the data. Then you can import it into your version of the form (that does DB connections) and you can do the update from that.

           

          Question 1:

          I am looking at the ODBC connection strings and javascript to do this.

          Is this possible? If so, what is the best way to do this?

           

          <comment> I just did a presentation on this topic .....here is the playback recording. It also has samples that show how to do it.

          http://adobechats.adobe.acrobat.com/p69655795/

           

          Question 2:

          Once the "extend features" has been used, can javascripts be used to load different data or extended the Reader functions can only be done after the data is loaded?

           

          <commment> The RE functionality in Reader will only allow them to save the PDF locally. I do not know how big this project is but if this is only for a few users, then I woudl buy Acrobat for your people (this gives them the ability to do the DB connection as well as RE to allow for the Customer to save the form locally). If this is a large project I woudl go down the LiveCycle Server path and woudl pre-populate the forms on the server. Note that this would be at least a 100K solution whereas the Acrobat woudl be the price of Acrobat times the number of users that you have.

           

          Make sense?

          • 2. Re: Database connection and what is the best method
            KRM240* Community Member

            Paul - that tip on the web cast was great! Many thanks

            I have the first criteria working with the code that you provided.

             

            Question on an enhancement to your code.

            I have the first filter working, say CompanyID

            I need to have 2 more filters, Year, and Project where the relation between Company and year and year and Prroject are both one to many

            So the initial filter by Company works fine, now how do I get a drop down to show the Years available for the company selected

            Below, I was trying to just get the 2nd filter to work.

            I am using your code from te drop down list in the 4-Populate+DD+then+select+specific+record.pdf example

             

            I thought it would be simply copy your code and use the following right after the first variable was selected:

            while(xfa.sourceSet.nodes.item(nIndex).name != "ClientYears")

            oDB.nodes.item(1).query.select.nodes.item(0).value = "Select * from ClientYears where ClientID = '" + inName + "'";

            Where inName is the CompanyID from the first selected criteria and the CLientYears is the table with the years that are appropriate.

             

            However not working, the last line of code above (in blue) turn blue (not normal black) for some reason....

             

            • 3. Re: Database connection and what is the best method
              pguerett techies

              These are always difficult without seeing the assets .... but if inName is the Name of t he DDlist then you will need to reference a property called rawValue to get the actual contents of the field. So it should be inName.rawValue

               

              If you hit Ctrl-J when you render the form in preview you will see all javascript errors when the program runs.

               

              Paul

              • 4. Re: Database connection and what is the best method
                KRM240* Community Member

                Hi Paul

                First - thanks for the tips so far!

                 

                Working on a project that requires 2 drop downs to filter the next third drop down.

                Need user to select ClientID and YearID

                I have the code working for the first dropdown (ClientID), but unable to get the second drop down to show JUST the data related to the selected clientID.

                 

                I am using these Data connections:

                Clients -  providing the ClientID's

                ClientsDetail -  Same as above but is the selected record from the first Drop down (ClientID)

                Years -  providing the ClientID's and YearID

                 

                The first drop down has the following JavaScript (selecting the Client ID and displaying the selected data from ClientsDetail

                form1.#subform[0].SelectedClientID::change - (JavaScript, client)

                var inName = xfa.event.newText;
                if (inName == ""){
                app.alert("You must enter a valid name - try again!")
                }
                var nIndex = 0;
                while(xfa.sourceSet.nodes.item(nIndex).name != "ClientsDetail")
                {
                nIndex++;
                }

                var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified
                oDB.nodes.item(1).query.setAttribute("text", "commandType");
                oDB.nodes.item(1).query.select.nodes.item(0).value = "Select * from Clients where ClientID = '" + inName + "'";
                //now connect to DB and get a record
                oDB.open();
                oDB.close();

                 

                So now I have the selected ClientID in SelectedClientID.rawValue

                How do I code a second Drop down to pull data from Years with the filter that uses this SelectedClientID.rawValue?

                What I am looking for is the setup code that will allow the following SQL select to work

                var myClientID = SelectedClientID.rawValue;

                "Select * from Years where ClientID = '" + myClientID + "'";

                 

                Any pointers would be greatly appreciated.  Thanks

                • 5. Re: Database connection and what is the best method
                  pguerett techies

                  This is pure speculation without seeing the database structure or the form. Are the values that populate the DDlists in different tables or the same one? If they are in different tables then I think you  will need a different data connection per table. If they are the same then you shodul be able to define the SQL statement that you want to run in the current code.

                   

                  Lastly which event are you using to run the second query? I suggest that you use the exit event of the DDList and not the change event.

                   

                  Paul

                  • 6. Re: Database connection and what is the best method
                    KRM240* Community Member

                    wrt

                    Are the values that populate the DDlists in different tables or the same one? - Yes different ones

                    If they are in different tables then I think you  will need a different data connection per table. I agree.

                    If they are the same then you shodul be able to define the SQL statement that you want to run in the current code.

                     

                    I suggest that you use the exit event of the DDList and not the change event. - Will try

                     

                    At the moment, using a button to initiate the data source for the #2 dDown with the following code

                    var myClientID2 = SelectedClientID.rawValue;

                    var nIndex = 0;
                    while(xfa.sourceSet.nodes.item(nIndex).name != "Years")
                    {
                    nIndex++;
                    }

                    var oDB2 = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified


                    oDB2.nodes.item(1).query.setAttribute("text", "commandType");
                    oDB2.nodes.item(1).query.select.nodes.item(0).value = "Select * from Years where ClientID = '" + myClientID2 + "'";

                    //now connect to DB and get a record

                    oDB2.open();

                     

                     

                    But nothing works (totally lost)

                    • 7. Re: Database connection and what is the best method
                      pguerett techies

                      Are you getting any errors in the javascript console (Ctrl-J) from acrobat?

                       

                      Paul

                      • 8. Re: Database connection and what is the best method
                        KRM240* Community Member

                        No errors reported.

                        • 9. Re: Database connection and what is the best method
                          KRM240* Community Member

                          Case solved, used data drop down lists from the custom  object library and w

                          ith slight mod in the code, have it all working just great.