InfoPath Dev

 Hello Everyone,

I am looking to create a table of information with some external data. Once that table is created I would like to create a calculated value with its corresponding information (to do some formulas against it - but first I need to get the value).

 

I have a sample form with 2 fields like below.

 

 

First field gets its information from an external secondary data source

Second filed is a calculated filed bound to the Main form

 

What I want to happen, is when I run the form the calculated filed should get populated with the field on the row its in.

 

I have tried many different iterations using both formulas and manages rules. I would post them but I think it may just confuse the issue.

 

Any help would be greatly appreciated!

Joe

 

Just some work I am doing and I know I said I wouldn't post my code to avoid confusion but maybe it will be helpful. Here are some of my attempts:

 

I put a rule on the repeating section to set Calc=Title - didn't work
I put a rule on "Activity Description" that Calc=Title - It will populate the FIRST item but only after its modified

I put a rule on the calculated value that its value is = title, this yields a result without any interaction.
*This seems to be where I want the code to go. The only problem is it yields ONLY the first column value in all the results.
       xpath = xdXDocument:GetDOM("StampList")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title - I think this is where I want to current() to be.

The closest I have come to was putting the below rule on the "Active Description"
       xdXDocument:GetDOM("StampList")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title[../d:Title = current()]
The only problem is I have to interact with the row and it will then change "ALL" the calculated values to the row I am on.

I have tried other formulas on the CALC field like this:
       xdXDocument:GetDOM("StampList")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title[../../d:SharePointListItem_RW = current()]
       xdXDocument:GetDOM("StampList")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title[../d:Title = current()]

But neither yield any results.

I think my solution lies somewhere in here, I just think I am not formatting it correctly.

 Hi - you'll only ever have a single value in "Calc" - it is a single field in your main data source. You can still use a calculated value - since it is inside the repeating secondary data source section, it will always "know" what row it is in. What is the value you are trying to show here?

Hello Hilary,

Thank you for responding!

If you notice from my "Sample" image, I am pulling the first row of information from a SharePoint table (Item1, Item2, Item3, etc.)

My goal is to make the calculated value (2nd column) equal to EACH corresponding Item in the first column of the repeating section.

But with all the formulas I have tried I can not get the 2nd column to display correctly, as you can see above that the results "Item3" only appears in the second column.

My goal would be to have the second column look identical to the first column like below (*Image was edited for desired results*)

Thank you!

 

 I know you are trying to make things clearer with screenshots, but I still do not seem to understand. Can you attach a sample form under the options tab?

 

 Sure, I apologize for the confusion.

 In short...I am trying to create a dynamic list in the first column that gets its information from a SharePoint list, and the second column should know what's in the first column (so I can do comparisons and formulas).

 So, the issue I see is that your binding is kind of odd. It almost seems as if you are hoping the main data source will have as many nodes as your secondary data source - but InfoPath doesn't work that way....

You can certainly add a calculated value (not bound to a main data source field) in your repeating table that is bound to your SharePoint list - but I don't think that is what you want to do. You can also include a number of instances by default in your main data source - but what you currently have in your sample form is just not going to work the way you anticipate.

You can just use a calculated value here - one with a formula in it, not bound to your main data source. Also, the formula in your main data source field is kind of strange:

xdXDocument:GetDOM("StampList")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Title = current()/../../my:group2/my:Calc

This is going to compare the first Title in the StampList to the current value of the my:Calc field.... I'd sort of expect this to return true or false, actually.

So let's give an example like this...

Let's say I have a SharePoint list that has 3 items in it.

Car
Boat
House

 

I then use a repeating table to display those items on a
form

SP List	Formula
Car
Boat
House

 

Then in the Formula column, I want to do a formula (in this case a concat) and add the word "OBJECT" at the end of each item and display its results to get this:

SP List	Formula
Car	Car Object
Boat	Boat Object
House	House Object

So if someone was to come along and add "Plane" to the SharePoint list I would get the same results when ever someone opened the form.

SP List	Formula
Car	Car Object
Boat	Boat Object
House	House Object
Plane	Pane Object

Unfortunately, as you pointed out, the closest I have gotten was something that resembles:

SP List	Formula
Car	Car Object
Boat	Car Object
House	Car Object

The formulas I have tried never seem to take each row into account;they only take the first row.

 

*Additionally I do not have a requirement to store the information but only to display it.

Technically I would be open to anything that would achieve my results.

Thank you for all of your assistance so far!

 In the attached copy of your form, I've added a calculated value to the repeating table that does this. By default, if you are referring to an item in the same dataset as the repeating control is bound to, you'll get the current row's item.

 YES!!!! That was it!!!!

Thank you soooo much!

So in short, the issue was where I placing the formula.

Thank you so much! I have been working on this for about a week now.

 Hello Hillary and again I cant thank you enough...

Now of course with this solution comes a new "hurdle"

 I was going to post a new thread but it directly relates to this solution you provided.

 

As you supplied the SAMPLE.XSN I was trying to add a managed rule to the "Calculated Value".

It appears that I can only put a rule on something that is bound to a node, and it appears that the calculated value may not be.

Do you know if there is a way to accomplish this task?

So for example, if the calculated value contained the word "OBJECT" (which they all would) then highlight it yellow.

 Action rules can only go on fields (and only fire when they change). It sounds like you just want conditional formatting though - you can put a formatting rule on a calculated value.

This was a really helpful post for me. I had a similar problem to Joe in that the subsequent sections would bind to the first value. I was using an Action rule to set a field's value; when a new section was added, the rule would set each value based on the field in the first section. I switched to using a calculated value (no concat, so it was simply pulling a value from a secondary data source) and it worked perfectly. Thank you!!

Hilary,

Thanks for the help in this thread so far, I'm glad I managed to bump into it because it is SO close to what I am trying, and failing, to do. The difference bewteen my issue and this is that I would like to pull data from an external source to display to the client, however I believe binding issues are causing my problem.

So, I have a list of cables, my repeating table, with a cable type ID. I want to use this cable type ID to marry up a cable type from a seperate data connector then display cable type data along with my cables in the repeating table. Is this possible? I feel like I am very close, but the calculated field just doesn't work and is always blank at the moment.

Regards,

Steven

Hi All,

Just an update I managed to do it, with a workaround.

Using a button I use function to set the label to the desired text and then format the button to look like a normal cell.

It's a workaround and not the nicest of fixes but it works.

Regards,

Steven