Filter and get values from json and put in new json array

[tkhcmark]

Hello,
First, I don't know what the process I am trying to accomplish is called so I am having a hard time searching for solutions without the proper search terms. Can someone tell what its called to I can read up on it.

I am updating a list in sharepoint and i want to only add one item entry per FullName and filter FullName so I get all values for TruckNum.

Input JSON


{
"FullName": "Joe's Trucking",
"TruckNum": "JOE108"
},
{
"FullName": "Joe's Trucking",
"TruckNum": "JOE106"
},
{
"FullName": "PEREZ TRUCKING COMPANY",
"TruckNum": "PRZ204"
},
{
"FullName": "PEREZ TRUCKING COMPANY",
"TruckNum": "PRZ05"
},
{
"FullName": "Tom's Cartage",
"TruckNum": "TOM08"
},
{
"FullName": "GEAR JAMMER TRANSPORT",
"TruckNum": "GJT229"
},
{
"FullName": "GEAR JAMMER TRANSPORT",
"TruckNum": "GJT313"
}
]

Into something like this. Not sure if "TruckNum": "TOM08"  is correct or missing []. Just using as an example.

"FullName": "Joe's Trucking",
"TruckNum": ["JOE108", "JOE106"]
},
{
"FullName": "PEREZ TRUCKING COMPANY",
"TruckNum": ["PRZ204", "PRZ05"]
},
{
"FullName": "Tom's Cartage",
"TruckNum": "TOM08"
},
{
"FullName": "GEAR JAMMER TRANSPORT",
"TruckNum": ["GJT229", "GJT313"]
}
]

This is my current flow witch creates a new item in sharepoint list for every object

[abm]

Hi @tkhcmark

Are you planning to update the  more than one TruckNum against one name or individual records?

"FullName": "Joe's Trucking",
		"TruckNum": ["JOE108", "JOE106"]

Looking the above data how you planning to update the TruckNum (Use both values) or create two records of Joes Trucking and update individually? Is this a text field? 

[tkhcmark]

@abm

I have a list of individual trucks “TruckNum” and I want to create a list of of companies “FirstName” and put all their trucks in the “TruckNum” column. “TruckNum” is unique value in the list I’m getting the data from. “FullName” is not unique and has multiple entries of the same value.

Each FullName will have 1 record and the TruckNum for each record will list all truck numbers associated to it in the original data source.

[tkhcmark]

Let me take a step back for a moment. I original asked how to do this using the data from a json file but I already created a list in Share Point that gets updated from this json file. Would it be more efficient to use the list in Share Point?

[abm]

Hi @tkhcmark 

Thanks for your reply. Still I am not very clear about what data you trying to update? All I can see from your JSON is name and truck number. How about related data for update? Could you please explain the over all process from Create to update?

Thanks

[tkhcmark]

@abm 

I now want to accomplish this using Share Point list for the data source.

I am using a Share Point list that has a list of trucks in it. Each truck i.e. "TruckNo" is unique so there will be multiple entries  for Company i.e. "FullName" in the list. I want to pull all the trucks from the first list and add them to another Share Point list I created that just has columns for "FirstName" and "TruckNo" So the new list will only have the unique values for the"FirstName" column and a "TruckNo" column related to each "FirstName" will be a comma separated list.

First Share Point List looks like this (and some other columns i don't need values from.)

FullName	TruckNo
TOM	TOM123
TOM	TOM124
JACK	JAK569
JACK	JAK987
BIG RIG	BGR001

I want to add to 2nd list like this.

FullName	TruckNo
TOM	TOM123, TOM124
JACK	JAK569, JAK987
BIG RIG	BGR001

My flow now just has a manual trigger and a get items from sharepoint list that has Limit Columns by View selected for a view in Share Point that just shows the FullName and TruckNo columns. I don't know what the next step is. Let me know if you want a screen shot of the flow.

Thank you.

[abm]

Hi @tkhcmark 

Thanks for the explaining your requirement.

This is what you need to do

1. Get Items

2. Initialise a variable called 'TruckNames' as array

3. Add a For Each Control (This is to iterate Step 1 values)

4. Inside the Loop Check TruckName (Column name) does not contain Truck Name (Value from Step 1) then Append the array with Truck Name

5. Close Loop

So this stage we have an array with unique TruckNames.

Next steps:

6. Read the array in a loop

7. Get Items filtered by truck name from step 6

8. Add a for each loop

9. Append Truck Nos

10. end of loop

11. Finally create (second list) with Truck name and Truck Nos.

12. Close Array Loop

[tkhcmark]

@abm 

Can you let me know what is wrong with the following flow? Its not outputting unique values.

Thanks

[abm]

Hi @tkhcmark 

Your IF condition is wrong. You need to map the array variable FullNames does not contain SharePoint FullName column. Then under the YES condition Append the varaible.

Thanks

[tkhcmark]

@abm 

I got the first part working. Thanks

How do I do "6. Read the array in a loop"? What do i use for this?

[abm]

Hi @tkhcmark 

Pass the array schema to Parse JSON. Generate the schema then iterate via loop.

Below is the schema you need to use in your Parse JSON step. 

{

"type": "array",

"items": {

"type": "string"

}

}

Thanks

[tkhcmark]

@abm 

Apply to each 2 has been running for 9 hours.  Is this part of the flow correct?

Thanks

[abm]

Hi @tkhcmark 

No that's not right schema. Can we do a screen share? Send me an email. I am available for the next half hour.

Thanks

[abm · Accepted solution]

Hi @tkhcmark 

Thanks for the screen share. Hope everything is working as expected.