Stefan Cameron on Forms
Building intelligent forms using Adobe LiveCycle Designer

Scripting Table Columns

A few days ago, Sergio, one of my regular commenters, posted a question about programmatically adding new columns to a table. My reply to his comment quickly turned into something that I thought should be promoted to a blog post so here it is.

This question required some investigation because it led me to the discovery of a bug related to adding/removing instances of a table column in a form viewed in a version of Acrobat prior to 8.0. More on that later in this post.

The short answer to Sergio’s question is that yes, in fact, you can modify the set of columns in a table programmatically at runtime. You can do this by either using the presence attribute — although this isn’t recommended because it can lead to data merging problems — or you can use Instance Managers to do it, which is the recommended method to use.

Here’s a sample form that contains a table with a “repeatable column”. Using the add and remove buttons that are provided, you can add and remove instances of the 3rd column.

Download Sample [pdf]

Minimum Requirements: Designer 7.1, Acrobat 7.0.5.

Table Structure

To better understand what’s going on here, I’ll start by explaining how tables are represented in XFA. In reality, there are no <table>, <tr> or <td> elements in XFA. What happens is that <subform layout=”table|row”> elements get used. When a subform represents a table, it’s layout attribute is set to table and when it represents a row, it’s set to row.

Table Subforms

If you read about using a subform’s Instance Manager in order to add/remove instances, you should know that a subform only becomes dynamic or repeatable once it’s placed in a flowed container and its set as repeatable using the Object palette’s Binding tab. By default, a <subform layout=”table”> element is flowed from top to bottom. Since the table subform contains row subforms which, in turn contain the cells, that means that every row in a table could easily be set as repeatable and then get an Instance Manager that you could use to add/remove instances of a particular row.

Row Subforms

Just like the table subform, a <subform layout=”row”> is flowed. Its flow direction, however, isn’t top to bottom, it’s right to left. This means that its contents — the cells — are flowed.

Cells and Columns

Table cells are little different. Essentially, since they reside in a row subform which is flowed from right to left, there’s no specific definition for what is a table cell. It’s simply any type of object (text, numeric field, subform, button, etc.) that’s placed within a row subform. The number of objects in the row determines the number of columns there are. Therefore, to have a 3×3 table, you would need 3 row subforms each containing 3 objects.

There’s no actual definition for a table column either. Table columns are inferred by the cells (put simply, without going into details about column spanning). This means that a column consists of objects in separate row subforms which are above and below each other (e.g. the first object on every row make-up the first column in a table).

Adding/Removing Columns

Given the explanations in my little “table primer” above, we now know that table cells are objects which are flowed from right to left within row subforms. Since row subforms are flowed by nature, it means that if a cell were a subform itself, it could be made repeatable and it would then get its own Instance Manager for free! And once we have Instance Managers, we can start adding and removing instances of those cells.

So the trick here lies in converting every cell which constitutes a column into a subform which then contains the type of object you would normally use to display information in that cell (e.g. a text field, a check box, etc.).

Now if you’ve never noticed the Type property on the Field tab in the Object palette before, you’ll want to check it out because it’s about to come in real handy for setting-up a dynamic table column which consists of cells which are all subforms. This property is used to change the type of the selected object(s). For instance, if you put a text field on a form and decide that it should have been a numeric field, you can change it’s type from text field to numeric field simply by using this property. Typically, you cannot change anything into a subform but when you select a table cell which is a text object (the default cell object type when you insert a new table into a form using the Insert Table menu command or the Table object in the Library’s Standard tab), you can, in fact, change it into a subform. So just select each cell in the column and change its type to a subform using this property. The result will be a cell which is a subform that contains a text object. Then you can change the text object into some other field type which better represents the data which will go into the cell.

Making Cell Subforms Repeatable

Unfortunately, this is one case where you’ll have to use the XML Source tab because the repeatable property isn’t available for cell subforms on the Object palette’s Binding tab. Since it’s a valid XFA setting, you can set this yourself using the XML Source.

Switch to the XML Source window by selecting “XML Source” from the top-level View menu. Then, insert the following XML inside each subform which defines a cell:

<occur max="-1"/>

This means that the cell subform goes from looking like this:

<subform layout="tb" name="Col3SF">
  <field name="Cell3" w="29.9999mm" h="10mm">

to looking like this:

<subform layout="tb" name="Col3SF">
  <occur max="-1"/>
  <field name="Cell3" w="29.9999mm" h="10mm">

and signifies that the cell will have one initial and minimum instance and can have as many additional instances as you need (no maximum).

Last but not least, the script!

After all that, the script is actually quite straight-forward: To add an instance of a column just add an instance of every repeatable subform cell that constitute a column. In my sample, I have a 3×3 table and each subform cell is named “Col3SF”. That means that the script looks like this (in JavaScript):

Table1.HeaderRow._Col3SF.addInstance(0);
Table1.Row1._Col3SF.addInstance(0);
Table1.Row2._Col3SF.addInstance(0);
Table1.Row3._Col3SF.addInstance(0);

Then, to remove an instance of the column, just do the reverse: Use the Instance Manager’s removeInstance method to remove an instance of each cell subform.

Bug when adding/removing table cell instances

Of course, everything was going great until now… Unfortunately, I discovered a little snag in playing with table column instances while making this sample. Fortunately, the bug has already been fixed in Acrobat 8.

Description

The first manifests itself when the second new instance of a column is added (by adding an instance of a column, I mean adding an instance of each cell subform in each row which collectively constitute a table column — as in my sample script above earlier). As of the second new instance, the cell in the header row will stop appearing in all subsequent instances.

There’s also an issue with removing column instances (by removing an instance of each cell subform). In this case, all new instances are removed until what was originally the first instance to be added is removed, leaving only the original, initial instance. What happens is that part of the last instance to be removed remains rendered on the form which doesn’t really look nice because it looks like the instance is still there (even though you can’t click on the cells anymore).

The problem is that the form’s layout isn’t properly updated after columns are added or removed.

Workaround

Luckily, there’s the xfa.layout object which gives us a solution to this problem when version of Acrobat prior to 8.0 are used to render the form. More specifically, versions 7.0.5+ since table support didn’t appear until then, when Designer 7.1 was released.

Using the xfa.layout object, you can tell Acrobat to re-render the form at your command. This effectively repaints the entire form and gets rid of any artifacts (those being the incorrectly rendered column instances). So, after adding or removing column instances, just place the following command:

xfa.layout.relayout();

Please use this with caution, however, since it may adversely affect the performance of your form (since this statement will essentially cause the entire form to be re-rendered every time a column is added/removed). That’s why I check the version of Acrobat that’s rendering the form in my sample so that I know whether I need to apply the workaround or not.

The other workaround is simply to use Acrobat 8.0 to render the form (save is as an “Acrobat 7.0.5 Dynamic PDF Form” but open it in Acrobat 8.0). Acrobat 8.0 now properly renders all instances as they get added or removed.

Fix

Please refer to the Bug List for updated information on the version(s) affected by this bug as well as if and when it was/will be fixed.


Posted by Stefan Cameron on October 28th, 2006
Filed under Acrobat,Bugs,Instance Manager,Scripting,Tables,Tutorials
Both comments and pings are currently closed.

57 Responses to “Scripting Table Columns”

  1. Sérgio on November 9th, 2006

    Hi Stefan,

    Thank you for your help.

    I was wondering, however, if it is possible to extend the goals: to have dynamicall rows at the same time. Is there any way to get the number or rows at a given time. I ask this because we need to know the number of rows to make your current code dynamical and not fixed to 3 rows.

    Thank you again.

  2. Stefan Cameron on November 11th, 2006

    Sergio,

    Tables are flowed containers by definition which means that as long as you make a row “repeatable” (by checking the “Repeat row for each data item” property on the Binding tab in the Object palette), it’ll get an . From there, you can use its count property which will tell you how many instances of that row currently exist:

    Table1._TableRow.count

  3. Brian on May 21st, 2007

    This is very helpful and think the expandable rows and columns will solve 90% of my issue. Just one more left, what happens when the user adds a column
    that will move the data off the right margin of the page? Is there a way to tell the form to start a new page when this occurs? And does the instance count continue or
    start over?

    I’m pretty new to LiveCycle and Jscript is my first programming in 15 years (back then it was Visual Basic for Applications) So thanks for any light you can shed on this.

  4. Stefan Cameron on May 23rd, 2007

    Brian,

    I’m glad you found this article to be helpful.

    Unfortunately, vertical breaks aren’t supported and therefore adding a column that will move the data off the right margin of a page will simply result in the column being added but only partially visible (cut-off at the content area on the master page). You may continue to add columns at that point but they just won’t be visible and there isn’t a way that you can cause the column to wrap to the next “line” (underneath the existing ones).

    As an alternative, you could use basic subforms organized in columns (to mimic a table but without the full table structure) and placed in a flowed subform with its “Flow Direction” property (on the Subform tab of the Object palette) set to “Western Text” (which means left-to-right). You could then give a specific width to the flowed “container” subform such that a new instance of the “column” subform within it (which would go beyond the container subform’s area) would end-up getting wrapped below and to the left of the existing columns.

    Since this is a little difficult to explain in words, I’ve posted a simple form that demonstrates this alternative (requires Designer/Acrobat 7.x or later).

    Does that look like what you’re trying to achieve?

  5. Brian on May 29th, 2007

    Stefan,
    I might be able to use this flow, but the user wants the form to be more complicated than simply left to right flowing will do. Let me try to explain the form this way:

    Down the page there will be conditions and this number of conditions will be variable (addrow method to get more of them with instance manager)
    Across the page will be alternatives and this number is variable (your add column article get these added)

    This basically re-creates a spreadsheet grid where each intersection / cell will have a text field and score field.

    I have many headaches with this design, but the two major ones are:

    1. After I’ve added a row or column; how do I reference that row/column to add instances to other column/rows?
    For example. I start the form out and add 3 rows with the instance manager so there are 4 rows on the screen.
    Then I create a second column. This only creates the 1st row instance by default; how can I get the other 3 dynamically added instances to show up in the new column.

    2. If I add a column that will go off the edge of the page, how can I wrap it with all the appropriate rows included. For this one I am thinking I need to trigger a
    new page or page instance somehow.
    For example. I have 4 rows and when I add a column; it would go outside the margin. The form should move the new column below with 4 rows under the
    heading/first row.

    You would think that for my first form, I’d get something easier….
    Thanks for your insight.
    Brian

  6. Stefan Cameron on May 31st, 2007

    Brian,

    Hmmm… Sounds complicated. Maybe we should take a step back for a minute to make sure you’re designing this the right way: Is this form essentially supposed to have a spreadsheet layout where each cell of the spreadsheet contains two fields (text and score)? And are you needing to add and/or remove cells individually or could the form ask the user for a grid size and then create the spreadsheet (e.g. the user would specify a “3 x 4″ grid and the form would then create a spreadsheet with 3 rows and 4 columns)?

  7. Brian on June 13th, 2007

    Stefan,
    That is basically the approach I took — redesign the form. I was able to simplify things and get it to be a (relatively) simple row instance form.
    For the columns I let the user add pages so the form became instances in instances (IE: each page allows 3 columns so the user can add columns in groups of 3)
    This made for a bit more coding but it isn’t bad.

    Also, I just got the upgrade to Acrobat 8.1 and it seems to really speed up my forms. Have you experienced the same? And will there be a corrisponding
    Acrobat Reader upgrade?

    Thanks again for you insight.
    Brian

  8. Stefan Cameron on June 18th, 2007

    Brian,

    I’m glad you got your form to work. Sometimes it helps to start with a clean slate. :)

    I gather a lot of customers will notice an increase in responsiveness and rendering speeds of their forms in Acrobat/Reader 8.1. The reason for this is Acrobat/Reader 8.1′s new “Direct Rendering” technology which essentially renders the XFA form directly to the screen rather than relying on the Acrobat Object Model. I’ll be writing an article soon on details about the new renderer. Note that the speed increase will only be on dynamic forms as static forms will require PDF structure — again, more details on that soon.

    And yes, there is an 8.1 update for Reader as well.

  9. Tyler on August 15th, 2007

    Stephen is it possible to ask the user how big of a table they want to insert.

    I have a table with 4 columns and n number of rows. In the last column there is a button that has insert table. This will merge the 4 columns in the exisiting table and then add to that row a table with user specified dimensions. How do I get the dimensions and insert a table?

    Thanks,

    Tyler

  10. Stefan Cameron on August 28th, 2007

    Tyler,

    You can query a user for desired table dimensions using the “xfa.host.response” function:

    xfa.host.response("question" [, "title", "default value"]);

    For example, to get the number of rows and columns, you could do this (show here in JavaScript) in your “insert table” button’s Click event:

    // default rows and columns
    var nRowCount = 3;
    var nColCount = 4;
    
    // ask user for row and column count
    var sNumRows = xfa.host.response("How many rows?", "New Table", nRowCount.toString());
    var sNumCols = xfa.host.response("How many columns?", "New Table", nColCount.toString());
    
    // get the user's response handling any invalid input
    
    if (sNumRows != null && sNumRows.length > 0 && !isNaN(sNumRows))
        nRowCount = Number(sNumRows);
    
    if (sNumCols != null && sNumCols.length > 0 && !isNaN(sNumCols))
        nColCount = Number(sNumCols);

    That would get you the dimensions for the new table. As far as merging the existing table, I’m not quite certain I understand exactly what you’re trying to do with the old and new tables and what the end-result is supposed to be.

    In the end, as per this blog post, you would need a table with a single row and a single column where the column is a repeatable subform and the table row is repeatable as well. In the form’s Initialize event, you would remove the only existing instance of the table using its Instance Manager. When the user clicks on the “insert table” button, you would then add the instance back-in (in order to display it) and you would then add the specified number of rows by adding X number of instances of the repeatable row and you would do the same for the columns, by adding Y number of instances of the repeatable column subform to each row instance that you add.

  11. Andrew A. on March 13th, 2008

    Stefan,
    Your post here on creating table columns was incredibly helpful. However, I seem to have run into a version issue. I am using your method to add new columns to a table – the table has two rows – the first a label (static text box), the second a text field where a user puts in input.

    I have tied the creation of the table columns into a drop down box change event – depending on which item is selected in the drop down list, the table creates X number of columns and puts a value in the first row of each – the label.

    This works perfectly in 8 but for some reason in 7 I can’t get it to display the new label values. Even if I put actually write in a value in the label before opening the pdf it doesnt’ display – which is odd because everything else I do displays fine – e.g. borders, and cell coloring- it is just the text that won’t show.

    It seems to have something to do with adding the subform. If I remove the subform from the table ‘cell’ it displays fine – but of course I can’t then create new ‘columns’.

    If I ask the program what the ‘value.text.value’ of the textbox is, it will tell me the right value – but for some reason it just doesn’t display.

    The oddity is that if I change the type of field that my label is – e.g. if I make it an actual text field (but make it read only) then I add my values to the rawValue – it works fine. The problem is that it won’t wrap and so my labels don’t show properly.

    I hope you are still monitoring this blog, despite its age. Any help would be appreciated.
    Thank you.
    -Andrew Albrecht

  12. Stefan Cameron on March 22nd, 2008

    Andrew A.,

    I’m glad you found this post helpful.

    Unfortunately, I can’t think of why the text in the labels isn’t showing-up in 7 (I assume you mean Acrobat). I do know that some things related to table rendering were fixed in Acrobat 8 so that could be one of the reasons.

    As an alternative, I suggest you do use a text field instead of a static text object for the label. You said text doesn’t wrap but if you make that text field support multiple lines (Object palette > Field tab) and set its height to be expandable (Layout palette > Height > Expand to fit), text entered on a single line will wrap! The last thing to do would be to make the text field read-only (Object palette > Value tab > Type property), remove its caption (Layout palette > Caption > Position > None) and remove its content area border (Object palette > Field tab > Appearance > None). That should give you a text field that looks and behaves like a static text object but that renders correctly in Acrobat 7.

  13. Reema on September 11th, 2008

    Hi experts,

    I have an adobe interactive form that shows a table. The table header is repeated for each page.

    I have a subform that has a checkbox. When the checkbox is checked, the first table column is to be hidden.

    I have written the following script on the click event of the checkbox: The script is in formCalc.

    var cnt = xfa.form.form1.subform_main.Table1.Row1.instanceManager.count
    cnt = cnt – 1 //zero-based index

    var hdr_cnt = xfa.form.form1.subform_main.Table1.HeaderRow.instanceManager.count

    if ($.rawValue == 1) then
    for i=0 upto cnt do
    xfa.form.form1.subform_main.Table1.Row1[i].field1.presence = “hidden”
    endfor
    for j=0 upto cnt_hdr do
    xfa.form.form1.subform_main.Table1.HeaderRow[j].cell1.presence = “hidden”
    endfor
    else
    for i=0 upto cnt do
    xfa.form.form1.subform_main.Table1.Row1[i].field1.presence = “visible”
    endfor
    for j=0 upto cnt_hdr do
    xfa.form.form1.subform_main.Table1.HeaderRow[j].cell1.presence = “visible”
    endfor
    endif

    At runtime, When I click on the checkbox, all the rows of column1 are hidden but cell1 of header row is hidden only on the first page. for all other pages, the entire header is displayed.

    Stefan can you help me find the solution.

    I am using Adobe 8.0.0 and Designer 7.1

    Regards,
    Reema.

  14. Stefan Cameron on September 12th, 2008

    Reema,

    Have you tried forcing an update to the form’s layout after you’ve executed your script? It’s possible that subsequent pages, where the header rows are repeated, aren’t getting updated after the first column field has been hidden or made visible. At the end of your script, add the following line:

    xfa.layout.relayout()

    Please let me know if this resolves the problem.

  15. Daniel on December 10th, 2008

    Hi Stefan, I have a problem with a dynamic form and Reader Extensions and maybe you can help (i hope so). I have a web application with a java servlet which calls to LC Form to get a pdf form and show to the user in the web browser.

    LC Form and LC Reader Extensions are installed in another server (JBoss running on RedHat). When servlet send a request to LC Form, this gets a xdp created by LC Designer, render it as pdf form and send to the servlet again which show it to the user. This step works fine. I have the problem in the next step.

    Before LC Form send the pdf form to the servlet I want to set save rights to this pdf using LC Reader Extension. And here is where I have my problem. The pdf form has dynamic fields and when I open it, I can save it with its entered data except these dynamic fields.

    In my java code, I set these rights to the LC Forms

    UsageRights useRight = new UsageRights();
    useRight.setEnabledDynamicFormFields(true);
    useRight.setEnabledFormDataImportExport(true);
    useRight.setEnabledDynamicFormPages(true);

    I have tried to save the form without rights and, using Reader Extension webUI, set these rights again but it doesn’t work.

    Do you know where can be the problem? Any idea? I have looking for some solution but I haven’t found anything.

    Thanks in advance.

  16. Stefan Cameron on December 15th, 2008

    Daniel,

    I’m not sure what’s going-on here since I’m not a Reader Extensions expert. The best thing I can suggest at the moment is to try applying all rights, not just select ones, and see if that fixes the issue. That will at least tell you if it’s a rights issue.

  17. Sandeep on January 5th, 2009

    Stefan,

    I am trying to insert records into MySQL database from rows of a dynamic table having rows upto 10. I am using for loop to read through the rows and insert them into database using insert statement as you showed us in your insert delete update forum. I am using the same database and same table name. I am using the same code which you were using for the insert statement.

    My code for reading rows in a table.

    CSUAppln –form
    page3 — page number
    Table— subform (positioned)
    EduInfo — subform (flowed)
    table1 —- (dynamic table—– grows upto 10 rows)
    var tlength = xfa.resolveNodes(”CSUAppln.page3.Table.EduInfo.Table1.Row1[*]“).length;

    for (var i=0; i<tlength; i++)
    {
    var name = xfa.resolveNode(”CSUAppln.page3.Table.EduInfo.Table1.Row1["+i+"].Name_loc”).rawValue;
    xfa.host.messageBox(”Row Value:”+name);
    var state = xfa.resolveNode(”CSUAppln.page3.Table.EduInfo.Table1.Row1["+i+"].State”).rawValue;
    xfa.host.messageBox(”Row Value:”+state);
    var frm_month = xfa.resolveNode(”CSUAppln.page3.Table.EduInfo.Table1.Row1["+i+"].frm_month”).rawValue;
    xfa.host.messageBox(”Row Value:”+frm_month);
    var frm_year = xfa.resolveNode(”CSUAppln.page3.Table.EduInfo.Table1.Row1["+i+"].frm_year”).rawValue;
    xfa.host.messageBox(”Row Value:”+frm_year);
    xfa.host.messageBox(”Values begin to insert”);
    Database.ExecSQL(”INSERT INTO edu_info (instu_name, state, enrolled_from, enrolled_to) VALUES (’”+ name +”‘, ‘”+ state +”‘, ‘”+ frm_month +”‘, ‘”+ frm_year +”‘);”)
    xfa.host.messageBox(”Values instered Sucessfully!”);
    }

  18. Stefan Cameron on January 6th, 2009

    Sandeep,

    You didn’t really say what the problem is but I’m guessing the rows aren’t being inserted into the database.

    The first thing I would do is change the name of the “name” variable to something else. “name” is a property on almost all XFA objects and when you try to declare a variable with the name “name”, the interpreter usually throws an error. Try renaming it to “instu_name” and see if you script works better.

  19. Sandeep on January 7th, 2009

    thank you stefan for interpreting my problem.
    When i preview the pdf and try to insert the values from the table to database it says SourceSet is null

    The code i used for accessing database is:

    /**
    * The name of the data connection defined in the form which is to be used as a template for the data
    * connection created to execute SQL statements.
    */
    var ODBC_DATA_CONN = “DataConnection”;
    xfa.host.messageBox(“ConnectionName:”+ODBC_DATA_CONN);

    /**
    * Search the sourceSet model for a data connection with the specified name and return a reference to it.
    * @param sDataConnName Name of the data connection sought.
    * @param bClone If true and the data connection is found, a clone of the data connection will be returned.
    * @return The data connection (or a clone) if found; null otherwise.
    */
    function GetDataConn(sDataConnName, bClone)
    {
    var oDataConnList = xfa.sourceSet.nodes; // this will be a list of nodes
    var nCount = oDataConnList.length;
    xfa.host.messageBox(“”+nCount+” V: “+oDataConnList);
    for (var i = 0; i < nCount; i++)
    {
    var oDataConnNode = oDataConnList.item(i); // this will be a node

    if (oDataConnNode.className == “source” && oDataConnNode.name == sDataConnName)
    return bClone ? oDataConnNode.clone(1) : oDataConnNode;
    }

    return null;
    }

    function PrepareForSQL(oDCNode)
    {
    // First, clean the data connection node itself to get rid of the unecessary nodes.

    var nDCChildCount = oDCNode.nodes.length;
    var oCommandNode = null;

    for (var i = nDCChildCount – 1; i >= 0; i–) // reverse through the list since we’re removing nodes
    {
    var oDCChildNode = oDCNode.nodes.item(i);

    if (oDCChildNode.className == “command”)
    {
    // keep the node for later
    oCommandNode = oDCChildNode;
    }
    else if (oDCChildNode.className != “connect”)
    {
    // We don’t want to keep anything aside from the and nodes.
    oDCNode.nodes.remove(oDCChildNode);
    }
    }

    // Then, remove all the nodes inside except for .

    if (null == oCommandNode)
    {
    // We weren’t able to find the node >> something went wrong!
    return null;
    }

    var nCommChildCount = oCommandNode.nodes.length;
    var oQueryNode = null;

    for (var i = nCommChildCount – 1; i >= 0; i–) {
    var oCommChildNode = oCommandNode.nodes.item(i);

    if (oCommChildNode.className == “query”)
    {
    // remember this for later
    oQueryNode = oCommChildNode;
    }
    else
    oCommandNode.nodes.remove(oCommChildNode); // remove everything else
    }

    if (null == oQueryNode)
    {
    return null;
    }

    var nQueryChildCount = oQueryNode.nodes.length;
    var oSelectNode = null;
    var oRecordSetNode = null;

    for (var i = nQueryChildCount – 1; i >= 0; i–) {
    var oQueryChildNode = oQueryNode.nodes.item(i);

    if (oQueryChildNode.className == “select”)
    {
    // remember this for later
    oSelectNode = oQueryChildNode;
    }
    else if (oQueryChildNode.className == “recordSet”)
    {
    // remember this for later
    oRecordSetNode = oQueryChildNode;
    }
    else
    {
    // anything else gets removed
    oQueryNode.nodes.remove(oQueryChildNode);
    }
    }

    oQueryNode.commandType = “text”; // specify that it’s an SQL query

    if (null == oRecordSetNode)
    {

    return null;
    }

    oRecordSetNode.bofAction = “stayBOF”;
    oRecordSetNode.eofAction = “stayEOF”;

    return oSelectNode; // return a reference to the
    }

    function ExecSQL(sSQL)
    {

    ExecSQL(sSQL, true);
    }

    function ExecSQL(sSQL, bAutoClose)
    {

    var oDC = GetDataConn(ODBC_DATA_CONN, true); // get a clone of the data connection (we must clone it prior to modifying it or else we’ll get a security exception)

    var oSelectNode = PrepareForSQL(oDC); // modify the cloned data connection to execute any SQL statement and get a reference to its node

    if (null != oSelectNode)
    {
    // We successfully converted the data connection into an “insert” connection. All we need to do now is set the SQL for the node
    // and open the data connection to get it to execute the query.
    xfa.host.messageBox(“Prepare SQL Success”);
    oSelectNode.value = sSQL;

    // Enable this statement to peek at what the data connection’s definition looks like now in the Acrobat JavaScript Console (Ctrl + J).
    //console.println(oDC.saveXML(“pretty”));

    try
    {
    oDC.open(); // execute the data connection (and the SQL statement)
    }
    catch (e)
    {
    console.println(e);
    oDC = null;
    }
    }

    else
    xfa.host.messageBox(“Prepare SQL fail”);

    if (null != oDC && bAutoClose)
    {
    oDC.close();
    oDC = null;
    }

    return oDC;
    }

    This is a script object named Database.

    After i run the document, it pops up a message in JavaScript Debugger, that oDC node has no value.

    I am using workbench ES 8.2. and i use preview pdf to view my document.

    thank you

  20. Stefan Cameron on January 13th, 2009

    Sandeep,

    I couldn’t see anything wrong with the script itself. What’s the return value from the GetDataConn() function? Is it null? If so, it means your data connection wasn’t found. One reason for this is attempting to execute this script in a PDF that’s running in the free Reader without having first extended the PDF to enable data import rights using LiveCycle Reader Extensions ES.

  21. Sandeep on January 14th, 2009

    So the document designed in live cycle require reader extended before it can be tested with designer’s preview?

  22. Stefan Cameron on January 15th, 2009

    Sandeep,

    Only if you’re attempting to import/export data into/out of your form via a web service and you only have Reader on your machine. As long as you preview with Acrobat Standard/Pro/Extended, you’ll be able to connect to the web service.

  23. Jenn D. on January 27th, 2009

    I have a form that I have added the script above to. When I hit the button to update, nothing happens… no error message and nothing was added to my database. Is there a way to step through this to see where it fails?

    Thank you for any info.

    Jenn D.

  24. rohit kumar behera on January 28th, 2009

    Hi Stefen,
    I am facing one issue with header continuation in subseqeunt pages in adobe form. I am gettng the header displayed in the first 2 pages but it disappears completely in the third fouth and fifth page. I have tried tweaking arnd settings for the form like making the form header included in the subsequent pages and also making the table subform flowed, still it doesnt wrk. Please help.

  25. Stefan Cameron on January 30th, 2009

    Jenn. D,

    You can try inserting some console.println() statements throughout the code to see how far you get and what the state of things are. That’s about the extent of debugging that you can do, unfortunately.

  26. Stefan Cameron on February 4th, 2009

    rohit kumar behera,

    This is the same issue as Hilary was describing in comment #84 in my Instance Manager Object Reference post.

    As I told Hilary, I wasn’t able to reproduce the issue using Designer 8.2 and Acrobat 9. Are you using those versions?

  27. Stefan Cameron on February 9th, 2009

    rohit kumar behera,

    If you can, please send me your form at formcollateral1_at_stefcameron_dot_com

    I’d like to investigate the issue a little more but I can’t do it without your form since I can’t reproduce the issue.

  28. Anthony on June 23rd, 2009

    Hi Stefan

    I have implemented your solution for columns and it works great for adding columns. My problem when it comes to deleting them.

    When I’m adding a column it also adds a delete button at the top of the column. When I press the delete button i want it to delete that specific column.

    I’m using

    var IndexNum = this.parent.instanceIndex;

    then using the IndexNum to delete that specific index. This has always worked when used to add and remove rows but can’t seem to get it to work with columns

    form1.tblMatrix.Row1._colColumn.removeInstance(IndexNum);

    Any ideas??

    I tried an alternative (compromise) by using the instancemanager count to delete the last column, but I can’t seem to get that to work either. Always returns a 1.

    Thanks

    Anthony

  29. Anthony on June 23rd, 2009

    Just to add

    It deletes the first cell but does not delete the rest of them.

    I have about 30 rows.

    Thanks

  30. Anthony on June 26th, 2009

    Just an update, I still have the problem but found out that it is not executing the next line of code, like there is something wrong with the code.

    Heres the code:

    var IndexNum = this.parent.instanceIndex;
    form1.tblMatrix.Row1._colColumn.removeInstance(IndexNum);
    form1.tblMatrix.Row2._colColumn.removeInstance(IndexNum);
    form1.tblMatrix.Row3._colColumn.removeInstance(IndexNum);
    form1.tblMatrix.Row4._colColumn.removeInstance(IndexNum);
    and so on to 33

    So it does the first two lines but stops, so it doesn’t execute Row 2 onwards. So it just deletes the first cell(row) of the column i have selected to remove.

    I have tested to ensure that IndexNum is returning the correct index of the column and that is correct. Also count is working aswell so please ignore the last statement of my original post.

    Thanks

  31. Stefan Cameron on June 26th, 2009

    Anthony,

    Is the button used to remove the column in the first row? If that’s the case and you’re running Acrobat/Reader 8.1 or later, the problem is likely that the button is removed immediately (and the script stops executing) after the Row1 column instance is removed.

    As of A/R 8.1, instances are removed immediately and code that follows isn’t executed (which could explain what you’re seeing). Try removing row column instances in reverse order where the column instance that contains the button that has the Click script to remove the column is the last one removed in the column.

  32. Anthony on June 29th, 2009

    Thats great stefan, sorted it now.

    Ye the button was being removed first so the code stopped after row1.

    Thanks for the help once again.

  33. Anthony on July 22nd, 2009

    Hi Stefan got another puzzle for you.

    I now have a table with the add and remove column. But what i want to do now is split a single column into 3. So when i click add column it adds all 3.

    I can add the 3 columns but i want them in the same order. for example:
    Column: 1 2 3
    D T B
    When i click add it goes
    Column: 1 2 3 4 5 6
    D D T T B B
    I want it to go
    Column: 1 2 3 4 5 6
    D T B D T B

    Is this possible???

    Thanks

    Anthony

  34. Anthony on July 22nd, 2009

    Just an update.

    I’ve got it to work buy grouping the objects in a subform (through the xml editor as you can’t wrap in hierarchy view on multiple objects in a table). I’ve had to position them so that the objects are on one line.

    But the problem i’m finding is that when I add a column it does not place the new column in the correct position, it overlaps the previous column.

    Any ideas??

  35. Anthony on July 27th, 2009

    Fixed the problem.

    By using the XML to edit what is contained in the column subform I forgot to reduce the number of columns defined in the XML code to create the table in the first place. So there were hidden columns and so when I created a new instance it using these hidden columns as the reference to where the new instance of the column will be drawn.

    Hope that makes sense.

    Anyway sorted now.

  36. Stefan Cameron on July 27th, 2009

    Anthony,

    I’m glad you figured it out! Wrapping the 3 columns was the right course of action in order to achieve what you wanted (“DTBDTB” as opposed to “DDTTBB”). Subform instances can be re-ordered within themselves however they cannot be amongst other subforms (unless they are positioned however in this case you would want them to be flowed).

  37. Yan Kliaver on October 21st, 2009

    Hi Stefan,

    I’ve built a table based on your post and also added an option to add rows.
    In this case I have to add “_Col3SF” subforms dynamically depend on table’s rows number.
    Here I have difficulties to with this expression:
    Table1.Row1._Col3SF.addInstance(0);
    I want to insert it into a loop, how should I handle the Row property to be changed?

    Thanks, Yan

  38. Stefan Cameron on October 29th, 2009

    Yan Kliaver,

    If your table rows are static and named “Row1″, “Row2″, …, “RowN”, you can loop through them using the resolveNode() method:

    for (var i = 1; i < = N; i++)
    {
        var row = Table1.resolveNode("Row" + i);
        row._Col3SF.addInstance(0);
    }
  39. Nathan on November 16th, 2009

    Stefan,
    I am working with dynamic columns. I have followed you template and instructions from May 23, 2007 and have them working fine. The problem I have though is I have a static number of rows and want the columns to break to the next page and not the next row. Any ideas?
    The next for I have has a table like a pivot table, with dynamic rows and columns. Any ideas on how to approach that?

    Nathan

  40. Stefan Cameron on November 19th, 2009

    Nathan,

    Unfortunately, I don’t believe the flow algorithms are designed to break horizontally. They only support breaking vertically to another page.

    My first approach would be to do it manually using script. Setup your page to be repeatable and have it contain a table with no column instances. If you’re about to add another column and there isn’t enough room in the page width (you can use the ‘w’ property of form objects to determine their width, as long as they don’t expand horizontally, which they shouldn’t since they’re in a table), add a new instance of the page and a new instance of the table column on that page and continue from there…

    Note that the ‘w’ property will be a string with a unit qualifier, most likely “mm” for millimeters. You’ll need to use parseFloat() in JavaScript to get the numerical value out of it so that you can do the right comparisons with the page subform’s width.

  41. Vinod Chattergee on January 7th, 2010

    Dear Stefan,

    Please refer the below link and help me out in fixing the issue.

    http://forums.adobe.com/message/2501448

    Thanks,
    Vinod

  42. Stefan Cameron on January 12th, 2010

    Vinod Chattergee,

    Sounds like this issue.

  43. Deepak on January 17th, 2010

    Hi Stefan

    In the section “Adding / Removing columns”, you had mentioned that we can change the data type of the cell dynamically. I am trying to do this as exaplained by you, however, unable to achieve it. Request you to please tell me the script for it.

    (I need to add all form fields and their values in a table and send it to web service for processing. Currently, I am able to add only text fields in the table and not other fields…)

    Thanks
    Deepak

  44. Stefan Cameron on January 19th, 2010

    Deepak,

    You should be able to drag other field types from the Object palette onto a cell and have that cell’s field replaced by the one you just dropped on it. Alternatively, you can select the cell and use the “Object palette > Field/Draw tab > Type property” to change the object’s type (e.g. from a Text Field to a Numeric Field).

    This is all done at design-time. There are no scripts involved.

  45. Deepak on January 22nd, 2010

    Hi Stefan

    Thank you for your reply. Initially, I had done the same but the data in the table was not getting populated. Later I found out that, there was some script error and hence the problem.

    The articles by you are excellent and gives real insights and solutions for any complex Form development…!!! Thanks a ton… :)

    Thanks
    Deepak

  46. Stefan Cameron on January 22nd, 2010

    Deepak,

    Thanks for the great feedback, I appreciate it! :)

  47. Deepak on January 29th, 2010

    Hi Stefan

    Your expert guidance is requested…!!! :)

    This is with regard to repositioning the Subform layout dynamically. I need to reposition the x or y coordinates of a Subform (which is “positioned” and contains sub elements in it like text fields and others) within the same page only.

    It works fine in ADLC…just change the y coordinate (say from 6in to 4in) and the whole subform is moved to that location correctly with elements intact.

    If the same conversion is done using javascript (for example: Page1.Subform1.y=”4.1239in”;), the subform indeed repositions but in wrong place… (instead of going to 4in, it goes to 8 or 9in.. )

    I tried to follow the example given by John (http://blogs.adobe.com/formfeed/2009/04/layout_methods_to_find_page_po.html), however was not able to figure out how to use it for my scenario. From the blog of John, it seems that we also need to recalculate the positions of the elements in the subform in javascript.

    How can we reposition the subform with easyness as we do in ADLC..??

    Thanks
    Deepak

  48. Jennifer on January 29th, 2010

    Stefan,
    First off, thank you for all of your work on this site. I absolutely love it!

    I’m not sure if this is the best place to put my question, but it sort of relates.

    I have a table with an add row button. Several of the cells in the table have checkboxes. If one is checked, then a new table pops up for more information regarding that specific item.
    Short Example (not specifically what I’m doing, but trying to keep it generic in case this post can help others too):
    Column 1 = Customer Name
    Column 2 = Drink
    If the cell in column 2 is checked, then Table 2 is displayed and it has them select what type of drink they would like, if they want it hot or cold, what size, etc.

    The first issue that I am having is that if I add an additional row and both of the users check the checkbox for the drink then I get the error, “Error: The element [max] has violated its allowable number of occurances”.

    The next issue I have is that if they change their mind and remove the check from one of the customers, but not the other (I would still want the table to show up since one user needs to reference the table) the table is removed. Also, if I remove all checks then I get the error, “Error: Index value is out of bounds”.

    Any help you could provide would be greatly appreciated! Several people are really excited about this form; I just need to get it working! :) Thank you!

  49. Stefan Cameron on February 8th, 2010

    Deepak,

    I’m afraid I don’t know what “ADLC” stands for.

    As far as I know, relocating the subform should result in all contained children to follow along and retain their positions relative to the subform’s top/left corner.

    When you say the subform moves to approx. 8in or 9in when you assign “4in” to its y coordinate, could the resulting position appear to be greater than 4in since it would be relative to the subform’s parent which might be 4in from the top of the page?

  50. Stefan Cameron on February 8th, 2010

    Jennifer,

    You’re welcome!

    It sounds like you might be adding/removing an instance of Table2 rather than showing/hiding it which leads you to attempting to remove it when it has already been removed (“index out of bounds”) or adding a second instance when Table2 isn’t repeatable (“element [max] has violated its allowable number of occurrences”).

    Assuming only one customer may fill Table2 even if two customers have checked the “Drink” box, then you should first place Table2 in a flowable subform and make it repeatable with a min number of 0 instances and a max of 1. Then, in your script associated to the checkbox which displays Table2, you should check, before adding an instance, if there is already an instance that exists. You can do this by using the “count” property of Table2′s Instance Manager to get the number of existing instances of Table2: _Table2.count.

    You would then do the opposite when removing Table2: Check the count to see if it has already been removed (count of 0) and only remove the instance of Table2 if there is an instance currently in existence.

  51. Jennifer on February 11th, 2010

    Stefan,
    Ah-ha! That makes sense. Thank you very much!

  52. Jennifer on February 17th, 2010

    Stefan,
    I’ve been working on this off and on and am still having problems. Would you be able to elaborate a little more?

    I have the subform flowable and repeatable (although the min count won’t stay checked). Next I am trying to get a show/hide script to work, but am confused. I’m sure that I’m missing something simple. I tried a few different variations to get the script to work and ended up with “Error: accessor ‘Subform2.presence’ is unknown”

    The presence of the Subform2 that I want to show when the checkbox in Subform1 is checked is currently set to visible.

    Any suggestions?
    Thanks!

  53. Stefan Cameron on February 25th, 2010

    Jennifer,

    It sounds like you’re trying to call a property (presence) on Subform2 when Subform2 doesn’t exist yet.

    If you’re following my advice from my previous reply, then Subform2 is set to be repeatable and has a min occurrence of 0 which means it doesn’t exist by default.

    In order to show Subform2, you have to create an instance of it using the addInstance() method of Subform2′s Instance Manager:

    _Subform2.addInstance(0);

    After that, Subform2 will exist and you can access the fields it contains and get/set properties on it.

  54. Lewis on June 11th, 2010

    Hi Stefan

    Great post, found the tables primer particularly useful.

    I was wondering, is there a way for this to work with dynamic data. That is having one row object which repeats based on data from a data connection?

    Using what you’ve described above I have created something which does this, but it won’t page down (it just flows off the bottom of the page). I think it’s because of the ‘dynamic’ column as you can’t tick ‘allow page breaks’ for this one as it’s grayed out.

    Any help would be greatly appreciated.

    Lewis

  55. Stefan Cameron on June 16th, 2010

    @Lewis,

    Check-out my Expandable Table with Totals tutorial. It shows you how to create a table that expands. To make sure it can flow onto other pages, make sure the table itself is set to allow content to break across pages (“Object palette > Table > Allow content to break across pages”).

    As for the data binding, bind the repeatable row to the repeatable data node that contains the data for the row similar to the way the “Registrant” subform is bound to repeatable <registrant> data in my MAX 2007 tutorial (see the solution form and don’t worry about Guide Builder, you don’t need it to run the form itself).

  56. Todd on June 24th, 2010

    Hello Stefan,

    I keep coming back to the site to see if I can make something new happen, you are doing a great job helping people out. I have a situation I am trying solve.

    I figured out how to make the Add rows and delete button work.

    I need for information from the table which includes the added rows, to be populated into the message area of an email based on what is answered with 2 radio buttons. I have a Yes and No button. If they select yes all the information from a table will populate the message area. If they click No only one sentence will go in to the message area. I am using a JavaScript for the email already, in Click, I have other items already being pulled from the form to populate the message area.

    I am also wondering about when I use the add rows button. How can I get the added rows information to populate the message area of a email. I set up the table so it has a repeat subfrom with a min count of 1.

    LiveCycle Designer ES 8.2 – XP

    Can you please help?

  57. Stefan Cameron on June 29th, 2010

    @Todd,

    Thanks for letting me know you find my blog useful!

    First, you should have a look at my tutorial on submitting form data by email, if you haven’t done so already.

    Regarding reading data from your table, let’s say you have n instances of a repeatable row named “repeatableRow” in a table named “table1″ where the repeatable row has a single cell/column where this cell is a text field named “textField1″. You can iterate the rows and get the cell values like this:

    // JavaScript:
    var cellValues = "";
    var rows = Table1.repeatableRow.all;
    for (var i = 0; i < rows.length; i++)
    {
        var row = rows.getItem(i);
        cellValues += row.textField1.rawValue;
    }