InfoPath Dev

Hi all,

I'm new to the forum and relatively new to InfoPath (with regards to doing anything complex at least).  I wonder if you can help me with a bit of a speed issue I'm having.  If you don't get what I am trying to say below skip to my example as I have a tendancy to babble a little! (Sorry)...

 

I have an InfoPath form which has some drop down 'pick lists' (choice style fields) setup as LOOKUP fields.  They look to other Lists to grab the options within the menu.  That part works fine and the form seems to load quickly enough (I just wanted to add this detail in case it becomes relevant later).

I have then created some rules that state when the field is no longer blank, it queries for data using a secondary data connection and then auto-populates some other fields based on what you've selected.  It works however it's slower the lower down the list you select!  The difference is extreme, if you select one of the first few options then you can expect the rule to complete in a few seconds and that is that, however if you select something near the bottom of the pick list then you can bank on waiting 2-3 minutes or more for it to complete which doesn't sit well with me since the lists it's looking in aren't mega huge, we're talking about a few hundred records tops here.

 

Example:  (I think it's difficult to explain this so I will put this example in place) - Imagine this scenario which is fake however similar to mine:

 

LIST A - Contains the fields:  [Name] - [Telephone Number] - [Email Address] - This List is fully populated and has names ordered A-Z.

LIST B - Contains the fields: [Name - LOOKUP FROM LIST A] - [Telephone Number] - [Email Address] - [Some other random fields] - This is the List our form is gonna submit data to.

So the form opens and it loads up the name field with it's values by doing the automatic data connection query that happens when you create a LOOKUP field.  That all goes well and you're now looking at a nice list of names in the drop down box.  Once you select a name the form runs a rule to bring in the telephone number and email address for the name you've selected using a secondary data connection and the action Set a fields value.

How that works is I say in the rule:
Condition - Name is not blank
Rule Type - Action
Run these actions - Query Using a Data Connection (Set to bring back the relevant fields in LIST A)
Set a fields value - Field: Telephone Number - Value: I setup this in advanced view.  I select the secondary data connection, I pick the field [Telephone Number] from that data connection.  I then click "Filter Data...", Add a filter using the ID field of List A to match the value of the Name field in my form (I know that because it's a LOOKUP field it stores the value in the background of the form as the ID number - hence I filter on that instead of the actual name).  I then OK it all down and it saves the rule and it does work fine and quickly in preview mode!

The issue comes once you publish and start using the form in the browser.  Then if you picked the first name on the list it would be quick.  The last on the list might take you 6 minutes!  Who knows, but it's a logn time!

 

My own attempts at working this out
The weird behaviour looked to me like it must be because the rule is looking down the list A-Z.  Therefore if the thing it's lookiong for is near the top it will find that record quickly and then complete it's task.  However something near the bottom must wait a lot longer.  This made me want to try to find a way to just have the data query somehow bring back only the record I'm looking for - almost like instead of bringing it all back and looking through it, can't I just get it to bring back the exact match using some sort of speedy query???

It just doesn't seem to make sense to me that I'm trapped with this horrendous wait for something so simple as to auto populate a form!  I've tried and tried to change my Rule and use things like "Query fields" instead of "Data fields" hoping they would make it query for the correct data rather than bring it all back but my inexperience is completely leaving me out in the cold on it now!

I'm hoping someone can help me realise where I'm going wrong here - it has to be me being silly somewhere and doing something the hard way!

Thanks in advance for any help or advice at all..!!!!

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!

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)?

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

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()]

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.

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.

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

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

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.

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!

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.

 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! 

 

 

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.

 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