You’re offline. This is a read only version of the page.
I've been trying to figure out a way to use dynamic tables in PowerApps. I get data just fine - the problem is that PowerApps doesn't have a string accessor (e.g. table["NameofColumn"]). Everything is strongly named at design time. Eventually, I'm also trying to do this in a component. The end goal is to dynamically create forms by "zipping" the data into a metadata table and displaying the form in a gallery.... whew!
So, the only thing I can think of is dynamic FetchXml to Json response and now I'm trying to parse and access the response. ParseJson returns a strongly named object. So, I'm down to trying to create a collection on the fly with the ColumnName and ColumnValue as rows like so:
ColumnName | ColumnValue
CN1 | CV1
CN2 | CV2
CN3 | CV3
and so on. I've got a collection of names with the following:
Here's the final code for the couple of folks that "liked" the first connundrum:
Share
Report
All responses (
Answers (
ClearCollect(
colColumnIndex,
Ungroup(
MatchAll(
jsonRequestSummary,
"""([^""]+?)""\s*:\s*""([^""]+?)"""
).FullMatch,
"FullMatch"
)
);
ClearCollect(
gloJsonCollection,
ForAll(
colColumnIndex,
{
ColumnName: Trim(
Substitute(
First(
Split(
FullMatch,
""":"""
)
).Value,
Char(34),
""
)
),
ColumnValue: Trim(
Substitute(
Last(
Split(
FullMatch,
""":"""
)
).Value,
Char(34),
""
)
)
}
)
);Sweet! And a lot simpler than where I was going! I did have some issues with extra ":" characters, so I've taken your cue and just used the RegEx to pre-filter the data into a string I could split on. Works like a charm. On to the next step, thank you!
@lionelgo
if you are currently getting the right "rows" or "records" already, then simply extracting one of the columns out of it, if that's the only place you're stuck, should be straightforward.
Say you were in a hurry and want to create a comma-separated string from a column in a collection (and say you don't have time to optimize it much by incorporating this into the data processing you already have),
then you can use the Concat function in Power Apps.
The Concat function iterates over a collection and concatenates the string representation of one or more records.
Here's how you might generate a comma-separated string from the 'Title' values:
PowerApps
Set(
gloTitleString,
Concat(
gloJsonCollection,
title & ","
)
)
In this formula, Concat is iterating over gloJsonCollection and for each record, it's appending the 'Title' value and a comma to the gloTitleString. At the end of this operation, gloTitleString should hold a comma-separated string of all 'Title' values from the collection.
You may have a trailing comma at the end of the string.
If you want to avoid the trailing comma, you can use the Left function to remove it:
Set(
gloTitleString,
Left(
Concat(
gloJsonCollection,
title & ","
),
Len(Concat(gloJsonCollection, title & ",")) - 1
)
)
Here, Len() is used to find the length of the string and Left() is used to take all characters except the last one, effectively removing the trailing comma.
See if this helps as well @lionelgo
You are indeed trying to achieve something quite complex. PowerApps generally doesn't support dynamic table and column names like you might see in SQL. PowerApps has a declarative language, Power Fx, which is not designed with dynamic data structures as a first class concept.
In the scenario you've provided, what you're trying to achieve with regex may not be the best approach, as PowerApps doesn't natively support regex operations. Even though your example does use a MatchAll function, which can mimic some of the regex functionality, it's still limited in its scope.
However, it seems that what you're essentially trying to achieve is to turn a JSON string into a table that PowerApps can interact with. PowerApps has a JSON function that can convert JSON to and from collections, which might be more suitable for your purposes.
This formula example should create a collection where each record contains two properties: ColumnName and ColumnValue, from a JSON string:
ClearCollect(
gloJsonCollection,
ForAll(
Split(
Mid(jsonRequestSummary, Find("{", jsonRequestSummary) + 1, Find("}", jsonRequestSummary) - 2),
","
),
{
ColumnName: Trim(First(Split(Result, ":")).Result),
ColumnValue: Trim(Last(Split(Result, ":")).Result)
}
)
)
This formula example should handle JSON strings formatted like {"Name": "John", "Age": "30"}, where each key-value pair is separated by a comma.
Note that this is a simplified version and might not handle more complex JSON strings.
For handling dynamic form creation, I suggest looking at PowerApps' built-in functions, such as:
- AddColumns() to dynamically add columns to your data source,
- UpdateContext() to update screen scope variables based on user input,
- and Patch() to update your data source with these changes.
Remember, you cannot use Set() here because it could cause issues later when you want to patch the data, as per Patch requiring the first argument to be a Collection, which means it also cannot be a global variable by Set even if it's a Table.
You could also name your global variables with the prefix 'glo', as in 'gloJsonCollection' to distinguish them from say context variables.
See if it helps @lionelgo
#1
WarrenBelz
146,660
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,999
Most Valuable Professional
