Binding related table rows based on a key item

[Sinuhe_310]

Hello,

I have two separate tables in my form, A and B, with following data:

Table A:

COMPANY_ID	DATAITEM_1
12345	Data1Test1
98765	Data1Test2

Table B:

COMPANY_ID	DATAITEM_2
12345	Data2Test1
98765	Data2Test2

In both tables, the value for the COMPANY_ID in each row is chosen by the user from a drop-down list. DATAITEM_1 and DATAITEM_2 are normal textfields.

I have bound COMPANY_ID, DATAITEM_1 and DATAITEM_2 in both tables to the corresponding elements in my XML schema. The form now produces data that looks like this:

...

<DATA>

  <COMPANY_ID>12345</COMPANY_ID>

  <DATAITEM_1>Data1Test1</DATAITEM_1>

</DATA>

<DATA>

  <COMPANY_ID>98765</COMPANY_ID>

  <DATAITEM_1>Data1Test2</DATAITEM_1>

</DATA>

<DATA>

  <COMPANY_ID>12345</COMPANY_ID>

  <DATAITEM_2>Data2Test1</DATAITEM_2>

</DATA>

<DATA>

  <COMPANY_ID>98765</COMPANY_ID>

  <DATAITEM_2>Data2Test2</DATAITEM_2>

</DATA>

...

What I am trying to do, is to make my form produce data like this:

...

<DATA>

  <COMPANY_ID>12345</COMPANY_ID>

  <DATAITEM_1>Data1Test1</DATAITEM_1>

  <DATAITEM_2>Data2Test1</DATAITEM_2>

</DATA>

<DATA>

  <COMPANY_ID>98765</COMPANY_ID>

  <DATAITEM_1>Data1Test2</DATAITEM_1>

  <DATAITEM_2>Data2Test2</DATAITEM_2>

</DATA>

...

In other words, I would like to bind DATAITEM1 and DATAITEM2 to a specific data group based on COMPANY_ID value on the same row. COMPANY_ID thus acting here as the key.

Could someone tell me if this is possible?

[Sinuhe_310]

Does anyone have ideas on how this problem could be solved? Or is it even solvable?

Any help would be appreciated.

[_Bruce_Robertson]

Hi,

You can't have a repeating data item bound to multiple repeating form items, so you have to acheive this in script. 

I would have all the fields bound in the first table and make any that you don't want hidden.

Then in the initiailse event of the form object that is bound to DATA add the following JavaScript code.

var unboundData = _UNBOUNDDATA.addInstance(false);

unboundData.COMPANY_ID.rawValue = COMPANY_ID.rawValue;

unboundData.DATAITEM_2.rawValue = DATAITEM_2.rawValue;

This assumes the other table has a row called UNBOUNDDATA

If DATAITEM_2 can be updated then you will need some script in the exit event to update the hidden field in the first table, something like;

DATA.all.item(UNBOUNDDATA.index).DATAITEM_2.rawValue = this.rawValue;

The UNBOUNDDATA.index reference gives you the row number.

Here is a sample I used to test this code, https://acrobat.com/#d=8ph8glc*5vBUqBSuDI0w7g

Hope this helps

Bruce

[Sinuhe_310]

Hi Bruce,

The idea that I should use hidden fields in the first table seems reasonable. Thank you!

However, it seems to me that the data in your example are not actually bound based on the COMPANY_ID on that particular row, but based on the row number. The solution does not work if also the COMPANY_ID in each row needs to be editable, does it? In my form, the user must choose the COMPANY_ID for each row from a dropdown-list.

So what I actually need is to have the data in separate rows (in separate tables or even in the same table) to be located under the same DATA element in the XML hierarchy IF (and only if) these separate rows have a mathing value for the COMPANY_ID.

I'm sorry I did not express the problem accurately in the first place.

[_Bruce_Robertson]

That would be an added complication, how do you populate the COMPANY_ID drop down list? Does you xml start off looking like;

<DATA>

  <COMPANY_ID>12345</COMPANY_ID>

  <DATAITEM_1></DATAITEM_1>

  <DATAITEM_2></DATAITEM_2>

</DATA>

<DATA>

  <COMPANY_ID>98765</COMPANY_ID>

  <DATAITEM_1></DATAITEM_1>

  <DATAITEM_2></DATAITEM_2>

</DATA>

And can the COMPANY_ID appear in the second table but not the first table?

[Sinuhe_310]

The drop down list values for the COMPANY_ID are populated from data that the user has typed in yet another table in the beginning of the form. So after the user has done this, the data does look like that.

All the possible COMPANY_ID values are thus listed in the very first table. Whether a particular value for COMPANY_ID appears in all the subsequent tables (there can be more than one) depends on whether the user selects this value from the COMPANY_ID drop down lists in these subsequent tables or not. So I guess the answer to your second question is: no, but it can appear in the first and not the second (nor any other subsequent tables).

[_Bruce_Robertson · Accepted solution]

I have modified my example, the tables with dropdown lists can be populated in the preOpen event with code like;

this.clearItems();

var list = xfa.resolveNodes("COMPANY[*]");

for (var i = 0; i < list.length; i++)

{

    var item = list.item(i);

    this.addItem(item.COMPANY_ID.rawValue);

}

The exit event code then looks like;

if (!COMPANY_ID.isNull)

{

    var company = xfa.resolveNode("COMPANY.(COMPANY_ID.rawValue === '"+COMPANY_ID.rawValue+"')");

    company.DATAITEM_1.rawValue = this.rawValue;

}

The new sample can be downloaded from https://acrobat.com/#d=SCOps1yz0powjzxs2L9n3w

But you will have a lot of scripting to add to complete the form, can they add duplicate company rows and what happens after they have selected a company added the DATAITEM_1 (or DATAITEM_2) and then changed the company, will you have to remove the data entered or allow them to change there minds again.

Good luck

[Sinuhe_310]

Thank you Bruce! Your scripting examples are most helpful.