Answered

Getting errors, changed my application from Excel to SharePoint

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello!

I made my application using an Excel database but then changed to SharePoint. Now I have fixed a lot of errors myself but I can't figure out where these errors are coming from.

The only thing I did change is the following: I had to change the tablename from "MancoTabel" to " 'Manco-Informatie'. The code works with Excel database but not with Sharepoint for some reasons. So basically all I did was change the table to the right table and I get this error.

This is how the error looks:
https://gyazo.com/f67868610f8fc371daa1d1da2db721b2

I also get a deligation error somewhere, it just shows I have one but I do not know where it comes from.

Here is the code:

SortByColumns(
 Search(
 If(
 gblIsAdmin;
 'Manco-Informatie';
 Filter(
 'Manco-Informatie';
 Verzender = User().FullName && If(
 selectedStatus <> "All";
 IsBlank(selectedStatus) || Status = selectedStatus
 )
 )
 );
 TekstZoekVeld.Text;
 "Ordernummer";
 "Verzender";
 "Deelorder";
 "Extern";
 "Product";
 "Intern";
 "Deelorder";
 "Tekeningnummer";
 "Omschrijving";
 "Status"
 );
 "Datum";
 If(
 SortDescending1;
 Ascending;
 Descending
 )
)

Categories:

Building Power Apps

All responses (34)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

A couple of questions to start...

1) Are all the columns of string/text type, or do you have other types in use in this formula (please verify in your list settings)?

2) For the delegation - how many records in your list are we talking about? The search function will not be delegable to SharePoint, so, if you have more records than the record limit in your settings, you will have issues with this.

In general for the formula -

Try changing your formula to this:

SortByColumns( Search( Filter('Manco-Informatie'; If(gblIsAdmin; true; Verzender = User().FullName && If(SelectedStatus <> "All"; Status = selectedStatus; true) ) ); TekstZoekVeld.Text; "Ordernummer"; "Verzender"; "Deelorder"; "Extern"; "Product"; "Intern"; "Tekeningnummer"; "Omschrijving"; "Status" );

I hope this is helpful for you.

v-bofeng-msft Microsoft Employee on at

Like (0)

Report

Copy link

Link copied!

Hi @Anonymous ：

Firstly, let me explain why the “delegation error” appears.

Delegation warnings appear only on formulas that operate on delegable data.

Why there was no delegation warning before: Excel is not a delegable data source.
Why the delegation warning appears: SharePoint is a delegable data source, but search is not a delegable function.

Secondly, let’s focus on your code

Can you tell me more detail about your SharePoint list?

I suggest you check the fieldnames and field data type in your SharePoint list:

Date type: search function could only works for text type field.
Field name: check the field name.

In order to make the original formula works ,you must make sure your fields meet these following conditions:

Cloumn Name	Data Type
Ordernummer	”Single line of text“OR“Multiple lines of text”
Verzender	”Single line of text“OR“Multiple lines of text”
Deelorder	”Single line of text“OR“Multiple lines of text”
Extern	”Single line of text“OR“Multiple lines of text”
Product	”Single line of text“OR“Multiple lines of text”
Intern	”Single line of text“OR“Multiple lines of text”
Deelorder	”Single line of text“OR“Multiple lines of text”
Tekeningnummer	”Single line of text“OR“Multiple lines of text”
Omschrijving	”Single line of text“OR“Multiple lines of text”
Status	”Single line of text“OR“Multiple lines of text”

Beat Regards,

Bof

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I'm trying to do it like this:

I have changed my whole excel file to text only. Whenever I try to export my excel file to sharepoint it automatically turns some columns into numbers.

It's in my language but you can still see I've set everything to text only.
https://gyazo.com/5abfe9243b716c96c4e9448c35e5aa77

Whenever I try to export my excel table to sharepoint I see this:
https://gyazo.com/9d68f4e425d16bb6fee76ff688b294c3

It has automatically changed these columns to numbers although I've turned them to text in Excel. I've then changed all of my column except the column named "Datum" to date and time. The code still does not work. @v-bofeng-msft

I've also tried to remove everything except the column that was already a text column from the fields and even then I got an error.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Even running that code, it gives me an error at the search part, sadly. @RandyHayes
v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ：
About data type:
I made a test,but didn't encounter the problem.
Mabe you should try to change the fileds' date type in SharePoint.
About the error:
check the field name in PowerApps
Add a button and set it’s OnSelect property to : ClearCollect(Test1; 'Manco-Informatie')
Check the field name in Test1(file-Collections-Test1).
If you can’t find “Ordernummer”, "Verzender", "Deelorder"……"Status", maybe there is something wrong with your SharePoint list.
In this case,I suggest you create a new SharePoint list.

If you still have problems, could you show more detail about your sharepoint list?

Beat Regards,
Bof
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Really weird!

I have done what you adviced me to do, the collection looks like this, I see a lot of columns that I don't know where they came from:

https://gyazo.com/f266d4a1805fa643bc4efc8dc6520653

When the sharepoint list looks like this:
https://gyazo.com/321c1eb9bac2df04a986106c205c047c

I've tried to re-do it all but it does the same thing.

All the columns are one line text except the column named "datum" cause that is a date.

@v-bofeng-msft @RandyHayes
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Firstly, I would go into the list settings of your SharePoint list and change all the fields to text that you want to be text, and all those you want to be a date, to a date.

Importantly!!! When you change the SharePoint datasource - make sure you remove the datasource from your App and then add it back in. PowerApps will NOT notice any list changes you make without doing that step.

Then, attempt your formulas.

Also - keep in mind, as mentioned - Search is not delegable - you will get a warning for delegation always.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your answer.

I have already tried to do this but it still gives me an error.

@RandyHayes
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Can you provide details on what error you are seeing?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

It tells me the column "Status" does not exist. And that the function search has invalid arguments.

https://gyazo.com/6a512182461d5ce6e487ed7603d193a4

So I looked into the database and I can see I do have a column named "Status" Maybe to make it more clear we could change it to:

'Manco-Informatie'.Status

@RandyHayes