Newbie Questions

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Daniel_5584 — Wed, 30 May 2018 18:56:06 GMT

I found a solution to grabbing the data, based on what Hilary mentioned in her last post. It involves creating helper fields in the repeating table of the secondary data source.

In the repeating table of the secondary data source create duplicate fields for all of the columns of your data and call them M[FieldName] or whatever makes the most sense to you. These fields will be promoted as merged fields when you publish. The default value in these fields will be a concatenation of the duplicate field's data, a delimiter, and the row reference.

Ex:
I have a field that populates a combo box of product names from a list; ProductName. I create a duplicate field for merged data, MProductName, in the same repeating table. This field (MProductName) is not displayed on the form.

The default value for MProductName is as follows: concat("|", count(preceding-sibling::*), "|", ProductName, "||", count(preceding-sibling::*), "||"). This will result in the following value for MProductName: |1|[ProductName]||1|| |2|[ProductName]||2|| |3|[ProductName]||3||...

Now publish the secondary data source, promoting the repeating table fields M__ as "merged." Now all the data is available for you to parse in your main InfoPath form, with unique delimiters surrounding each field's data.

On your main InfoPath form you have a repeating table in which you wish to populate the data from the secondary data source repeating table.

The default values for these fields should be:

substring-after(substring-before(ProductName[SecondaryDatasourceID = MainDatasourceID], concat("||", count(current()/../preceding-sibling::ProductInformation) + 1, "||")), concat("|", count(current()/../preceding-sibling::ProductInformation) + 1, "|"))

What this says is: in the string for Product Name, from the specific form in the Secondary data source, grab everything before ||[current row number]|| and everything after |[current row number]|. That's it. As long as you have a unique identifier between the datasources to properly filter the data it will populate only the data from the corresponding repeating table element. The nice thing about this is that even if you don't have the correct number of rows prepopulated in the main InfoPath form all the user needs to do is append rows to populate any remaining data.

So it doesn't look as messy or confusing you can always create an additional helper column in each repeating table called "counter." This field would hold : count(current()/../preceding-sibling::ProductInformation) + 1.

counter can take the place of "count(current()/../preceding-sibling::ProductInformation) + 1" in your subsequent fields.

There is an alternative method using the "double eval()" trick to concatenate the data into a field outside of the repeating table, but I could not get that to work.

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Daniel_5584 — Thu, 03 May 2018 20:37:15 GMT

I was afraid of that. It is frustrating since I know the data is there, but I just don't know how to get to it. Coding would make it so simple, but I am not allowed to use custom code on the forms. I will play around with merging the promoted data and will report back if I find anything useful.

Thank you for your time.

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Hilary Stoupa — Thu, 03 May 2018 20:03:15 GMT

I understand now. So the issue you will have is that there is no easy (non-code) way to get that repeating data from one form to another. If you promote the repeating data as merged, if every field in each row is filled out, you may be able to use something similar to the default data approach outlined in this thread - but I'm not confident in that. My guess is that if the promoted column is merged your secondary data connection will have a repeating "value" for each column, but I'd have to test that to be certain.

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Daniel_5584 — Thu, 03 May 2018 18:57:38 GMT

My original form is the CCR document. This gets filled out by the user and then submitted to a SharePoint form library. Depending on what the needs are, different documents may be created using the data from the CCR form. Credit memo is one, RGA another, and CAPA is the third. I am attempting to use the ID field from the CCR form as a way to tie all the associated documents together.

Let's say that I am creating CCR 1950. I then create a credit memo from that form using a workflow and populate the field CMNumber to be 1950 (using the workflow). Then I have the Credit Memo form fetch the product information data from form CCR 1950 by setting default values and using the ID as a filter. I am not sure how else would I have it populate the repeating table data from a particular form. Am I approaching this wrong?

I am not familiar with merging fields, from the little bit I read today it appears as though what I have actually done in my CCR form is to only promote the first row of data from the Product Information repeating table; is that correct? I will continue to read up on promoting merged fields today.

Also I do many squirrely things on that form, as I am not that familiar with InfoPath and working with VBA in the past has led me to believe that when Microsoft auto populates something it is generally done in a convoluted manner. As such, when I create repeating tables I notice that two nested groups are created, the inner group being the repeating table. I generally un-nest the groups and delete the empty group. I'm guessing that maybe I should not be doing that, but I don't know what function the "outer" group serves.

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Hilary Stoupa — Thu, 03 May 2018 15:03:36 GMT

I'm confused by your formula. In the Product Name field in the main data source of your AP_Credit Memo template, you are comparing the ID for the product to a non-repeating field in your main data source, that is, ID from the secondary data source should equal the CRMNumber in the form. That confuses me, since every single ID in your product list will be unique. Can you explain why you are doing this, and help me understand? In terms of promoting repeating data, you can promote as "merged" which will then give you a semi-colon delimited list in the column.

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Daniel_5584 — Thu, 03 May 2018 14:33:05 GMT

This is the form template I am using as my secondary data source. Each belongs to its own SharePoint Library, if that makes a difference.

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Daniel_5584 — Thu, 03 May 2018 14:29:02 GMT

Hi Hillary,

Thank you for your quick response to my post. Changing to IE did the trick. I seem to only be able to attach one file to a post. The one attached to this post is my main data source. I will attach the secondary data source in my next post.

To answer your question regarding the XPath, this is the XPath of the promoted group: /dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Product_x0020_Information

However, that is my issue. These are two separate forms; each a template for its own form library. When publishing the form I am using as a secondary data source I had tried to promote the repeating table group, but it only promotes it as a count of the group items if I add it as a column, and will not promote it at all if I try to use it as a web part connection (I am fairly new to SharePoint and InfoPath, so I still don't fully understand much of it yet).

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Hilary Stoupa — Wed, 02 May 2018 23:16:15 GMT

What is the XPath to your repeating group? You can right click on it in the fields pane and select copy XPath to find it. You can also try using Internet Explorer to see if you are able to attach a file under the Options tab & I can give you a hand - sometimes the forum works better w/ IE.

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Daniel_5584 — Wed, 02 May 2018 21:55:15 GMT

Hi Hilary, I am having a similar issue as the OP. I am attempting to pull field data from a repeating table in a secondary data source to populate a repeating table in my main data source. My secondary data source is a SharePoint InfoPath form library. I am not able to use code as my site does not allow it. I am trying to use default data and Xpath to set default values and, thanks to the help I received reading this post, I feel that I am very close to a solution but I am not there.

The XPath I am using is:

xdXDocument:GetDOM("Customer Complaint Reports")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Product[../d:ID = xdXDocument:get-DOM()/my:myFields/my:CMNumber][count(current()/../preceding-sibling::my:ProductInformation) + 1]

This will successfully pull the first item, but I cannot get the remainder to populate (Even if I set the number to "2", "3", etc.). Can you tell me what I am doing wrong?

I attempted to attach my forms for reference, but I get the error that the upload webpage may be down or may have moved to a new address.

Any help would be greatly appreciated.

Thanks,

Daniel

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Hilary Stoupa — Fri, 03 Mar 2017 16:06:53 GMT

The general approach should be the same, regardless of the names of your fields. Attach your form file under the Options tab if you need a hand. :)

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

ganesh.thiagarajan — Thu, 02 Mar 2017 22:42:19 GMT

Hi Hillary, Thanks for the innovative solution, I had a run through the Data source that you have used, I have a slightly different source and looking to see how I can use your solution. Your Sample Data Source: A A-1 A-2 A-3 B B-1 B-2 C C-1 C-2 C-3 C-4 My Sample Data Source: 1.20 GENERAL INFORMATION Port of the last Port State Control inspection (this includes if vessel was detained, or if significant deficiencies were listed) 10 1.23 GENERAL INFORMATION Date the last special survey was completed 10 1.24 GENERAL INFORMATION Date of departure from the last class-credited drydock/repair period 3 Question: Is there a way to Populate each 'Question' tag as a separate row with QNo,Category,QText,PotentialRisk as columns to that row?

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

Hilary Stoupa — Thu, 12 Mar 2015 15:01:28 GMT

Oh - I see - I hadn't realized your secondary data had repeating fields. If you want the same structure in your main data source, I think you'll need to make field29 (for example) in your main DS repeating, then add instances in the default data and set default values again.

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

lindaopiyo55 — Thu, 12 Mar 2015 08:57:45 GMT

See preview of form attached.

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

lindaopiyo55 — Thu, 12 Mar 2015 08:53:27 GMT

Hi Hilary, I've attached the form for your review. Please open it in design view to access. It seems the issue is with how the data is being copied to the main data source (group 29). Even though I have set 3 of these groups with the same default value, it copies all of it into the first line. Please see the preview attached in the next post. I would really appreciate your help.

Re: Repeating Table from Secondary Data Source with additional fields from Main Data Source

lindaopiyo55 — Wed, 11 Mar 2015 09:37:24 GMT

My form picks data from a sharepoint list, & you may be unable to preview this data due to restricted access to the SHP. How else can I replicate a secondary data source without connecting to a website? I wanted to create an xml doc for the data connection but I don't have xml notepad. Even sharing the form is proving a challenge - but I'm trying to build my infopath skills. Maybe you can share a quick tutorial on how one executes option 3 so I can see if I missed a step. Or could it be because your form picks data from an xml file while mine is from a SHP list?