Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted. - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

Last post 02-08-2013 04:21 PM by Hilary Stoupa. 13 replies.
Page 1 of 1 (14 items)
Sort Posts: Previous Next
  • 02-06-2013 05:56 PM

    Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    I have a Form that is submitting shipments to a Sharepoint List (Fields: SKU, Description, Shipped, Quanity, ShippingID, Received).  What I want to be able to do is open up a different Infopath form that has a text box or dropdown where the user can input or select a ShippingID.  Once this ShippingID is selected, I want the a repeating table to be populated from the Sharepoint list that is filtered based on ShippingID.  There may be dozens of items that match this filter.  I have done this with Filter Owssvr command.  The problem comes in where I want the user to now update a field called "received" that is in the same list for each paticular item in the repeating table that shows up in the filter.    It is being used to track inventory.

    What is the best approach for this?  I have followed tutorials where I can create Sharepoint list items in a form that stay synced with the list when they are changed, BUT I am populating the list manually.  The goal is to make it so information to auto populate for different people to edit and different forms.

     Thank you in advance!!

  • 02-06-2013 10:10 PM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    Well after hours of research, I have it working. BUT some of my fields from the source table or blank which is preventing me from copying to the destination.  So I need one of the following:

    1)  a way to copy the whole table even if their are blank fiends
    2)  a way to insert a button on each individual row that will let me populate a source row from a repeating table to a destination row in a repeating table. The order of the rows do not matter as long as there are no completely empty rows

    I also MIGHT be putting this in a repeating section as well.

    Again, my goal is to just bring in a filtered sharepoint list based on a text box value, and then to be able to edit fields in that returned list that will update Sharepoint.  If I can do that then I may not even need to bother with copying of tables. 

     Any help would be greatly appreciated.

  • 02-07-2013 04:39 AM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    Hi-

    I don't believe FilterOwssvr is needed in this scenario. Since you're using InfoPath 2010 and ShP 2010, you can just set a queryfield (as a filtering parameter) then execute the secondary data connection the list. The returned data will be already filtered.

    You can copy the secondary data source repeating data into the main data source by using the CopyTable command, if the data sources match.

    If they don't match, you can use Insert and SetValue to accomplish the data transfer. More on this in this tutorial:

    http://www.infopathdev.com/files/folders/qrules/entry58577.aspx

    Let us know whether this helps!

    Ernesto Machado
    Qdabra® Software/ InfoPathDev.com
    The InfoPath Experts – Streamline data gathering to turn process into knowledge.™


  • 02-07-2013 08:01 AM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    I am not quite sure how to set a queryfield as a filtering parameter.  Everything I have read says you can only filter Sharepoint lists in a repeating table by using either conditional formatting to hide rows or to use FilterOwssvr.  I want users to type in the ShippingID in a text field and then query the Sharepoint database to a repeating table which will return numerous rows.

    I then need to either update OR add each row using the following method: http://www.infopathdev.com/blogs/hilary/archive/2012/07/05/qrules-refreshsharepointlistitems-walkthrough-for-v4-2.aspx

    I am open to any solution that performs the above, even if it includes clicking buttons in each row of the repeating table.  I tried the copytables but since the number of fields didn't match and some of the fields had no values yet, it was not working very well.

    Thanks for the help so far!

  • 02-07-2013 09:33 AM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    Hi-

    Hilary discusses using the queryFields as a filter in this blog post:

    http://www.infopathdev.com/blogs/hilary/archive/2012/06/27/qrules-submittosharepointlist-walkthrough-for-v4-2-working-with-existing-data-amp-submitting-a-single-mapping.aspx

    CopyTable will only work when the secondary data source schema matches the main data source schema. That's by far the easiest route to accomplish this (and you could modify the main data source to achieve this uniformity).

    However, sometimes we're limited in the modifications we can make to the form and can't achieve this. Thus we offer an alternative, which is to use Insert and SetValue to bring the data from the secondary data source to the main data source:

    http://www.infopathdev.com/files/folders/qrules/entry58577.aspx

    Once the data is in the main data source, it can be modified as needed and the mapped back to the list using the SubmitToSharePointList command.

    I hope the links above are useful, but we do offer one-on-one assistance. For more info on that, you can check out this page:

    http://www.qdabra.com/webstore/p/43/pay-as-you-go-support

    Thanks!

    Ernesto Machado
    Qdabra® Software/ InfoPathDev.com
    The InfoPath Experts – Streamline data gathering to turn process into knowledge.™


  • 02-07-2013 04:12 PM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    Slowly but surely I am getting there.  I appreciate all the efforts.  I am close to having it working using the Insert Command.  The biggest difference in the tutorial is I have a repeating table in a repeating section but the tutorial just uses a repeating section.  For me, all my actions get performed in one section at a time so I dont think that is an issue.  Here is my problem and hopefully you can give me some help.

    My Copy List button does work, BUT when the list copies, I can see it delete all my rows in the target list, but it does not populate down.  If I manually add rows, the first row is blank but all the other rows have the correct information.  So the copy portion is working just not the row portion.  Here is my layout.

    myFields
      Shippings
         Shipping (Repeating Section)
            Trucks
                Truck (Repeating Table)
                    field1
                    field2, etc

    I have the following actions on a button:

    Delete /xpath=/my:myFields/my:Shippings/my:Shipping/my:Trucks
    concat("Insert /parent=/my:myFields/my:Shippings/my:Shipping child=my:Trucks /count=", Count)

    If I run my Sharepoint list query, the count field returns "2" if 2 rows are queried so it seems the Count field is correctly pulling the right numbers.

    I do greatly appreciate the help.

    Thank you.

  • 02-07-2013 07:03 PM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    Oops.  I had a typo and forgot the"/" before child! Now I need to work on getting the info duplicated into the right section.  Should I be able to use the preceding-sibling command for that?

  • 02-08-2013 07:05 AM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    Hi - my apologies, I'm having a really hard time following this thread - but I think since Shipping is repeating you'll need to use /xpathsrc instead of /child so that you can have filter on /parent to get the Insert to occur on the correct Shipping group.
    Hilary Stoupa

  • 02-08-2013 08:51 AM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    Here is the form I am working with if that helps http://www.ctscomp.com/Podis-Beta.xsn

    I basically followed this article: http://www.infopathdev.com/blogs/hilary/archive/2012/07/05/qrules-refreshsharepointlistitems-walkthrough-for-v4-2.aspx

    I also followed this article: http://www.infopathdev.com/files/folders/qrules/entry58577.aspx

    Both are in the form, now I just need to integrate the two together. The biggest difference is I am doing repeating tables in a repeating section.  I would like users to type in a search string that will query Sharepoint list.  I dragged the d:SharePointList_RW onto the form as a repeating table.  I created a Copy Button that copies the results to the repeating table in the repeating section.  I would like it to always copy to the repeating table in the last section.

     I tried to follow this thread to at least start off with the deletion and insertion of the rows in the right section http://www.infopathdev.com/forums/t/20593.aspx?PageIndex=1

    I tried the following concat("Insert /parent=/my:myFields/my:Shippings/my:Shipping[", count(preceding-sibling::Shipping) + 1, "]/my:Trucks /xpathsrc=/my:myFields/my:Shippings/my:Shipping/my:Trucks/my:Truck) /count=", Count)

    To at least get the rows inserted but it doesn't work.  Hopefully this explains a little better.

  • 02-08-2013 09:02 AM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    So - what does "doesn't work" mean here? Rows not inserted at all? Rows inserted in the wrong place? Can you put the QdabraRules error node on your form and see if you get an error back? I'll take a quick look at your form.

    Hilary Stoupa

  • 02-08-2013 09:05 AM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    Hi-

    As you're developing the form, you'll want to add the QdabraRules > Error node into the canvas, to get an idea of why the command is failing.

    I believe your parent xpath is actually:

    /my:myFields/my:Shippings/my:Shipping[index]/my:Trucks

    Anyway, the Error should tell us more. Let us know what you find.

    Thanks.

    Ernesto Machado
    Qdabra® Software/ InfoPathDev.com
    The InfoPath Experts – Streamline data gathering to turn process into knowledge.™


  • 02-08-2013 09:16 AM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    Okay - so, for your Delete command, you'll want to use a concat statement - since you have nested repeating data, your Delete command would actually delete all instances of Truck. Try this instead:

    concat("Delete /xpath=/my:myFields/my:Shippings/my:Shipping[", count(preceding-sibling::my:Shipping) + 1, "]/my:Trucks/my:Truck")

    The Insert statement in the form you posted doesn't seem to match what you have above? But this:

    concat("Insert /parent=/my:myFields/my:Shippings/my:Shipping[", count(preceding-sibling::my:Shipping) + 1, "]/my:Trucks /xpathsrc=/my:myFields/my:Shippings/my:Shipping/my:Trucks/my:Truck /count=", xdXDocument:GetDOM("ViewOptions")/Util/fields/Count)

    Worked for me....

    Hilary Stoupa

  • 02-08-2013 04:14 PM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    Sorry, I had tried sooooo many different combinations I may have sent over the wrong form with a different delete statement. 

    Both are working perfectly now.  Adding the error field helped out a lot too.  I realized that my count was not a positive integer because i wasn't running my query again, duh!  BUT I was missing the "my:" in the insert command. You guys are amazing, thank you!!

    I am a computer guy myself so by saying "It is not working", really doesn't relay much information. 

    Again thank you to both Hilary and Ernesto!

    So far I love my trial to QRules and will be purchasing soon!

  • 02-08-2013 04:21 PM In reply to

    Re: Query Sharepoint List using text box as filter. Return results to a repeating table that can be resubmitted.

    We are happy to help and so glad you are loving qRules. I'm glad you have everything working the way you want!
    Hilary Stoupa

Page 1 of 1 (14 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.