ignore "Comma" character when between digits

[marial16]

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

[Drrickryp]

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

[marial16]

it didn't seem to work

[RandyHayes]

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

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

[Drrickryp]

Hi @RandyHayes 

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

[RandyHayes]

@Drrickryp 

And this guy...

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

The above Jason, not so important!

[Drrickryp]

@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.