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

Comments

14 comments

  • 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

    0
    Comment actions Permalink
  • Avatar
    MikeM

    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!

    0
    Comment actions Permalink
  • 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.

    0
    Comment actions Permalink
  • 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.

    0
    Comment actions Permalink
  • Avatar
    MikeM

    Ah Ha! now I am on the right track.  

    I totally missed the middle part of your index statements.

    ${/pd:AP/pd:processFields/pd:LoopVariables/...

     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 :) 

    0
    Comment actions Permalink
  • Avatar
    MikeM

    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.

    0
    Comment actions Permalink
  • Avatar
    Nik

    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. 

    0
    Comment actions Permalink
  • Avatar
    MikeM

    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. 

    0
    Comment actions Permalink
  • 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.

    Thanks!

    1
    Comment actions Permalink
  • Avatar
    Jean-Sébastien Renaud

    Hello Loren, 

    I hope you are still following this post too! ;)
    Have you found a solution for your issue? I have to do exactly the same as you (File Upload for every subform item).

    Thanks !

     

    0
    Comment actions Permalink
  • Avatar
    Loren Bratzler

    Jean-S,

    I did actually find a solution for this.  It may take me a few days to dig it up and then refresh myself on how it works!

    Loren

    0
    Comment actions Permalink
  • Avatar
    Jean-Sébastien Renaud

    Oh great!


    I will continue digging on my side also but if at some point you remember what you did and you don't mind giving me some tips, it would be greatly appreciated!

    Thank you and have a nice day.

    0
    Comment actions Permalink
  • Avatar
    Loren Bratzler

    So here is how I ended up doing this. It's actually a pretty complicated / ugly process so I will try to explain as best I can.

    I did get help from AgilePoint as they provided me with the JavaScript function.

    The first step was to add a hidden field to the Subform to store the ids of the files that are being attached.  This field will hold the full file path of each file that is added to that occurrence of the subform.

    It's kind of hard to see in this screen-shot of the full subform definition.

    But here is a closer view of the relevant fields:

    Note that all the attachments in all rows of the Subform are uploaded to the same repository folder.  

    Next, we have a JavaScript function for setting the File IDs in the hidden field.  This function is executed by a "Before Submit" rule:

     

    The JS function (provided by AgilePoint) looks like this (sorry about the formatting - pasting code into this forum doesn't maintain indentions)

    function SetFileIDs() {
    var fileData = {};
    eFormHelper.getSubFormData({
    "fieldId": "SubsVendors"
    }, function(res) {

    if (res.isSuccess) {
    jQuery(res.data).each(function(i, d) {
    var itemIds = "";
    jQuery(d.SubPOFiles_MultiFile.SubPOFiles).each(function(ind, data) {
    itemIds = itemIds + data.itemId;
    });
    fileData["StoreFileIDs_" + i] = itemIds;
    });
    }
    });

    eFormHelper.getField({
    fieldId: "SubsVendors/StoreFileIDs:[*]"
    }, function(res) {
    if (res.isSuccess) {
    jQuery(res.data).each(function(i, d) {
    var controlId = jQuery(d).attr("id").split('-')[0] + "_" + i;

    eFormHelper.setFieldValue({
    fieldId: jQuery(d).attr("id"),
    value: fileData[controlId]
    },
    function(result) {

    });
    });
    }
    });
    }

    I will admit that I do not completely understand what is going on in this code. The gist of it is that it is looping through the subform rows and getting the itemId field from the File Upload control for each file uploaded on that row.  It then stores all the fileids for that row in the hidden field as one concatenated string.

    It might make more sense when you see the results of the following test case.

    In this test case, I have two rows on my Subform and I am attaching 3 documents to each row.  Of course the end user does not see the hidden field.

    When I submit the form, the "Before Submit" rule fires off and the JavaScript method is executed.  The JavaScript sets the value of the hidden field.  Here is the hidden field value after submitting for the first Subform row:

    and here is the value of the hidden field on the second subform row:

    So now we have a field in each subform row that has the complete file paths of all the files that were attached on that row.

    The next step is to decide what you want to do with the files.  In my case, I needed to move them to separate repositories, one for each subform row.  The only way to do this was with a nested loop.  The outer loop loops through the subform rows and the inner loop loops through the attachments.

    There is a bug in AgilePoint though where the inner loop does not just loop through the attachments on the current row of the subform (from the outer loop).  The inner loop always loops through all the attachments on all the subform rows, not just the current row.  Again this is hard to explain.  To get around that problem, I had to put a condition on the inner loop to check to see if the current fileID is contained in the hidden field that has the list of all files attached on that row.  If the current fileID is in the list, then I copy it to the repository for that row.  If it is not in the list, it is skipped.

    I will try to post again about the nested loop when I have some more time.

    I hope this helps and doesn't scare you away!!

    0
    Comment actions Permalink
  • Avatar
    Jean-Sébastien Renaud

    Wow!
    Thank you so much Loren! You are right, pretty complicated solution... but actually I was at a point were I was thinking using the exact same technique. Unfortunately, my javascript knowledge is not that good and I was about to ask someone else to help me do that part. It will probably save me a lot of time!
    Thank you also for the heads up for the loops!

    Good thing that this solution is now available in the community. I'm pretty sure it will be useful for other people in the future!

    0
    Comment actions Permalink

Please sign in to leave a comment.