cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
nj
Nintex Newbie

Query a list for multiple items, each item with repeater

Jump to solution

Let's see if I can even explain myself correctly. I'm trying to query a list, returning multiple items. Each item has a repeater with multiple details.

Item has:

Building

City

Repeater

Created Date

Repeater fields are:

      Person

      Number

Since I have multiple items I'm returning, I need to call web service. I'm able to build an HTML table with the returned information that is stored in the Results variable (After using XSL transform option). 

The problem is each row becomes:

Building | City | Repeater XML with everything about all people entered in repeater | Created Date

I need to be able to build an HTML table consisting of

Repeater Person | Repeater Person Number | Building | City  | Created Date

Next Repeater Person | Repeater Person Number | Same Building | Same City  | Same Created Date

What actions would I even use to keep track correctly? It seems like I would benefit from using a collection but the returned XML can only be stored in a single variable. 

Labels: (1)
Reply
24 Replies
borrajaya
Nintex Newbie

Re: Query a list for multiple items, each item with repeater

Jump to solution

Hi N J,

are you making use of Nintex workflow here ?

what is your object exactly ? if you are having a form that has the mentioned repeating fields concept and trying to transform data into another list with the way you are thinking to parse, the Nintex workflow that I uploaded here

Nintex Forms Repeating Control two way sync with child list

 might come in handy. in the blog I was doing two way sync thus using two workflows, if you need only one way you can stop with single workflow. to achieve that building information appear along with each repeating row, you can add list look up fields while creating parent item look up field in the child list.

refer to below screenshots for more details

Let me know if this helps or your requirement is something different.

Reply
nj
Nintex Newbie

Re: Query a list for multiple items, each item with repeater

Jump to solution

It looks like this is something down the same road of what I'm trying to achieve. I'll be more specific though. 

I've got two lists. One has lots of fields, I'll call it the main list. Then I made another list to run queries on the target list for reporting purposes, I'll call it the report list. The report list has a web service call that returns the below information:

[XML] <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi= - Pastebin.co... 

this is one of 29 returned items from the main list for this specific web service call. I've removed any identifying information. I'm not even sure I'll be able to use this as it looks like everything in the repeater(ows_multiRepeaterDetails) is broken.

Here's a screenshot showing the same item the above XML is regarding. This is with only resident. Each item could have 20 residents.

The output should be a table showing 

ResidentResident NumberMain List Item IDCenterRegionCreated DateMonth ServiceAR TypeMedicare Bad Debt Amount
Person 1Number1212Mississippi2017-10-03 08:08:49JanuaryDEN2200.00
Person 2Number2212Mississippi2017-09-13 08:08:49MarchQWE3400.29
Person 3Number3212Mississippi2017-09-13 08:08:49MarchPOP3400.29
Person 4Number4212Mississippi2017-09-13 08:08:49MarchEWQ3400.29

In this example, you see that Person 3 and Person 4 come from separate items from the main list entirely. 

I have no issue creating the table with the fields that are not inside the repeater. 

Nintex staff: I can't post the returned XML because I keep getting an error:

best I can do is post a pastebin: [XML] <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi= - Pastebin.co... 

0 Kudos
Reply
emha
Nintex Newbie

Re: Query a list for multiple items, each item with repeater

Jump to solution

Since I have multiple items I'm returning, I need to call web service.

you need not to complicate things with web service, query list action can return multiple items as well.

just make sure you store result sets into collection variables.

I need to be able to build an HTML table consisting of...

it looks like you do not have problems to make a table out of scalar fields.

so here is a possible way to do the same with repeating section

- regardless of how do you get it, either with webservice call or query list action, do following with each item's repeating section field

- with query XML action store each repeating section field/column into a collection variable. you can do that with single query XML action which's set up might look like this

- then make a for each loop over one of these collections.

save loop index into a variable.

- use collection operation action(s) to pick values from collections populated by above mentioned query list action. 

index variable gives you pointer to all the values of a single repeating section row across all the collections.

store the values into scalar workflow variables.

- now you can use these workflow variables to build related part of output table

Reply
borrajaya
Nintex Newbie

Re: Query a list for multiple items, each item with repeater

Jump to solution

As Marian Hatala Moderator mentioned, do not complicate things. you can query list items and use Query xml action to retrieve all your child items and insert them to your child/reporting list along with primary list field data to your reporting list columns.

0 Kudos
Reply
nj
Nintex Newbie

Re: Query a list for multiple items, each item with repeater

Jump to solution

I have no idea why I thought I couldn't do this with a Query List action.

OK, so I have variable collections set up for Center, Region, Created, Month of Service, AR Type, Amount, and Repeater.

Then, I created collections for Resident Name and Resident Number; columns that exist inside the repeater.

So you're saying to run a query XML against the collection for the Repeater, use XPath to grab the specific values that are useful to me, store them in their collections (Resident Name and Resident Number), and then when it comes time to output them to a notification email or however I'm going to do it, add in the information from the non-repeater values. 

I guess the only part I'm not sure how to do, is maintaining the relationship between the repeating section details and the details outside the repeater. They don't exist 1 for 1, as some repeaters will have 10 entries for 1 item.

What specific actions should I use to maintain the relationship between the values inside the repeater and the details outside the repeater, that it exists on? Here's an example of what I'm afraid will happen.

Item ID 1 is for Region MS. It has 2 Residents. Mark and Marcell.

Item ID 2 is for Region KY. It has 3 Residents. Ken, Kyle, and Kirsten.

When I start iterating through the Residents, what am I using to keep track that Ken, Kyle, and Kirsten don't wind up being linked to MS incorrectly?

I feel like I'm just missing one piece of this and I'm not sure what.

0 Kudos
Reply
emha
Nintex Newbie

Re: Query a list for multiple items, each item with repeater

Jump to solution

the trick is in this paragraph

- then make a for each loop over one of these collections.

save loop index into a variable.

you will need two nested for each loops in your design - one for items returned by (eg) query list action and second to iterate through repeater rows returned from query XML action.

in both for each loop actions you have to save index value to a variable.

then index value from outer loop will identify item field values across all the collection - so, Region[idxItem], Center[idxItem], Repeater[idxItem], etc will all be values of the same item

index value from inner loop (for a given Repeater[idxItem]) similarly will identify values across all the collection that store repeater data, eg ResidentName[idxRsRow], ResidentNumber[idxRsRow] etc.

note the construct Collection[idx] is just to explain it easily. to pick single element out of collection you will need to use collection operation.

hope it clears it out.

Reply
nj
Nintex Newbie

Re: Query a list for multiple items, each item with repeater

Jump to solution

Well, I feel closer. I'm obviously not thinking about this the right way or something. Let's assume I want to just attach the outer values to each inner(Repeater section) value and then send that information in a Notification. 

I've got a For Each(OuterLoop) cycling through varCollCreated(the Created field of the returned items) which stores the result in tmpOuterLoop and indexes to idxOuterLoop, after that is doing an XML Query to grab the Repeating section details on that same item to get the Repeating fields ResidentName and ResidentNumber. After that I have another For Each loop(InnerLoop) which stores result in tmpInnerLoop and indexes to idxInnerLoop.

Here is where I'm stuck. I thought that I would use something like Build String to create the string of "Resident Name - Resident Number - Center - Month of Service - Created - Region - Amount - AR Type" but if I tried that at this level, I would wind up with the whole collection of each of those, not the specific entry.

I'm sorry to keep on like this, but I still don't understand the last piece that would tie each InnerLoop value to the OuterLoop value. Maybe I don't understand how I'm supposed to be using the Indexes?

0 Kudos
Reply
borrajaya
Nintex Newbie

Re: Query a list for multiple items, each item with repeater

Jump to solution

because you already have your outer data in the list item properties you can directly use this kind of table construction inside your nintex workflow 

and sending this final table to your notification body.

somehow not able to switch this to advanced editor to be able upload the example workflow I built for your problem hence you got to manage with screenshots.

This is how the key steps of workflow would look like 

0 Kudos
Reply
emha
Nintex Newbie

Re: Query a list for multiple items, each item with repeater

Jump to solution

I think you're on right track

I'll assume you want to build up structure like this from your original question (slightly updated)

I need to be able to build an HTML table consisting of

 

A: Repeater Person | Repeater Person Number | Building | City  | Created Date

B:   Next Repeater Person1 | Repeater Person Number | Same Building | Same City  | Same Created Date

B:   Next Repeater Person 2| Repeater Person Number | Same Building | Same City  | Same Created Date

B:   Next Repeater Person 3| Repeater Person Number | Same Building | Same City  | Same Created Date

that means the line I labeled 'A' you have to 'create' (add to table string) within outer loop.

lines labeled 'B' you have to add within inner loop.

once you're at the end of outer loop send out a notification (if notification should be sent per each item) and clear table string variable to start over with new table (notification content) with new (outer) iteration

Reply