locked
InfoPath 2007 SQL Lookup Help RRS feed

  • Question

  • Hi Everyone - 

     

       I've been working on this for several days now and based on the searches I've done I'm not sure that it can be done.  

    My problem:  I am creating an InfoPath 2007 form that allows a user to enter a large number of items and then I want to search the database for a corresponding value based on each of these numbers.  

    Options that I've tried:

    1. SharePoint list.  Didn't work because if I provide a plain text box to enter values I couldn't access them line by line
    2. InfoPath 2007 form.  Still working on this but I'm not sure what the best way to go about it is.  I want to use a text box to allow the user to enter a large number of items (by pasting) and then a Listbox to display the information that I retrieve from SQL, but I am unable to figure out how to go about this.  I also attempted to use a repeating table, but the problem with this is I cannot paste in more values than there are current rows showing.  So, if I had to paste in 100 values, I would have to click on the 'repeat table row' 99 times before I could paste in all of my values.  Even if I attempt to do it with one value I'm still not sure how I can get the SQL query to use the field with the entered data to return the correct data.  

    Please let me know if you need clarification as I'm sure that this may not sound as clear as I think it does.  

    cliff notes version:  How can I allow the user to enter a large amount of data and then perform a SQL lookup based on each entry all within InfoPath 2007.  

     

    Any help is very  much appreciated as I am going crazy trying to figure this out. 


    Thanks, 


    Trent
    Tuesday, July 5, 2011 8:56 PM

Answers

  • You're probably dealing with the annoying whitespace issue.  A lot of times there's whitespace in the SQL value.  So, let's say the model number the user enters is:

    "A1234"

    but in SQL it's actually:

    "A1234    "

    it will not be able to find it - it wants to include the white space.  Soo, the easiest thing to do is modify your sql query around the model number to trim the space:

    select RTRIM(modelNo) as modelNo from dbo.models

    Also, for your modelNo field in the form's datasource where the user enters the model number, you need to make sure they don't enter spaces on the end by accident.  So for the default value of the modelNo field use the normalize-space() function.  Within the parenthesis insert a field or group - and select the current field.

    Wednesday, July 6, 2011 5:58 PM

All replies

  • There really isn't a good way to do that.  It sounds like you've fallen into a case where you're trying to please your end-users just a tad too much (yes, I'm guilty of that as well quite often).

    The only way I could think of to accomplish that would be using code.  Say these numbers they are pasting are each separated by a space.  You would need to programmitically pick the blob pasted in apart by looking for the spaces, then programatically create a new row in a repeating table and then set the values of the fields in the table.

    Also, just fyi, to insert more than 1 row at a time, say you have 10 rows already in your table... highlight all the 10 rows then click insert - it will insert 10 more rows.

    Wednesday, July 6, 2011 4:15 PM
  • Hi Melli - 

     

       Thanks for the advice; you  may be correct.  I'd be ok with allowing the user to enter a block of text and then using the workflow to submit it to me, I run a SQL query to retrieve the data, then provide that to the workflow to move to the next step.  I guess that could be accomplished with some form of text box?  

     

    Thanks, 

     

    Trent


    Trent
    Wednesday, July 6, 2011 4:34 PM
  • If you're really o.k. with committing to that, then yes you would use a multi-line text box (a text box with its properties set to multi-line)

    BUT you may really want to think that through prior to committing to it.  If there are going to be a lot of these requests I would definitely not commit to that.  If it's a once in a blue moon thing, sure. Also, what if you're out of the office and a rush request comes in - the requestor is going to go to your manager, their manager, everyone they can think of to track you down.  That's a lot of responsibility and extra work for you...

    You don't need to do all the work for the end-users, anything better than the current process will do.  For instance, if they currently have to look through a list for each item and get the information and hand write it then having a form where they have to enter each number individually on a line, but it populates the information for them - they'll be happy with that.  I too had this come up at one point and they were ok with having to insert the rows, then copy the information in.  They were ecstatic to just get out of the paper mill. 

    So, in summary I'm really trying to push you to go with the have the users enter their own ID's on each individual line in a repeating table.  You can have a sql data connection that queries that ID and have rules to set the remaining fields based off that ID. 

    Wednesday, July 6, 2011 5:47 PM
  • I agree, I would prefer not to do anything manual at this point and would much prefer having the user enter their own information (even if it is line by line).  Unfortunately I cannot get the database connection to work appropriately so even that is going to be a problem at the moment.  

    Right now, the problem I'm having is the SQL database is setup correctly within InfoPath, I have edited my SQL statement to reflect the appropriate fields I want returned, but when I attempt to do a lookup it does not work.  

     

    I have the user enter a model number and then i retrieve another field from the record where model number = what was entered.  Having a really frustrating time trying to get any of this to work.  I have the filter set correctly (according to multiple information i've been able to verify online), but I just can't get the correct value to be displayed.

     

    Any advice?


    Trent
    Wednesday, July 6, 2011 5:53 PM
  • You're probably dealing with the annoying whitespace issue.  A lot of times there's whitespace in the SQL value.  So, let's say the model number the user enters is:

    "A1234"

    but in SQL it's actually:

    "A1234    "

    it will not be able to find it - it wants to include the white space.  Soo, the easiest thing to do is modify your sql query around the model number to trim the space:

    select RTRIM(modelNo) as modelNo from dbo.models

    Also, for your modelNo field in the form's datasource where the user enters the model number, you need to make sure they don't enter spaces on the end by accident.  So for the default value of the modelNo field use the normalize-space() function.  Within the parenthesis insert a field or group - and select the current field.

    Wednesday, July 6, 2011 5:58 PM