cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marial16
Post Partisan
Post Partisan

ignore "Comma" character when between digits

Hallo ,

we have a JSON file in share point and we want to retrieve  it in power apps by creating a collection.

 

Collect(
TestCollection,
{
FirstField:Last(FirstN(Split(Last(FirstN(Split(Result,",").Result,1).Result).Result,":").Result,2).Result).Result,
SecondField:Last(FirstN(Split(Last(FirstN(Split(Result,",").Result,2).Result).Result,":").Result,2).Result).Result})
 
Some of the fields may contain Comma Character in the text, so when we try to use the Split function, it Splits the Text Input as well and we don't want that
 
Last(FirstN(Split(Last(FirstN(Split(First(FlowResponse).Result,",").Result,96) .Result).Result,":").Result,2).Result).Result
 
Is there a way to Ignore the Comma character that is between double quotes  (" " ) of the JSON ?
 
Thank you
 
 

 

6 REPLIES 6
Drrickryp
Super User
Super User

Substitute(
                 Last(
                         FirstN(
                                       Split(
                                                Last(
                                                         FirstN(
                                                                    Split(
                                                                              First(
                                                                                      FlowResponse
                                                                                 ).Result,","
                                                                         ).Result,96
                                                              ) .Result
                                                    ).Result,":"
                                                ).Result,2
                                       ).Result
                            ).Result, "","", ""  ""
)

it didn't seem to work

RandyHayes
Super User
Super User

@marial16 

You'll drive yourself crazy doing all the FirstN, Last, First, etc...

 

I do not know your JSON structure, but I can provide some general guidance.

Given the following JSON record:

{
  "firstValue":"number one",
  "nextValue":"number one, plus one",
  "Status":"Closed",
  "Max":10,
  "Min":5
}

This formula will provide a record of those elements and values:

With({_textJSONrecord: 
    "{
        ""firstValue"":""number one"",
        ""nextValue"":""number one, plus, one"",
        ""Status"":""Closed"",
        ""Max"":10,
        ""Min"":5
     }"

     },

    Collect(TestCollection,
        With({convertedJSON:
            ForAll(MatchAll(_textJSONrecord, """(?<nvalue>[^""]+)"": *""?(?<jvalue>[^(""|\n)]+)""?").SubMatches,
                {Name:First(SubMatches).Value, Value:Last(SubMatches).Value}
            )},

            {firstValue : LookUp(convertedJSON, Name="firstValue", Value), 
             nextValue : LookUp(convertedJSON, Name="nextValue", Value),
             Status : LookUp(convertedJSON, Name="Status", Value),
             Min: Coalesce(Value(LookUp(convertedJSON, Name="Min", Value)), 0),
             Max: Coalesce(Value(LookUp(convertedJSON, Name="Max", Value)), 0)
            }
        )
    )
)

 

Result in a collection:

RandyHayes_0-1610643751394.png

 

I hope that you can use this to convert your JSON as well.

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Drrickryp
Super User
Super User

Hi @RandyHayes 

I probably shouldn't have commented as I know nothing about JSON except maybe this guy.9b4605b7b5d2b7e11a477cc9d9445977.jpg

RandyHayes
Super User
Super User

@Drrickryp 

And this guy...

RandyHayes_0-1610645826618.png

 

But on the serious side - JSON is HIGHLY important to know for working with PowerApps.

The above Jason, not so important!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Drrickryp
Super User
Super User

@RandyHayes 

I really qualify as an idiot savant.  I have become pretty proficient at the things that I use PowerApps for, the rest of it not so much.  

Helpful resources

Top Solution Authors
Top Kudoed Authors
Users online (4,170)