Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


setting up an infopath form to behave like a VLOOKUP

Posted on 2009-07-13
Medium Priority
Last Modified: 2012-06-21
we have an excel template in place where it does a VLOOKUP on a separate workbook and fills out the remainding columns of the row with product data.  I want to replace this excel template with an infopath form (2007), and store it in a sharepoint doc library, however my initial tests have been either confusing or the performance to lookup data from a SQL data source from InfoPath are really slow... like unusably slow.

so I found this on the web, kind of walks through how to do this

The problems when trying to implement this in a test form are:

1. It uses a 'drop down' list, whereas I just want to have a text field that users can input a product number
2. Performance for the number of product records we have is really bad, takes 2-3 minutes and freezes the form, when filling out...

I'm just kind of shocked at how bad the performance was, so I assume either it's just bad filtering on my part, or it will just never be as fast as the excel vlookup function?

I saw somewhat similar posts on EE, and I bought a big thick book on it, but I am just wondering what other people have done in similar circumstances?
Question by:rmicone
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 24844940
so... i'm not going to be of much use on the Infopath question you have.

however, i want to propose a potential alternative...  i'm not entirely sure if it will meet all of your requirements because you may have some requirements you haven't mentioned.

but... it sounds to me like you may be able to accomplish your goal using the Data View Web part (actually, Data Form web part... since you'll want to have the ability to edit entries).  data view web part is available in Sharepoint Designer (free tool)

i'm not exactly sure how you have your data stored.  it sounds like it's in SQL tables now?  or is it still in Excel?

the basic concept is to make SQL do the work, and then use data view web part to provide an interface for users to edit records in the SQL table.  you can also add a search functionality so that users can "filter" the data view.

here is info on how to create the data view web part:

if you want to add a search button:

Author Comment

ID: 24845007
thanks for the comment, actually I did use sp designer + data forms to create the current application that we're using, (it's a sales order type shopping cart deal), which stores the data in SP lists... The only problem here is that the users say it takes too long to do a shopping cart style search and add to the form I've setup... and so yeah I am looking for the alternate approach using Excel or InfoPath and using workflows instead... here's the setup I'm looking to accomplish

1. User goes to a doc library on SP site and clicks New -> "Sales Order" , which is a custom content .xlsx template I've uploaded
2. User fills out a few lines of info relating to the client
3. User will enter a product ID (which they know) and hit tab to have it fill in the name, color, style, ... user enters QTY
4. User saves it to the doc library which starts the workflow

... the rest I'm sorting out, but my real wish is that infopath can replace excel for this

InfoPath looks like it could do this, with all the great controls and repeating sections to drag and drop but... yeah having some problems getting it to work.  The xpath filtering is really not intuitive to me yet.  It seems like this would be a very common thing in InfoPath, but like I said the performance indicates maybe I should just work on customizing the excel form using vlookup... I'm sure we all know that if it takes more than a few seconds, the end users declare it unusable...


Accepted Solution

rmicone earned 0 total points
ID: 25120326
I pretty much sorted this out on my own, I will post it for posterity I guess...

I ended up using InfoPath anyways, but did a few things:
1. Set the data source to 'store a copy of the data in the form'
2. Set the data source to *not* refresh when the form opens
3. Put a Refresh button at the bottom of the form for people to validate/lookup all their data when they were done inputting the required fields.

It is looking  up a lot of data, so I still got complaints of "this takes too long" but it's kind of more of a PR issue the end users as waiting 7 seconds for accurate data will save minutes to maybe even hours down the road...

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question