Repeating Table from Secondary Data Source with additional fields from Main Data Source - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

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

Last post 05-30-2018 11:56 AM by Daniel_5584. 20 replies.
Page 2 of 2 (21 items) < Previous 1 2
Sort Posts: Previous Next
  • 05-03-2018 07:33 AM In reply to

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

     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. 

  • 05-03-2018 08:03 AM In reply to

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

    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.
    Hilary Stoupa

  • 05-03-2018 11:57 AM In reply to

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

    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.

     

  • 05-03-2018 01:03 PM In reply to

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

    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.
    Hilary Stoupa

  • 05-03-2018 01:37 PM In reply to

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

    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. 

  • 05-30-2018 11:56 AM In reply to

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

    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. 

Page 2 of 2 (21 items) < Previous 1 2
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.