Submit one to many to database, secondary source needed as lookup - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Submit one to many to database, secondary source needed as lookup

Last post 07-17-2019 02:14 PM by Hilary Stoupa. 9 replies.
Page 1 of 1 (10 items)
Sort Posts: Previous Next
  • 07-11-2019 01:31 PM

    Submit one to many to database, secondary source needed as lookup

    Hi folks,

    I'm pretty sure what I'm wanting to do can't be accomplished out of the box but I would love it if I'm just missing something simple.

    Scenario:
    I have 3 objects I'm working with. Object type A is a parent with a one to many relationship to object type B. Object type C is a lookup table for a property of B.

    Goal:
    -Provide a list of "rows" for each C
    -For each "row" provide repeatable table of B
    -Submit 1 A with any number of B using the ID field from each B's equivalent C.

    If this isn't clear I can try and come up with a mock up form that demonstrates.

    I have a feeling code is going to be required. We tend to avoid deploying InfoPath forms with code behind the form itself however I've done a few small ones using web services. I'm just not sure how I would approach the web service idea for this set up.

    Thanks!
  • 07-11-2019 01:44 PM In reply to

    Re: Submit one to many to database, secondary source needed as lookup

    Yeah, this would have to be a database bound form or you would need a web service - the company I work for has one, DBXL, that does this, and you pass in the XML as a string and there is a mapping that parses the XML to the DB.
    Hilary Stoupa

  • 07-11-2019 01:47 PM In reply to

    Re: Submit one to many to database, secondary source needed as lookup

    Any thoughts as to how it would be done as a database bound form? It's already bound to the database. All of the data is in the same database.
  • 07-11-2019 01:52 PM In reply to

    Re: Submit one to many to database, secondary source needed as lookup

    So you created a form bound to the database? Does it not submit correctly?
    Hilary Stoupa

  • 07-11-2019 01:59 PM In reply to

    Re: Submit one to many to database, secondary source needed as lookup

    It does. Where I'm struggling is how to have all of the records from C represented and allow for distinct B objects to be created for each of them.
  • 07-11-2019 02:11 PM In reply to

    Re: Submit one to many to database, secondary source needed as lookup

    I'm going to need a sample, or some screenshots - I just read through the description again, and I'm having trouble understanding the DB structure you want in the form. You can always attach your form to a reply and I can delete it after I download it (if you are worried about the DB connection info in the form).
    Hilary Stoupa

  • 07-15-2019 08:31 AM In reply to

    Re: Submit one to many to database, secondary source needed as lookup

    Hi Hilary,

    Thanks for taking the time to help. I've attached a mock up form with a db structure similar to what I'm working with. It's very bare bones as I'm trying to just focus on this one aspect of the form.

    When looking at the structure keep in mind the following:

    -For each entry in ReviewCategory show a "row" on the form with that Category's description.
    -For each "row" of the category offer a repeating table type of control that allows users to add a ReviewFinding.
    -The submit will submit one Review with many Findings.

    -Primary Keys are handled separately and by external logic are guaranteed to be unique.

    Like I said initially, I'm pretty sure it's not possible via OOB controls. The best I can come up with as a no code solution is to give the user a drop down of categories and have them insert findings via a repeating table. The problem with that set up is it doesn't force the user to see every possible category when they do a "Review" (like I said, this is just a mock up).
  • 07-16-2019 08:31 AM In reply to

    Re: Submit one to many to database, secondary source needed as lookup

    Oh, wow, I don't think this is going to be possible as described - I don't think you can nest a repeating section from another data source inside another one. So - maybe there would be a way to add a button to the repeating table that shows the review section & sets the Category ID - have to be careful with that if there are multiples. Is the net result that you need users to open a form, fill out a review for every single category and then submit? That is, you are collecting Reviews for a number of categories for every user?
    Hilary Stoupa

  • 07-17-2019 01:53 PM In reply to

    Re: Submit one to many to database, secondary source needed as lookup

    Yes, that's correct. The user would fill out a review for each category and each category can have multiple reviews.

    I ended up implementing a solution in which I show them how many categories are allowed for the type of review they are doing and make sure they do each of them. I've got a repeating table that holds reviews and a drop down in each row that allows them to select the category.

    Further, I've ensured that they can't select the same category multiple times. For each review I have a nested repeating table that allows them to enter their multiple reviews for each category.

    Ultimately, I wanted to be able to display all of the categories without forcing the user to select each one but I've implemented enough checks and balances to make this way work.

    Thanks for all of your time and effort. I end up looking at your posts quite often when I'm in InfoPath land lol. I can't begin to imagine how many people and their numerous google searches you've helped over the years!
  • 07-17-2019 02:14 PM In reply to

    Re: Submit one to many to database, secondary source needed as lookup

    It sounds like you have a good implementation that meets your end goal - working around the limitations of InfoPath. :)
    Hilary Stoupa

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