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.

[Former Community Member]

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?

[KRM240_]

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....

[Former Community Member]

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

[KRM240_]

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

[Former Community Member]

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

[KRM240_]

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)

[Former Community Member]

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

Paul

[KRM240_]

No errors reported.

[KRM240_]

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.