Answered

ParseJSON in AddColumns function

(0) Share

Report

Posted on by Dioz

Hey everyone,

I've got a SharePoint list with columns that contain data in the following format, since they're imported lookups from a different list:

{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1234,"Value":"XYZ"}

I managed to extract the values in a gallery with:

Text(ParseJSON(ThisItem.SampleColumn).Value).

My problem is, that I want to use the extracted values in a filter and I can't use the ParseJSON itself, because it has to be delegable. So my idea was, to put the following function in my OnStart, to collect all the necessary values in a column:

AddColumns(SharePointList,"NewColumnName",Text(ParseJSON(SampleColumn).Value))

This doesn't seem to work though, as it doesn't create a new column for me at all. Is there a different way I could try, to make the values usable? Preferably without an additional workflow. Would this cause performance issues on app start?
Thank you

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Dioz ,

First of all, AddColumns can not add a new column to the data source , it will just return a temporary virtual table.

Second, AddColumns is not delegable, if you want to get all items with out delegation issue , please do not use this function.

Finally , according to your description, it is unlikely that this formula can be delegated , could you please show me the logic of the filter .

Best Regards ,

Bof

Was this reply helpful? Yes No
Dioz 61 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey, thank you for your response.
I know that the function isn't adding the column to the actual datasource, but I though I could still use the virtual table column for filtering the other list.

My current filter looks like this:
Filter(
SharePointList,
(Category.Value = cmb_Category.Selected.Name || Process = cmb_Process.Selected.Name || Process-Nr = cmb_MyProjects.Selected.Name) && (StartsWith(
Titel,
txt_Search.Text
) || StartsWith(
SampleColumn,
txt_Search.Text
)) && Status.Value = "In progress"
)

In this case I'd have to exchange "Process" with the column I had in mind with the AddColumns

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Dioz ,

Please try:

Filter(
SharePointList,
(Category.Value = cmb_Category.Selected.Name || First(Split(Last(Split(ColumnName,"""Value"":""")).Value,"""")).Value = cmb_Process.Selected.Name || Process-Nr = cmb_MyProjects.Selected.Name) &&
(StartsWith(Titel,txt_Search.Text) || StartsWith(SampleColumn,txt_Search.Text)) && Status.Value = "In progress"
)

But this formula is not delegable, and I'm afraid there is no delegable solution.

Best Regards,

Bof

Was this reply helpful? Yes No
Dioz 61 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft

Thank you, your formula seems to work. Unfortunately you're right about it not being delegable. If I just use the part with Text(ParseJSON(ColumnName).Value) directly in my filter it would also work without being delegable. Good to have a different option to play around with though. I'll leave this open for a bit longer to see if anyone can come up with another solution, but it feels like those are my best options.

Was this reply helpful? Yes No