Data Connections

Re: Speeding up a form containing Data Connections

ABENNY — Tue, 28 Mar 2017 14:46:42 GMT

Can you send me an infopath form with this as an example?

Re: Speeding up a form containing Data Connections

WonderLaura — Thu, 17 Mar 2016 17:40:26 GMT

Hi Theonus,

My blog has just moved, so the new link to that post is http://wonderlaura.com/2011/8/1/infopath-query-specific-sharepoint-list-data

Re: Speeding up a form containing Data Connections

Theonus — Mon, 06 Jul 2015 10:28:17 GMT

Hi Julie,

I sent this to someone else after they contacted me from this thread. I emailed them with some images as examples, let me know if this helps you and if you're still unsure then I'll try to help more:

I also have a little test site to show people that updates choice fields based on users answers to a previous choice list. My example shows this:

Choice 1: Fruit or Vegatable (Choices “Fruit” or “Vegetable”)

Choice 2: Select Item (If the user chooses Fruit only fruit will show in the choices. If they select vegetable then they only see vegatables).

This example uses the same method as you would for bringing back data to populate a field in a form or such like. In InfoPath when you’re making the rules you need to think about when you Query using a data connection, I like to think that setting a Query field value is like entering a keyword on google before you press search. Imagine, the reason your system is taking a long time to do what you’re doing now because you’re searching for ALL and then filtering through the results for what you want.

If you switch to using Query fields it is like searching only for what you want and then only getting one (or fewer results). This is therefore extremely quick in comparison. In your rules set it up like this (I’m going to imagine your list a little bit so forgive me if it’s a bit out but I hope you get what I am saying)

Set a rule to say when a field is not blank, Rule type = Action:

1. Set a fields value. Now this one needs to be the Query field of your data connection, so for example if you had a column named “Court Name” and in your data table you had “Court Name” and “Court Address”, you would want to set the Query field value of the Secondary data source (your external data connected list) to equal the value the user selected in your form for “Court Name”. This is a little bit like putting on an autofilter! Therefore reducing the results of your upcoming Query using a data connection.

2. Next, you can now Query using a Data Connection. Because you’ve applied this Query field your data connection will only bring back one address now!

3. Finally you just have to set a fields value. This time you would set the value of the field on your form to the value of the data field in your secondary table (the one you just ran the data connection for).

This is done. It all sounds MUCH harder than it is. It’s only hard until you manage to pull it off once, then you totally get it! I’ll show you mine doing the same as you would above:

Step 1:(In my example because I’m using a LOOKUP type field as a choice field I have to link to the ID of the data connection as the system remembers the users choice in the form of the ID of the record from the external table – It’s a bit annoying and why it took me a while to work out – but if you don’t use that type and instead use a normal choice field and populate it will a data connection instead – it’s easier and instead of ID in my example I would be setting the Query field “Party Name” to the value of the field “Party Name” in my form.

Step 2: Query!

Step 3: Set values! The easy bit! So I’m now setting my field in my form to the value of the “Data Field” in my secondary data source.

Let me know how you get on and if you didn’t get any of it. I’ll help you until you get it. Trust me when I say it’s very easy once you’ve got your head around it. Let me know how you get on.

Re: Speeding up a form containing Data Connections

julie_h_lee — Sat, 04 Jul 2015 16:48:54 GMT

Hi,

I have the same problem, but very unclear on the instruction. Can you please send me a copy of your form or a screen shoot of the rules that you have set up. Thank you so much!

Re: Speeding up a form containing Data Connections

Theonus — Tue, 05 Nov 2013 13:17:18 GMT

I have the same issue at times reading techie solutions..! I'm one of those people who need to understand exactly what I'm doing and why rather than to follow some step by step process blindly. My way means I always end up getting my head around something and therefore I can apply that knowledge on future projects.

Re: Speeding up a form containing Data Connections

Cederpants — Mon, 04 Nov 2013 20:58:35 GMT

Ugh, I have been racking my brain for weeks! Finally, someone laid it out in a way that made sense to me!!! No offense to all the techies around here, but I don't yet understand a lot of the language that gets thrown around. My data connection time dropped from 4+ minutes to only a few seconds for each new item in my repeating table.

Jimmy, I'm so glad you had this problem, so that I could fix mine also!

Re: Speeding up a form containing Data Connections

Tronc — Thu, 31 Oct 2013 22:37:14 GMT

Hi Jimmy,

I'm trying to do something similar and I think I understand the rules you posted above.

But when I execute the rule to query using data connection. My data fields never show any data. The only way I can get the data to show is if I tick Automatically retrieve data when form is opened.
However the list is quite large and time consuming, or errors saying there are to many list items. So I was trying to use a queryField then submit that to retrieve the relevant dataFields.

SharePoint List example
StudentNo, House, Year, DOB

Using StudentNo as a queryField

With rule of:

Condition - None rule runs when field changed.

Run these actions - Query using a data connection

When I preview the form and manually input the student number. The rule is trigger. It goes away (I thought) to retrieve the data to display it in my Data Connection Data Fields on my form.

But nothing is displayed.

I found this example online that I followed but it has the Automatically retrieve data when form is opened ticked. http://www.youtube.com/watch?v=VQlWiCdbKBg

Any assistance would be greatly appreciated.

Re: Speeding up a form containing Data Connections

Theonus — Tue, 23 Jul 2013 09:05:41 GMT

Hi Jimmy,

Just a little courtesy update to say I figured it out and resolved the performance issues with the Query fields!

It was simple, and I'm glad I now know how to use them! All I had to do was add 1 rule and then modify my other rule that sets the value (for anyone who might read this later).

Rules go like this now:
When the drop-down list is not blank

1 - Set the value of the Query field in the secondary data connection to match the value selected in the form

2 - Run data connection (effectively only bringing back a singel record now)

3 - Set value of other field in our form to the value of the corresponding data field in the secondary data connection.

All I had to do was add rule 1 and remove the filter from rule 3 that was in place before that.

Now instead of waiting 1-3 minutes for my form to populate, it takes about 1-2 seconds max. This is a great way to do things and I knew it must be simple! It's done my head in for a week or so on and off!

Thanks for all your help Jimmy.

Steve...

Re: Speeding up a form containing Data Connections

Theonus — Mon, 22 Jul 2013 14:50:33 GMT

Hi Jimmy,

Yes I had no knowledge or experience using this method before I hit this snag. It's one of those things where I've sort of had to learn as I go doing this project.

I'm going to give that a go and see how I get on with it. I'll let you know - thanks for your patience with me. I'm pretty good at getting my head around things once I've done them but I'm self-taught on SharePoint and InfoPath so it can be a bit of a blurry path to walk for me!

Steve...

Re: Speeding up a form containing Data Connections

Jimmy — Mon, 22 Jul 2013 14:41:37 GMT

Ok, well is there some part of the Query Field functionality that you don't understand? The idea is that you would simply set the ID query field in the data source to the value that was selected, and then perform a Query a data source action. If the ID is unique, this should return just one row, and you can copy the values over with out any XPath filtering.

Re: Speeding up a form containing Data Connections

Theonus — Mon, 22 Jul 2013 14:27:03 GMT

Hi Jimmy,

Yes it would be the name of the field in the main data source where the rule is taking place! Your XPATH formula does work and get the results I need however it seems at the same speed as my way.

The lower down the list your selection - the longer it takes to come back with the result. The last entry on the list just made me wait over a minute to populate 1 field on my form. I'm thinking I must work out some way to get this "Query Field" working as to cut down the records returned by the data connection. I'm still reading this (Link) stuff and I'm thinking about trying to mess around with this but unsure how it will work out as they are doing this to populate a choice list and not to set several field values.

Re: Speeding up a form containing Data Connections

Jimmy — Mon, 22 Jul 2013 13:38:22 GMT

So would /dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Party_x0020_Name be the field in the main data source where this rule is taking place? If so, does this speed up the performance at all?

xdXDocument:GetDOM("PARTY TABLE1")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:PARTY_x002d__x0020_CODE[../d:ID = current()]

Re: Speeding up a form containing Data Connections

Theonus — Mon, 22 Jul 2013 12:46:20 GMT

Hi Jimmy,

- I'm using InfoPath 2010, I can see the Query fields when I'm creating the rules but I just haven't ever known how they worked and have historically always just used Data fields and filtered the results so that it only brings back the record I want to get the data from. I assume however that in the background this involves bringing back the entire table and then going through each record to set the filter. Where a query field I assume would only bring back the single record I need in the first place therefore speeding up the whole thing. I just need to work out how to do it.

- The data connection I am using does already have the values I need yes! I have pre-populated the lists that I'm trying to feed from. I have made up the example above because I didn't want to disclose anything commercially sensitive but the principal is the same.

- OK I will show you an example of the XPATH formula I am using which works but just is slow. Obviously the table names and field names are somewhat different to my example but that's due to the reasons above:

xdXDocument:GetDOM("PARTY TABLE1")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:PARTY_x002d__x0020_CODE[../d:ID = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Party_x0020_Name]

Thanks for even taking the time to reply!
Steve...

Re: Speeding up a form containing Data Connections

Jimmy — Mon, 22 Jul 2013 12:15:09 GMT

A few questions:

What version of InfoPath are you using? Query fields are a built-in feature in 2010 and later.
Are you saying that the data connection you are using to populate the dropdowns already has the telephone number and other values you need, or did I misunderstand that?
Could you show us the exact formula you are using right now (after clicking the Show XPath option)?

Re: Speeding up a form containing Data Connections

Theonus — Mon, 22 Jul 2013 11:22:15 GMT

Update: I have been researching around the web and although I still haven't found anything I've been able to transfer to my form yet I think I might be onto something.

I think the direction I need to go is setting up a Query field or something before I run the data connection. I was reading this page and it seems to indicate that this would bring back only the record applicable to my next action and therefore by default would (should?) speed up my processes.

If anyone knows a thing or two about Setting Query Fields value and then triggering the data connection the advice would be well received!