PDF Data Entry - Part I

I have been nagged by many folks for a how-to on data entry via PDF forms. A certain competitor provides the ability to create a PDF form and then allow users to enter data via Adobe Acrobat or Reader and push it into the application tables. Well now you can do it with E Business Suite, I should add its not out of the box, there are a few applications teams looking at this and a few have already started to provide this type of functionality. I indebted to Incheol on our development staff for help on some of the finer points ... Publisher is getting so big it's tough to keep up with all its capabilities. 


Some folks will be asking 'whats the point?, there are forms and pages provided by the application for data entry, why not use those?'

It's a fair question but when you think about entering orders or invoices into a system your users may want a faster medium than Oracle Forms to get the data in ... the more obvious choice would be Excel - I'll cover that in another post. How about wanting to provide your customers or suppliers the opportunity to enter data into your system but you do not want them to use the professional apps. Or maybe you want to collect  I-9 information from your new hires and rather than develop a user interface, why not use the govenrment provided I-9 form.  

Here's where a PDF form can be very handy, its a reasonably familiar format for users now, Adobe Reader is almost ubiquitous on the desktop so minimal install for them and you have the power to make the form as simple as you like.


Im going to split this article into a few sections, 1 to save you reading and 2 to save me writing massive amounts of editorial. Im going to start with creating the PDF form itself. Sounds simple enough and ought not to warrant an article to itself I hear you say. But Publisher a few tricks up its sleeve that can prove very handy.


Let's assume you want to collect employee information using a PDF form.


EmpDataForm1:


Building the form is going to require either Adobe Acrobat 5 or higher. If you are building a form from scratch rather than using a 3rd party form e.g. I-9 you typically do not start with a blank page. Use Word, scan in a form or similar to build a layout and then add the data entry fields to the form. 


There may well be some information you already know, in our case, the list of job descriptions, manager and department names. You could hard code these but things change so if you can make it dynamic so much the better. Fortunately publisher can help you here, it has the ability to populate fields and combo boxes for you.

EmpDataForm2:


The table we want to populate is the EMPLOYEES table in the demo HR schema.

Name              Null     Type
----------------- -------- ------------
EMPLOYEE_ID       NOT NULL NUMBER(6) 
FIRST_NAME                 VARCHAR2(20)
LAST_NAME         NOT NULL VARCHAR2(25)
EMAIL             NOT NULL VARCHAR2(25)
PHONE_NUMBER               VARCHAR2(20)
HIRE_DATE         NOT NULL DATE
JOB_ID            NOT NULL VARCHAR2(10)
SALARY                     NUMBER(8,2) 
COMMISSION_PCT             NUMBER(2,2)
MANAGER_ID                 NUMBER(6)
DEPARTMENT_ID              NUMBER(4)


Notice that the table does not store job, manager and department names but rather the relevant IDs. Therefore when we populate the combo boxes we need to extract the value (for the user to select) and the 'id' so that the table can be updated with the 'id' rather than the name.


We can use the FormProcessor API to populate the combo boxes:

    String pdfTemplate = "D:\\Work\\DataEntry\\EmpForm.pdf";
    String pdfData = "D:\\Work\\DataEntry\\Data.xml";
    String pdfForm = "D:\\Work\\DataEntry\\EmpFormEntry.pdf";
    
        try {
         System.out.print("Starting to fill form ...");
           
           FormProcessor fp = new FormProcessor();
           fp.setTemplate(pdfTemplate);
           fp.setOutput(pdfForm);
           fp.setData(pdfData);





           Properties prop = new Properties();
           prop.setProperty("all-field-readonly","false");
           fp.setConfig(prop);            
           fp.setLOV("JOB_ID",getJobHash());
           fp.setLOV("MANAGER_ID",getMgerHash());
           fp.setLOV("DEPT_ID",getDeptHash());

           fp.process();
        } 
        
        catch (Exception ex){
            ex.printStackTrace();
        }

Its pretty straightforward, we need to use some dummy data with the PDF template (form). If you had fields that you could pre-populate you would put this data into the XML. In our case we did not have any. 


The two main things to notice here are:
1. We need to set the "all-field-readonly" property to false. Without this the fields in the resulting form will be set to read only.
2. We are using the setLOV method to populate the combo boxes in the PDF form. It has a couple of signatures but Im using the following:


setLOV(java.lang.String fieldName, java.util.Hashtable values)


I have fields named JOB_ID, MANAGER_ID and DEPT_ID. To populate the hashtables for each I connect to the database and select the appropriate data. For the MANAGER_ID hash I have:

private Hashtable getMgerHash()    {
      int mgerID;
      String mgerName = "";
      //Hsh table for manager information
      Hashtable mgerHash = new Hashtable();
      System.out.println("Loading Managers \n");
      try
      {
        // Try and geta connection to the db
        Connection conn = getConnection();
       // Fetch the emp id and name for those emps that are managers
        PreparedStatement getHash 
                = conn.prepareStatement("select distinct e.employee_id ID," +
                                      "e.first_name||' '||e.last_name NAME \n" + 
                                      "from employees e, employees f\n" +
                                      "where f.manager_id = e.employee_id");
       
        ResultSet locRslt = getHash.executeQuery();
        // loop thru the dataset and load values to the hashtable
        while (locRslt.next()){
        
            mgerID = locRslt.getInt(1);
            mgerName = locRslt.getString(2);
            mgerHash.put(String.valueOf(mgerID),mgerName);
           }
        //Clean up 
        locRslt.close();
        getHash.close();
        conn.close();
      }
      catch (SQLException eSQL)
      {
        System.err.println("Could not create connection");
        eSQL.printStackTrace();
      }
      catch (Exception e)
      {
       System.err.println("Exception thrown");
       e.printStackTrace(); 
      }
       
       return(mgerHash);
    }    

This passes a filled hashtable back to the main code:

           fp.setLOV("MANAGER_ID",getMgerHash());
Once the code completes we have pre filled the form with the latest combo box values for the user to select appropriate values. They will select a value from the list but the id will be stored.
Publisher can even help you inject javascript into the form as well. I have not included a sample here but check out the the JavascriptEditor class in the javadocs. You can inject/replace javascript at document and field level. 
You can get the starting PDFForm, the resulting pre-populated form and the java class here.


You'll have noticed the Submit button ... more on that next.

Comments:

Hi Joby

Sadly we do not yet support generating form fields in our PDF output from an RTF template, thats why I used a PDF form as the template and then added to it. We are looking at the RTF option for a future release. You could take a PDF form and partially fill it thou no problem, then maybe populate drop boxes etc.

As for submission, you can host the document inside the XMLP common region and have your users interact that way rather than running a conc request. Im working on an article for the common region and will then combine the data entry with it and show it working ... I hope. But the common region will get around all the security issue you would have had with a conc manager generated PDF and users trying to post data from Adobe Reader. The region is covered in the user guide if you want to get started with a custom page.

Tim

Posted by Tim Dexter on August 13, 2007 at 08:52 AM MDT #

Hi Tim, Wonderful article. Very much appreciated. However i have a question about this. Can we incorporate this into an existing scenario where the PDF is generated by XML Publisher..? Designing the RTF template to generate the PDF with form fields in it. And then user run the concurrent program, which generates the PDF report and fill in the data and submitting the form inserts the data into the database using the current APPS Login... that way authentication too is accomplished... Any feedback on this is appreciated.

Posted by Joby Thomas on August 13, 2007 at 09:00 AM MDT #

Dear Sir, I am Looking Data Entrey Job. If Your Compay Have Please let me know

Posted by THUSHARA DE SILVA on February 22, 2009 at 05:57 PM MST #

This appears a good information on data entry via PDF forms. I going to read more posts after this.

Posted by Kapinder on March 23, 2009 at 12:25 AM MDT #

Great post!

Posted by Nashville concrete on August 31, 2010 at 10:05 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today