SharePoint Integration

Re: Needing Infopath form to refresh in order to view SharePoint calculated column results

Vashista — Tue, 13 Jan 2015 12:01:16 GMT

After a lot of web research, i found your answer, and it is very helpful, I do have same type of rewuirement.
My list is having calculated fields with very complex formulas, I designed the infopath form by which user enter values in the fields and on based of these values, value is calculated in calculated columns.

I created a view for newItem form and another view for display calculated values. followed the same steps you mentioned but unfortunate no success.

Could you please elaborate more?

Re: Needing Infopath form to refresh in order to view SharePoint calculated column results

JasonJJones — Tue, 19 Aug 2014 15:00:01 GMT

I was finally able to get the issue resolved using a different technique than donstephen described above. I did not attempt those, but after reviewing them they may have worked as well. Below is what I did to query SharePoint calculated field values into my form by using the main data connection.

What I did was to create a rule with the first action being to "Submit the data" and then add another action to set a unique ID field in the queryFields so that in the next action of the rule when I queried the connection it only returned a value based on what I set in my queryFields.

You must submit your data first, but then in knowing the ID of the record was definitely unique, you then set your queryfields "ID" field = the ID field from the dataFields. Once you've done that, just add a query to your data connection and voila, any SharePoint calculated column field values which would
have been calculated during your initial "submit" action will be pulled back into your form.

Re: Needing Infopath form to refresh in order to view SharePoint calculated column results

donstephen — Tue, 19 Aug 2014 09:42:55 GMT

Hi Jason,

Thank you for the clarification. Yes, I would agree that re-creating the formulas on the fields would be tedious work. Since IDs are generated after, what I would I suggest at this point is generating your own unique ID on a hidden field. Something like concat(userName(), translate(now(), "T:-", "")). This way, you can filter your query to only return the current data using this value. Here is a webinar we have which discusses Secondary Data Connections and Filters:

https://www.youtube.com/watch?v=VQlWiCdbKBg

If this doesn't work, you could also try using REST data query:

http://msdn.microsoft.com/en-us/library/office/fp142385(v=office.15).aspx

Hope this helps.

Re: Needing Infopath form to refresh in order to view SharePoint calculated column results

JasonJJones — Mon, 18 Aug 2014 15:35:13 GMT

I can display the resulting values from my SharePoint calculated columns successfully, but only if I close the form out and then manually reopen it. I can NOT create a data connection which includes SharePoint Calculated columns. The only data connection which includes these fields is the Main Data Connection. The issue is I need to submit the data and then query it again in order to capture the values SharePoint calculated with the submit and display in my form without closing it and I have not found a successful method to accomplish this. When you query the Main Data connection it pulls in the entire data set for the particular SharePoint list and then gets confused and displays the values in the form from the very first record from that SharePoint list and not the data from my current record.

As per your suggestion, the logic is extremely complex for the SharePoint calculated fields which I don't believe can be replicated in an Infopath calculated column. This was my reasoning for choosing to use SharePoint calculated columns rather than attempt to use Infopath to calculate values. Below is an example of just one of the SharePoint calculated columns.

IF(AND([bp_bid_tier_Complex_score]=0, [bp_bid_tier_NS_score]=0), "Standard", IF([bp_bid_tier_NS_score]= MAX([bp_bid_tier_Complex_score], [bp_bid_tier_NS_score]), IF(AND([bp_bid_tier_NS_score]=1, [bp_project_management]="1.00000000000000"), "Standard - see comment", "Non-Standard"),""))&IF([bp_bid_tier_Complex_score]>[bp_bid_tier_NS_score],IF(OR(AND([bp_bid_tier_Complex_score]=2, [bp_policy_deviation]="1.00000000000000"), AND([bp_bid_tier_Complex_score]=3, [bp_policy_deviation]="1.00000000000000", [bp_project_management]="1.00000000000000")), "Non-Standard - see comments", "Complex"), "")

Re: Needing Infopath form to refresh in order to view SharePoint calculated column results

donstephen — Mon, 18 Aug 2014 08:54:58 GMT

Hi Jason,

Without querying your Main Data Connection, a workaround you could use is setting the calculated boxes' values to the same formula logic used in SharePoint. This way, you wouldn't need to query SharePoint anymore. Otherwise, you'd have to query SharePoint without closing and opening the form. May I know what's hindering you from enforcing specific values to the current form?

Regards,

Needing Infopath form to refresh in order to view SharePoint calculated column results

JasonJJones — Wed, 13 Aug 2014 21:34:21 GMT

I have a complex tool developed in SharePoint and Infopath which requires users to answer a series of questions. The responses to these questions are then used to come up with a status field and various other SharePoint calculated fields. Due to the extremely complex nature of the conditions involved with setting the status field, it was determined that the field would be a Calculated Column in SharePoint. I actually have 27 SharePoint calculated columns in my list which all are based on the users responses. My process flow has the users respond to the questions, then click a button which switch's views to a "dashboard". My issue is this dashboard and another view displays most of the SharePoint calculated columns and when I switch to either of these views the dashboard is blank. Of course if I close the form and reopen it those views are populated with the current values SharePoint calculated.

I attempted to add a rule to query the Main Data connection, but there is no way for me to explicitly force that to only query the data for my current item. It displays data from another list item when this was attempted.

Is there anyway for me to query these calculated values without closing and opening the form?