InfoPath Dev

 Objective: A "ordered-goods X shipped-goods" form, where half the page lists all invoices for a client´s construction (product and quantity only). The other half of the vertically split page shows all shippings. And below each there is a sort of summary, an outline of everything the client bought, and everything the company has already delivered (and what still needs to be delivered).

PURCHASED MATERIALS
Invoice 1
Material          Sq2
marbleA          10
marbleB          20

Invoice 2
Material          Sq2
marbleA           3
marbleC           2
-----------------------------------------------------    and this is what I need below (if I get this working, ill get the rest of the form working too)
PURCHASED MATERIALS OUTLINE
Material          Sq2
marbleA           13
marbleB           20
marbleC            2

(this is what it should be like.. the outline must show only one of each marble type, and the sum of meters from all invoices.. but I can´t get this to work)

1) the material-sq2 is a repeating table, inside a repeating section (so user can add another invoice to the form).
2) purchased materials outline is a repeating table.

My datasource is like this:
clientcontrolform
........purchasedmaterials (group) (repeating section)
..............invoices (group)
..............idinvoice (field)
..............individualinvoice (group)  (repeating table)
.....................purchasedmat (field)
.....................purchasedmatsq2  (field)
........purchasedmaterialsoutline (group) (repeating table)
..............purchasedmatoutline (field)
..............purchasedmatsq2totaloutline (field)


Basically, i need to populate the purchased materials outline with a single entry of each marble..how?

Hello,

I am little bit confusion with your data source and summary. I am not clear about your data source.

From summary,

Invoice 1 and Invoice 2 repeating section values with different columns and you want to show the result is in separate section called ‘PURCHASED MATERIALS OUTLINE' in that for each field you have to add the formula like sum (marbleA).

One thing I am not getting in Invoice 1 and Invoice 2 sections contains only two fields and you want output is for three fields which is not equal to your schema?

Shiva:

Hello,I am little bit confusion with your data source and summary. I am not clear about your data source.From summary,Invoice 1 and Invoice 2 repeating section values with different columns and you want to show the result is in separate section called ‘PURCHASED MATERIALS OUTLINE' in that for each field you have to add the formula like sum (marbleA). One thing I am not getting in Invoice 1 and Invoice 2 sections contains only two fields and you want output is for three fields which is not equal to your schema?

Hi Shiva! Thanks for replying!
Sorry, the Purchased Materials outline is the summary. This part of the form (which is a repeating table), must retrieve all unique values from material names. The numbers of sq2 are not important, I´m only having trouble getting the unique names of marble(materials) to the summary below all invoices. Think of it like this: Invoice1 sells blue and green. Invoice2 consists on the sale of blue and yellow.

The summary (purchased materials outline) is a repeating table which must automatically list every unique marble sold (retrieved from all invoices above), so I don´t get blue twice (duplicate values).

Is the summary table a read-only thing? If so, couldn't you create a SQL view with the data you want, using aggregate functions like SUM(), and then add another data connection to your form to pull the data back?

Hilary Stoupa:

Is the summary table a read-only thing? If so, couldn't you create a SQL view with the data you want, using aggregate functions like SUM(), and then add another data connection to your form to pull the data back?

Hi, Hilary! You´re right, the summary is a read-only table. But the data is not being retrieved from a secondary source (sql).. it has to be retrived in real-time (if you will) from the table the user entered. This form will be changed and saved by the user many times along a couple of weeks before it´s info is sent to a databse.

Using the not function of preceding-sibling (very popular in this forum for what i could see) i was able to get this:

let´s say the user entered this into the materials table..
invoice 1
blue    10
green   5
yellow  5
yellow  3
then he added another repeating section so he can add invoice 2
invoice 2
blue     3
black   2
black   1
yellow  2

the summary table should tell the user, while he is typing the invoices, all colors and the sum of colors´s values, like this:
black    3
blue      13
green    5
yellow   10

however, i´ve linked the summary repeating table to the node tree just above the field with the color, and then directly with the color, and filltered the table to show only data with unique values using:

not(my:materialcolorname = preceding-sibling::my:individualinvoice/my:materialcolorname

the result im getting is
material summary     sq2
blue
green
yellow (only once, so it worked for at least one individual invoice)
blue
black (only once again, so it worked for the oter individual inoice)
yellow (this color is also present in invoice1, but this is the problem..it is showing up again)

I know, this thread is quite old bur is it already solved? I'm interested in a summary of a repeating table in an other repeating table as well.

Joern,

Sorry, it really has been a long time since I posted it, but I still know what it was for. Sorry, no, I could never get it to work.

Today I would do something like what Hillary sugested above, but that is the case only if you are using a database.