cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnSSG
Level: Powered On

Excel as DataBase

Hello guys,

I am looking for a way to "Search a field calledName" in an Excel file (EF)" meaning that Excel is the Database and display the name below as I type it.

It works when I create a sharepoint list and write everything by hand. However it takes too much time when I have to modify the database following an EF. (I am not the one writing the EF)

 

What I tried :

- I tried with my Excel File (EF) being on my laptop (but at the end, what I want is to put the EF on Sharepoint).

- I tried importing my EF as a sharepoint list (3 ways tested) (https://sharepointmaven.com/3-ways-import-excel-sharepoint/)

The issues : 

- Can't find the fields (it says that its not existing). Even when selecting the Source, doesn't display the rows, only the Name of the Column

- Type is not a text but a table (which throws an issue)

 

The source code :

SortByColumns(
       If(
              Len(SearchField.Text) > 0;
              If(
                     DropdownSearch.Selected.Value = "Name ou Fname";
                     Search(
                                   'List Collaborateur';
                                   SearchField.Text;
                                    "Name"    //or Text('List Collaborateur'.Name)
                     );
                     If(
                                   DropdownSearch.Selected.Value = "Desktop";
                                   Search(
                                                 'List Collaborateur';
                                                  SearchField.Text;
                                                 "Desktop"
                                   );
                                   If(
                                                 SearchField.Text = "5" || SearchField.Text = "8";
                                                 Search(
                                                                      'List Collaborateur';
                                                                      SearchField.Text;
                                                                      "Desktop"
                                                 )
                                   )
                     )
              )
       );
       "Name"
)

 

Hoping to get help from experts.

Thanks in advance.

John.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Excel as DataBase

Hi @JohnSSG ,

Could you please share a bit more about the issue within your app?

Could you please share more details about the error message within your app?

Further, how do you import the Excel data into your SP list?

Based on the formula that you provided, I think there is something issue with it. I have made a test on my side, please take a try to modify your formula as below:

SortByColumns(
       If(
              Len(SearchField.Text) > 0;
              If(
                     DropdownSearch.Selected.Value = "Name ou Fname";
                     Search(
                                   'List Collaborateur';
                                    SearchField.Text;
                                    "Name"    //or Text('List Collaborateur'.Name)
                     );
                     If(
                                   DropdownSearch.Selected.Value = "Desktop";
                                   Search(
                                                 'List Collaborateur';
                                                  SearchField.Text;
                                                 "Desktop"
                                   );
                                   If(
                                                 SearchField.Text = "5" || SearchField.Text = "8";
                                                 Search(
                                                            'List Collaborateur';
                                                             SearchField.Text;
                                                            "Desktop"
                                                 )
                                   )
                     )
              );
         'List Collaborateur'       /* <-- Add formula here , type your SP list data source here */
       );
       "Name",
SortOrder.Ascending /* <-- Add formula here */ )

Please check if you have imported your data into your SP list correctly. Also please check if you have created corresponding column name within your SP list as that in your Excel file.

 

If the issue still exists, please consider take a try to re-create a new app based on your SP list, then take a try with above solution I provided, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
KroonOfficeSol
Level 10

Re: Excel as DataBase

@JohnSSG 

 

Your approach is totally wrong in this case and I'm still not sure what you try to do. Is this in a gallery or a combobox/dropdownbox? For a combobox/dropdownbox is this on the .Items or the .DefaultSelectedItems. Also your SortByColumns() part is not right and doesn't have an Ascending/Descending parameter. 

 

If you want to create a cascading dropdown then you should filter your .items on the first dropdown. Best you can use something like this:

SortByColumns(
	Filter('List Collaborateur'
		,IsBlank(DropdownSearch.Selected.Value) || Column1 = DropdownSearch.Selected.Value
		,IsBlank(SearchField.Text) || Column2 = SearchField.Text // || Column3 = SearchField.Text
	)
	,"Name"
	,Ascending // Or Descending if you prefer
)

For a gallery this works the same. btw for a dropdown it's my opinion you should use ShowColumns() formula to tell PowerApps the Columns you use in the dropdown, mostly a name and identifier column is minimal.

 

Also in PowewrApps you don't have to use a nested if() formula, because the If() formula behaves like the Ifs function in Excel. So if you like to check for more conditions it works like this:

If(Condition 1
	,Result 1
	,Condition 2
	,Result 2
	,Conditions 3
	,Result 3
	,etc.
)

If you want to use search() formula it works like this:

Search('List Collaborateur'
	,TextInput.Text
	,FirstColumnToSearch
	,SecondColumnToSearch
	,ThirdColumnToSearch
	,Etc.
)

But I really never use Search() formula, because I find the Filter() formula easier to read and more explicit in what I want PowerApps to do. Think of when you have to go back to change something in the way your app works, then readability is a delight.

 

Hope this helps.

 

Paul 

KroonOfficeSol
Level 10

Re: Excel as DataBase

@JohnSSG 

 

BTW, you should better use a SharePoint list and find an idea to import the delivered Excel. Maybe Flow gives a solutions for this.

 

Paul

Highlighted
JohnSSG
Level: Powered On

Re: Excel as DataBase

Hello,

Sorry I wasn't clear. You can forget about the Dropdown.

The most important part is that there is a textbox in which the user can write a name or a Firstname.

And below is displayed a gallery with the name appearing as the user is writing. You can see a picture attached.

 

Basically, I'm already using a sharepoint list with columns "Name", "Firstname"....

And it works ! However, because updating by hand the database is taking a lot of time. I tried importing the Excel File as a Sharepoint List but I've got the issues above.

 

Thank you for your tips, I will improve my code.

Community Support Team
Community Support Team

Re: Excel as DataBase

Hi @JohnSSG ,

Could you please share a bit more about the issue within your app?

Could you please share more details about the error message within your app?

Further, how do you import the Excel data into your SP list?

Based on the formula that you provided, I think there is something issue with it. I have made a test on my side, please take a try to modify your formula as below:

SortByColumns(
       If(
              Len(SearchField.Text) > 0;
              If(
                     DropdownSearch.Selected.Value = "Name ou Fname";
                     Search(
                                   'List Collaborateur';
                                    SearchField.Text;
                                    "Name"    //or Text('List Collaborateur'.Name)
                     );
                     If(
                                   DropdownSearch.Selected.Value = "Desktop";
                                   Search(
                                                 'List Collaborateur';
                                                  SearchField.Text;
                                                 "Desktop"
                                   );
                                   If(
                                                 SearchField.Text = "5" || SearchField.Text = "8";
                                                 Search(
                                                            'List Collaborateur';
                                                             SearchField.Text;
                                                            "Desktop"
                                                 )
                                   )
                     )
              );
         'List Collaborateur'       /* <-- Add formula here , type your SP list data source here */
       );
       "Name",
SortOrder.Ascending /* <-- Add formula here */ )

Please check if you have imported your data into your SP list correctly. Also please check if you have created corresponding column name within your SP list as that in your Excel file.

 

If the issue still exists, please consider take a try to re-create a new app based on your SP list, then take a try with above solution I provided, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

JohnSSG
Level: Powered On

Re: Excel as DataBase

Thank you very much everyone ! You've helped me improve the code and have made it worked. 

 

Best regards,

John

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 422 members 5,378 guests
Please welcome our newest community members: