Saving Content of a nested sub form to a Database. How do you get values of the second sub form for 2nd loop?



  • Avatar
    Karl Makatenas

    I did this for travel authorizations about a year and a half back, so hopefully it will give you inspiration to find a better way.  I had a whole bunch of static subforms (user individual data) within a repeating subform (all the people traveling), so it required a loop within a loop.

    Activity Name: "Traveler = 0"
    nthTravelerInForm = 0

    Conditional for outer loop
    IsProfileLoopDone == true

    Activity Name: "Traveler Increment"
    nthTravelerInForm = Int32.Parse(${/pd:AP/pd:processFields/pd:LoopVariables/pd:nthTravelerInForm}.ToString())+1

    Activity Name: "Index = 0"
    CurrentTravelerNum = 0

    Conditional for inner loop
    nthTravelerInForm != CurrentTravelerNum

    Activity Name: "Index increment"
    CurrentTravelerNum = Int32.Parse(${/pd:AP/pd:processFields/pd:LoopVariables/pd:CurrentTravelerNum}.ToString())+1

  • Avatar

    Ah yes, I see what you are doing.  


    Unfortunately my nested subform is dynamic, so my problem is trying to get it to iterate on the nested subform 3 times, if it has 3 entries. 

    I am trying to figure out what combination will work for assigning the temp variables over so it will do the second loop properly. 


    It ends up all lumped as a concat string.  


    Model Data is set up as follows:

    Nested Form is set up as follows:

    1st Loop Set up

    2nd Loop

    I am wondering If I am just missing the magical reassignment method to get everything to copy to the temp  variables properly. 


    Thanks so much!

  • Avatar
    Karl Makatenas

    You can remove the bottom two rows from the first loop configuration (Tasks and Tasks_SF).  They're unnecessary.

    Also, if your model data needs to simultaneously house all user data, I would consider changing your architecture so that the user data is housed somewhere other than within AgilePoint's process model schema (because I have no idea how to even begin doing that :P).  If you don't need all user data within the model data, then raise the model data hierarchy by pulling out Task, Duration, and Project from the group(s) that they're in and/or remove the group(s) ability to repeat.

    Say you set it up and use a test case with the following data:
    User 1: Tasks ABC
    User 2: Tasks DEFG
    User 3: Tasks HIJKL
    If you get Tasks ABC G L, then you'll need to use indexes like in my example because the inner loop isn't resetting.
    If you get Tasks ABC ABC ABC or ABC DEF HIJ, then you'll also need to use indexes like in my example because the outer loop is progressing differently than the inner loop.

    When it's all done, you should be looking at the values of tmpProject, tmpDuration, and tmpTask during each iteration to have the correct values, not Tasks_SF.  For debugging, I usually have a wait timer set to one day and place this wait timer immediately after the "next iteration" activity in the inner loop, then I use AgilePoint's Runtime Manager to inspect the schema for the values of that iteration; when done inspecting, I'll manually bypass the wait timer to proceed with the next iteration.

    Hopefully that can point you in the right direction.

  • Avatar
    Karl Makatenas

    Also, it's unclear whether you are using the Tasks_SF Model Data or the Tasks_Subform Form Data within the second loop.  Let me clarify that you should be using the Tasks_Subform Form Data there.

  • Avatar

    Ah Ha! now I am on the right track.  

    I totally missed the middle part of your index statements.


     Are you addressing the systems index for the loop or your only variable nested under a complex type?


    Thanks again. I most certainly owe you a coffee :) 

  • Avatar

    In Retrospect, I was thinking of the problem wrong. 


    I added a unique identifier from the first subform, to the nested subform with rules.  (In this case Date/Username).


    Now I do not need to do nested loops to save all the data.  


    Just loop on the first sub form saving each entry, then loop on the nested subform saving each entry. All Data is Saved and the interface makes sense to the user.

    Didn't realize / try to pull the nested subfrom out into it's own loop until you gave me the idea Karl.  Thanks again.

  • Avatar

    Here is what we do:

    Data Population to SQL works at the parent level and the first subform level. (It will not work with nested subforms).

    Nested subforms are stored to SQL by 'Batch Insert SQL ' and 'Batch Update SQL'

    Make sure to create a unique ID for each subform row and then store it within each nested subform row for reference later. 

  • Avatar

    Thanks Nik,

    For historic purposes:

    The final implementation was to treat the sub form as its own form while saving. 

    To do this we added a hidden element to the sub form and had a rule to copy the unique ID from the parents row to the sub form, this way all the data necessary to save the sub form is in the sub form. 

  • Avatar
    Loren Bratzler

    I am hoping that Karl or Mike (or anybody!) is still following this post.

    I find myself with a similar situation where I have a Sub-Form that has a File Upload control in it.  We need to be able to have the users add rows to the sub-form and then optionally attach files to each row.  The files that they attach to each row need to be kept separate from files that are attached on other rows of the sub-form.

    My first thought was that I would try to create a separate file repository for each row of the sub-form by using a hidden random value on the sub-form in the file repository configuration.  That did not work because all the files ended up going to the repository for the first row of the sub-form.

    So then I started thinking that I would need to use some kind of nested loop to separate the files.  That was when I found this post and saw Karl's approach of using indexes to determine if the current iteration of the inner loop matched the iteration of the outer loop.  But as in Mike's case, with his child sub-form, that won't work because the number of files that can be attached is variable.

    I then studied Mike's eventual solution where he defined a hidden field on his child sub-form that would tell him which parent row the child belonged to.  But I can't use that approach either because my "child" is a file upload control and I cannot modify the data elements of the repeating schema behind the upload control.

    So I am beginning to wonder if this can even be done or not?  I welcome any thoughts or suggestions on how to make this work.


Please sign in to leave a comment.