×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need answer to whether adobe acrobat can pull data from sql

Need answer to whether adobe acrobat can pull data from sql

Need answer to whether adobe acrobat can pull data from sql

(OP)
Hello,

I am researching an issue for my company.  Because the look and feel of what I am trying to do using Crystal Reports is not great I need to resort to other software packages.  To explain, I have a government form(pdf), that I need to display and connect somehow to sql server database and pull information that I need from that database.  So for example, I need to tap into a database and fill in all the fields on the pdf form with user information.  Is this possible.  I have installed the full version of Adobe Acrobat 5.0.  Is this not the correct version or software that I should be using.  Is there a better software package to handle this.  I know crystal reports can do this as I achieved, however, once I put this report on the web, the pdf dpi are lost.  I have no idea how to fix it(have even tried the registry fixes) so someone told me adobe acrobat can do the same thing.  Your help is appreciated

Sal

RE: Need answer to whether adobe acrobat can pull data from sql

No, you cannot do this with Acrobat alone. You can of course write a program to query your database and create an Acrobat FDF.

Thomas D. Greer
http://www.tgreer.com
Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

(OP)
Sorry tgreer,

Bear with me here.  I am totally new to using the full version of adobe acrobat.  I am used to using the reader only.  How do I create an Acrobat FDF.  So I can use sql query to query the database and then do what.  Sorry could you explain a bit further or direct to some articles that I can research to understand this.  Thanks again tgreer.

RE: Need answer to whether adobe acrobat can pull data from sql

Sure! First, you need the full version of Acrobat to create a PDF Form. This is a PDF with fields that a user can fill in. With Acrobat, you create the fields, and can tie actions to those fields, validation scripts, and so forth. When the user completes the form and submits it, the form can be submitted to a web server, or the data can be saved in a local file. This Acrobat Data File is called an ".FDF" form.

For your purpose, you need to create an FDF form programmatically. One of the items that can be stored in an FDF is the name of a PDF to "merge" with.

Step 1. Create your form.

Step 2. Create a web or windows program that:

Step 2a. Queries your database
Step 2b. Writes the results to an FDF file
Step 2c. Loads the FDF/PDF file for viewing in reader or display on the web.

My good friend John Deubert has written a book on Acrobat Forms. His website is http://www.acumentraining.com.

Thomas D. Greer
http://www.tgreer.com
Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

Check out the Acrobat Forms Data Format (FDF) Toolkit at http://partners.adobe.com/asn/acrobat/forms.jsp - All of the information on doing this is included in the toolkit and related samples you can download.

RE: Need answer to whether adobe acrobat can pull data from sql

OK.  My question is similar, so I hope you don't mind me adding to this post.  I was handed this project, and I admidt that I am not sure where to start.  

We have many pdf documents which are basically images of forms.  For example, an IRS form.  Now my end goal is to be able to merge data with these documents, from a data source.  From what I have been looking at, I might be able to do this with Acrobat.

We have Acrobat 6.0 standard and 5.0 full.  I don't know how to take a pdf and make it into a form or how to create the fdf file to configure the link to the fields with the data source.  I would love to be able to do this, but I can't seem to find clear instructions of what I need and how to do this, step by step.

I tried to get the toolkit, but the password they sent me gives me insufficient priviledges to view the download.  

Another idea I was having was to somehow imbed the image of the form onto a single page in a word document.  Then just use mail merge from there.  I can't seem to get this to work right.

If you want to see what I mean, go to the irs website and download a w-9 form.  That is one that I am working with.

I would love to have this form in word, and then use merge to bring in the data, but I like the idea of adobe as well, since the "form" file is originally in adobe.

Thanks for any help you can give!

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

I have 5 professional installed and I have the form fields all set, and named (they already were!)

Now I just need to understand FDF files, how to create them, and set one up so I can test it.  It may be early, but lets say I get this set up to an access query... how would I set the parameter to ask for what record they want merged?  Thanks for any suggestions!

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

I'm not sure I understand your specific question. When the form is submitted, you submit it to a server page. Just like with a regular HTML form.

You don't need to submit it as an FDF, though you can. I would suggest submitting it as HTML, so that you don't have to do anything exotic on the server.

Thomas D. Greer
http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

OK, maybe I don't understand what FDF is supposed to be used for.  Let me explain my scenario, since what you said about server stuff confused the heck out of me.

I have users that have to fill out the same forms all of the time.  They are not filling the forms out for themselves.  They are filling them out for a client.  That client's data in stored in SQL server db.  So, we want to be able to take a PDF form that needs to be filled out, let the staff choose the client to fill out the form for, and have the form populate.  This should be very user end.

I am interested in any way that this can be accomplished.  If PDF is the format that I should keep the form, I will learn what I can to be able to allow end users to choose the client and have the form populate for them.  If I need to somehow bring the PDF into word ( the set up is entirely lost, it only brings over text) then I will have to completely recreate every form, and I don't think that's a realistic solution.  I am interested in any other ideas that anyone may have!  Thank you all so much.  I love that Tek-tips users always take the time to try to help each other with collaboration.

I have this post, which I was intending to send to Adobe, when I found a form from them to download the FDF toolkit.  It turned out that the submit button didn't work, so I couldn't send them my request for information.  Here is what I wanted to ask them.  Maybe someone here might be able to help me to understand more:

I don't know much about FDF, but my research has indicated that it may be the solution to what I am currently trying to create.  I have a form in Acrobat 5.0.  I want to be able to merge this form with data.  Preferably from an Access database, which would link to a sql server.  I want the end user to, in some form, be able to specify the record they want merged, and have it merge onto the pdf form.  We have many forms and this solution would be most helpful in saving time.  Thank you for any information or support you can give.  I would certainly like to test out the usability of the FDF toolkit if that is possible.

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

1) All of the client data is stored in a central SQL Server Database.

2) Users (your employees) are connnected to this server.

3) Users will open a PDF, and "bring in" data from the SQL Server into the PDF.

Correct?

I would suggest that you develop an Intranet-based client server application. That application would let your users:

1) Select a client data set.
2) Retrieve that data embedded in a PDF, served to them in a browser.

To do this, you need to write a Web Server application (in PHP, ASP, or ASP.NET) that will connect to the database, get the data, store the data as an FDF, import that data into a PDF, and return that PDF to the user.

You don't necessarily need the FDF Toolkit. FDF files are text files, so can be written easily.

If you understand this and think that it's the route you would like to take, please let me know, and we can go into more detail.

Thomas D. Greer
http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

I might as well just continue:

http://www.tgreer.com/tektips/form01.fdf

When you browse to that FDF, the FDF is served to the user's browser. The "FDF" is associated with Acrobat Reader (or whatever flavor of Acrobat the user has). Acrobat parses the FDF to see what PDF it needs. It then retrieves that PDF, and use the PDF as a container for the FDF data.

Here is the FDF:

CODE

%FDF-1.2
1 0 obj
<<
  /FDF
  <<
    /F(http://www.tgreer.com/tektips/form01.pdf)
    /Fields
    [ << /T(Text1) /V(Thomas) >>
      << /T(Text2) /V(Greer)  >>
    ]
  >>
>>
endobj
trailer
<</Root 1 0 R>>
%%EOF

You can see the relevant parts. The "<<" and ">>" delimiters define a structure called a "Dictionary". Dictionaries contain key-value pairs.

The FDF dictionary contains two keys: /F and /Fields.

The /F points to the PDF. In this case, I'm pointing back to a PDF on my webserver. Acrobat will go get it automatically.

The /Fields object contains the form data. It must exactly match the form fields in the PDF file. /T means "Textbox". /V means "Value".

So when I created that PDF, I created two textboxes, "text1", and "text2".

When I created the FDF, I made the /Fields dictionary match, with values for those two textboxes.

So, all your server needs to do is author the FDF, with a pointer to a specific PDF, and data from the SQL Database.

IMPORTANT NOTES:

This isn't particularly efficient. You have to hit the server twice: once to get the FDF, and again to get the PDF.  The only way around this is to embed the FDF directly into the PDF on the server. You'll need software to do this, and that software isn't cheap (one example: http://www.appligent.com/products/applications/fdfmerge/fdfmerge.html).

Secondly, this works in IE, because of the Window's association of the .fdf file extension with Acrobat. In a Mozilla/FireFox browser, the FDF file might simply display in the browser as a text file. To make this work with Mozilla, the Web Server must serve the FDF with the proper mimetype, which is "application/vnd.fdf".

If you found this post helpful, please support Tek-Tips by clicking some of the ads/sponsors. I would appreciate visitors to my site, as well, as it is also advertiser supported.

Thomas D. Greer
http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

Thanks for all the explaination.  I think this is a little advanced for me.  All I have really done with ASP pages is redirect urls and associated files like css and inc's to custom directories.  Other than that just changing appearance of sites with the css's and such.  Anyway
I have a post going now in the office forum.  

I am getting somewhere with the pdf as an image, inserted into word, and put with a mail merge.  This could have some hope.  Here is a link to that post: Thread68-961745
javascript:openindex(450,350,'http://www.tipmaster.com/includes/refinfo.cfm?w=450&h=350')

A little messy, because I don't know how to link to other posts.

Anyway.  Its a new one in the office forums and its a mail merge part that I am working on.  I think this avenue may be more of what I am looking for.  This also may show you what I am really, in the end, trying to do.  If you have any other suggestions for how to accomplish this I would love to hear.  I want to be able to recreate this for lots and lots of forms for our how company.

It would be really great!

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

but is this really going to be something I can accomplish?  I don't know about putting things on a server and giving all our users access to them.  What if I want to feed the data through Access.  can we still do this that way?

Thanks.

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

If you want to create a basic desktop solution, all you have to do is give the users the FDF. They double-click it, it opens Reader, and Reader will go and GET the PDF from your server. So you don't have to do web programming.

You do need a program to create a PDF though. I don't do Access programming, so can't give you specific advice.

But remember that FDF is just a text file.

So you need to:

1) Get the data from the database
2) Turn it into an FDF file
3) Give the file to user (or let them go get it)

The user opens the FDF, and the rest is "magic".

I think we've "completed" this thread, but I would be happy to help you offline. You can get my email address from my website.

Thomas D. Greer
http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

problem is that I don't see how the user ever gets to choose which client record from the data source they want for the form they are choosing.  If the FDF is hard coded with the field values, this won't work.  That would mean that we would have to have thousands of fdf files, one for each user.

See where my confusion is?

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

Well, how were you planning to let the user pick the client data with ANY solution? At some point, somewhere, somehow, there has to a program that allows the user to select a client. Right?

You wouldn't create the FDFs ahead of time. You create them as needed, dynamically, in real-time.

Thomas D. Greer
http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

With a mail merge the end user would open the word document.  They would see the form with merge fields all over, and the mail merge task pane on the right.  On that pane they would see a link saying select recipients.  They would click that and a box would open with the records from the connected data source.   They would put a check box by the record they want, and hit ok.  Then they would hit next on the pane, and then print on the pane.  Done.

Close, no saving.  Next user would open to see same process as above.

That is the kind of automation I am looking to create.

Even if it was a web page or something.  It would have a point for them to say what record( which client) do you want.  They would select, hit ok, and the form would show on the proceeding page with the record filled into the fields.

Does that explain the automation I am looking for?

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

Thanks for trying.

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

lol.  It does sound cool, its just not what I am looking to do.  You have to understand, the goal is to fill the form out for them, not save the form they fill out.  For this particular project, we have 40,000 records to choose from.  Its big!

But that's really nice of you to offer.  Maybe someone else will see the post and take you up on the offer!

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

I know we should drop this, but "filling out the form for them " is exactly what I'm proposing. If you click on:

http://www.tgreer.com/tektips/form01.fdf

that is exacly what happens. The PDF (form01.pdf) is filled out with the data in the FDF. And generating that FDF from an Access or SQL database with a web app would be a snap.

Thomas D. Greer
http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

ok, I got an error that said this file does not exist.  My confusion is that I thought this meant that an fdf file was created for each record.  How does the user select the record they want ( from a pretty interface/search) and select that record to merge with the pdf?
go ahead, wow me with how you can solve this one!

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

Data is in Server.

1) User goes to a web page, and selects a client.

2) The Web app connects to a database, and retrieves that client's data.

3) Web app creates an in-memory FDF, and streams it back to the user.

4) The FDF, now in the user's browser, connects to the server to retrieve the PDF.

5) The PDF, properly filled-out, is now displayed in the user's browser.

The FDF never was written to any hard-drive, so there is nothing to "clean up".

I'll write a sample application for you to test.

Thomas D. Greer
http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

Here is a very quick and dirty application:

http://www.tgreer.com/tektips/fdf_data.php

I chose PHP as the coding language, but it could just as easily be ASP.NET, ASP, etc. Same with the database. I build a very rudimentary database in MySql, but any database would work.

Here is what happens:

1) "fdf_data.php" queries a database, and display a list of names.

2) Clicking one of those names takes you to "fdf_query.php".

3) "fdf_query.php" queries the database for that name's complete data. It then builds an FDF using that data, and serves it back to the browser.

4) The browser loads Acrobat to process the PDF.

5) Acrobat reads the name of the PDF the FDF, and gets it from the server.

6) "fw9.pdf" loads in the browser, and Acrobat combines the PDF and the FDF.

7) Done. The user now has a completed PDF form.

Thomas D. Greer
http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

ok, so answer this for me, if you can...

How did the list of names end up on the query page?

This does have great possibilities!  I think this might be useful to me.

Just to let you know where I am at, this current project uses data that the staff works on in a custom app.  This app has a way for a user to go to a clients name, go to a tab, select multiple word docs ( pretend they are the same as a bunch of pdf forms) and then that clients record merges with all of them.  This is good because when this department uses these forms, it is a bunch of forms for one client at a time.  

In any event, other departments use their forms differently.  I am sure that there is a department that has to fill out the same form many times for different clients, and this would be wonderful.  Is it hard to set up?  

Could I set this up realistically for lots of different forms to lots of different data sources?

Can I control access to it? ie network login?, it would go on intranet I suppose...

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

Q: How did the list of names end up on the query page?
A: A PHP program queried the database and ouput the results as HTML:

CODE

<body>
<p>Click one of the following names to load that person's data into a PDF Form:</p>
<br/>

<?
$sql = 'SELECT name FROM `misscrf` LIMIT 0, 30';
ShowNames($sql);
?>

<?
function ShowNames($sql)
  {
    $res = mysql_query($sql);
    while ($row = mysql_fetch_array($res, MYSQL_ASSOC))
    {
      foreach ($row as $value)
      {
        echo "<a target='_blank' href='fdf_query.php?name=$value'>$value</a><br/>";
      }
    }
  }
?>
</body>

Q: Could I set this up realistically for lots of different forms to lots of different data sources?

A: The FDF format points to a PDF. The PDF has to have field names that match the FDF. As long as that is the case, the same form data can be attached to an indefinite number of forms. In fact, the web app could have two choices: pick the client, pick the form.

Q: Can I control access to it? ie network login?, it would go on intranet I suppose...

A: It's a web app. You can create any security you like, including custom written, integrated IIS or Apache, or Windows based security.

My point with all of this is to show, while no pre-packaged, wizard-driven, dummy-proof, Learn Spanish in 7 Days solution exists for populating PDF Forms (unless you want to pay a LOT of money), if you care to do a little programming,  you can create your own very effective solution.

But it IS programming. Surely your organization has programmers, and you can show them what I've done as a starting point. If not, we can talk privately.


Thomas D. Greer
http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

RE: Need answer to whether adobe acrobat can pull data from sql

Thanks for all of your help.  The powers that be want me to create this within our custom program for now.  The reason for this is, that our users will open this program, select a client record and select a bunch of forms for that one client.  Pretend they are buying a house.  LOTS of forms.  This allows our staff to select the client, select every form that they want, and then print.  The pain in the butt thing was that I wasn't shown the whole program at first, so I was led to believe that our goal was to keep this in PDF.

In the future this may be useful though, so thank you for explaining it all to me.

misscrf

Management is doing things right, leadership is doing the right things

RE: Need answer to whether adobe acrobat can pull data from sql

No problem, good luck. I've written forms/data/printing management programs for international banks, small Texas cities, the largest commericial printing company in the U.S., and a rural telephone company in Montana. If I can be of further help, let me know.

Thomas D. Greer
http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close