Merging Information from two repeating tables into one

[Mike]

I have three views in one form, the first view is a time sheet, second view
is a parts list and third view is an invoice. All three views have
repeating tables, all of which have a different layout.

I would like to query the data from the repeating table in the time sheet
view, and query the data from the repeating table on the parts view and
insert it into the invoicing repeating table.

I do know some VB Scripting and no J Scritping.

Can anybody help?

 

[Scott L. Heim [MSFT]]

Hi Mike,

The first question I have is this: where is your data being stored? For
instance, did you create your InfoPath solution from a data source (i.e.
Access, SQL Server or web service) or did you start with a blank form and
simply add controls?

Based on your answer, I may have some additional questions.

Thanks,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mike]

Scott,

I started with a blank form and added the controls. Our intention is to
"extract" the information to an excel spreadsheet in upload into our
finanical package.

 

[Scott L. Heim [MSFT]]

Hi Mike,

Thank you for the additional information. As you are probably aware, a
"View" in InfoPath is exactly that: a "view" of your data. Since you are
trying to "export" the data to Excel it may be easier to simply create a
new View (i.e. named: vwExcelExport) where you pull all needed "groups"
into this one "export" View. Does that make sense?

Now with that in mind, I want to make you aware of something that may or
may not come into play: InfoPath is able to create hierarchical data
structures (i.e. 1 -> Many) very easily. However, Excel would need to
"flatten" that data structure in order to display the same information. All
I am suggesting is that if you have not exported your current data
structure to Excel yet you may want to do so just to be sure you get what
you need for your other application.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mike]

Scott,

Thank you for your quick responce.


I still have the issue of taking information from two repeating tables and
inserting the information into a third one. The extract is later (after the
trhird repeating table is populated).

The third repeating table is needed on the invoice view so that we can
produce an invoice to the customer. The invoice repeating table combines the
labor view and the parts view. The upload to our financial system will not
happen until later.

 

[Scott L. Heim [MSFT]]

Hi Mike,

Ok - let me now get some additional information from you. In your initial
post you indicated the layout of each repeating table is different - am I
inferring correctly then that the data is also "different?" If so, how
would you be able to combine this into one table?

Could you post some sample data and your structure from the 2 repeating
tables so I can get a better idea of what you need?

Thanks,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mike]

Scott,


Thanks for your quick responce. Here is the layout for the 1st table...

Qty, Part No., Qty Picked, Qty Used, Qty left over, Qty Returned and Qty on
Order

2nd table

Start Hour, Finish hour, Number of hours, Accommodations Cost, Substance
Cost, Kilometers traveveled.

3rd table is as follows:

Qty, Item, Description, Price and Total.

The Qty would come from the number of hours on the second table, and the qty
used from the first table

The item would be coded as "hours" from the second table and "Part No." from
the first table.

The cost would be coded as a flat rate of $75.00 fro every reference in the
second table and the part number will have another external XML data
reference for every part in the first table.

The total will be a calculated field.


Hope this helps

 

[Scott L. Heim [MSFT]]

Ok - one additional question: is Table3 a "summation" only or for each row
in Table1 and Table2 we need to have a corresponding row in Table3?

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mike]

First table is a line by line and the second table is a summation.

 

[Scott L. Heim [MSFT]]

Mike,

Please accept my apologies as I am confused: table1 is "multiple row
(multiple line items) table. Table2 is a summation of Table1? In what way?
Why would this one be a repeating table? And then what is Table3? Line
items or summation?

You also indicated that the "Qty" field in Table3 is the "Number of Hours"
from Table2 and the "Qty Used" from Table1. So if Table2 is a "summation"
of Table1 is the "Number of Hours" field a "calculation" that totals the
"Qty" field from Table1??

Again I am sorry but I am not clear on your structure here...

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mike]

Scott,

I can understand the confusion. Let's see if I can clear it up.....


Table 1, Table 2 and Table 3 are all repeating tables with footers which
total the detail lines. Table 1, which is the time sheet view, I am only
interested in the total line ( this is the summarized total line). In Table
2, I am interested in all of the details. Table 1 summary and table 2
details will be placed as line items in table 3.

If this makes it simpler, I am interested in how to programically read
informtion from one repeating table, and put it into another repeating table.
I have read an intersting artical on how to insert row into a repeating
table by using the "Document.view.ExecuteAction
"xCollection::insert","group2_1"" method. This work great in test. After I
insert a row, how do I address inserting values into the table (and how do I
read from another)?

 

[Scott L. Heim [MSFT]]

Mike,

That does help! So now I am wondering if we can actually get you where you
need to be without code!

What about this as an option:

- Table1 (with the summary fields)
- Table2
- Table3 (bound to the same "group" as table2 - you can make this such that
a user cannot enter/delete the items)

Now so far what this will do is as you add items to table2 they will
automatically appear in table3 as well. However, at this point, we are
still "missing" the summary items from table1. You could now add the
columns you need to table3 by simply right-clicking on the last column (or
any column for that matter) and choosing Insert -> Column. Lastly, you
would add a text box to that new column and set the "Default Value" of this
box to the same expression as your summary expression in table1.

What you now have is a duplicate of table2 with the added summary data from
table1.

Does this make sense? Do you think this may be an option?

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mike]

Scott,

Interesting. If I have three items that are summerized from table 1, and
with the solution that you present, will there be 3 detail items in the table
3?

 

[Scott L. Heim [MSFT]]

Hi Mike,

Yes - with this assumption on my part:

- Let's assume table1 is as follows and has this data:

Name Hours Days
Scott 8 1
Mike 40 5

-- 48 6

** NOTE: The "--" above simply denotes the summary data

- What I am suggesting is that table3 is "bound" to the same group as
table2; however, once you do that you will now add 2 additional columns
(right-click -> Insert -> Columns) to table3: one for "Total Hours" and one
for "Total Days" where the "Default Value" field can be the same expression
as the summary fields above - this way as you add detail items to table2
they will appear in table3 along with the summary information from table1.

Let me know if you need sample steps and I can provide those for you.

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mike]

Scott,

There is some more information that you do not know about. I have to read
the table 1 and table 2 and recode the information for the invoice. As an
example, the reg hours has to appear as "Regular Hours Worked" on table 3.
Along with this, I have to code in the rate per hour to charge.

I think it would be ideal to programmically copy data from one repeating
table to another. I need to know how to read and update rows in a table by
the use of BV scripting. Is there anywhere where I can see how to do this?
Can you help?

 

[Scott L. Heim [MSFT]]

Hi Mike,

No problem. I am going to be tied up for a bit today but did not want to
leave you hanging on this. In a previous post
(http://groups.google.com/group/microsoft.public.infopath/browse_thread/thre
ad/96fe5ca27f92db15/41eb58cc879af2e7?lnk=st&q=InfoPath+Add+%22Repeating+Tabl
e%22+VBScript&rnum=6&hl=en#41eb58cc879af2e7) someone needed to
programmatically add rows to a repeating table. I realize this is not all
you need to do but maybe this will help get you started in the mean time.
There is sample VBScript in the above post that I provided...let me know if
this helps get you going and where you still need some direction (if you
indeed need more direction - that was bad of me to assume) and hopefully I
can fill in the blanks for you.

Thanks Mike!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mike]

Scott,

Thanks for the tip. I have already downloaded and tested the VB script.
It works great. It is very simple and I have researched various options
(i.e. removing rows). I also created the standard "Hello World" test with a
button and a text box - and this also worked great. I just have problems in
addressing the fields contained within the repeating tables. I am trying to
figure out how to programically read and set the values within the repeating
tables. Any help that you can give me would be great.

Thanks