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 (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 (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 (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 (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