pull excel content based up on the user login to the App

(0) Share

Report

Posted on by rajkumar88

397

Hello Everyone,

DId anyone tried below scenario before? Is it even possible?

we have a PowerApp with whole bunch of screen. However on landing screen we can to show 4 fields form the Excel file based up on the user login to this APP. it can be based up on user email.

Excel field names: Goals, answers, question3,question4.

I tried to import or add as an data source of the excel file called "test" however it is throuwing an error that there is no table found. But the thing is that I made the content as "Tabel" and named it as "testtable" and saved it to OneDrive too.

Not sure what I am doing wrong.

I would like to achievce two things

1. import excel data file as a data source to the form

2. on the landing screen based up on the user login/email ID it should lookup the excel file and publish/pull those four columns in read only mode.

ANy help would be much appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (0)

Sort by

yashag2255 24,769 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

HI @rajkumar88

Firstly, please remove the connection to your excel sheet on one drive, now close the app and clear the cache of your browser and do a re-login.

When you are logged in, try to create a new connection to the one drive and since your excel table is formatted as table, it should pick the table. Please make sure the excel sheet is not opened anywhere else in edit mode. If you are still not able to see the table, please create a new file and insert a table inside of it with all the data and then try to create a connection.

Once the connection is created successfully, now you can add a Display Form and select the attributes that you want to show by selecting Edit Fields in the right menu.

Now to show the details corresponding to current user, please modify the "Item" property as:

LookUp(testtable, emailcol = User().Email)

Here, testtable is the name of table in excel sheet and emailcol is the column in which we want to search current user. User().EMail, gets the email id of current user, in case you have mapped it with the full name, please use User().FullName

Hope This Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
rajkumar88 397 on at

Like
a
(0)

Report
Copy link

Link copied!

@yashag2255,

Thansk for the solution. What is use Email is in multiple rows? For example if UserA has multiple rows with different information, can I display on the screen of all these information? as per below screen shot, I wasnt to display objective and description of three rows for UserA on the form.

ex:

Was this reply helpful? Yes No
rajkumar88 397 on at

Like
a
(0)

Report
Copy link

Link copied!

and also do Ineed to provide read only access to each and every user on "'ONEDrive" location so that they can be able to view on the scree? or it doesn't require that level of permission? I don't want users to see the other's data from those columns by accessing that excel file. Please advise.

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@rajkumar88

If you want to pull multiple records at a time, please use the filter query instead of lookup.

Expression:
Filter(testtable, emailcol = User().Email)

Please make sure that you are using a Gallery/ Data Table control to show these results, since Filter return a table consisting of all the rows.

Also, there is no need to provide any permission to the excel until you are doing a patch or submitting an edit form. You are just filtering the records in your apps using the Filter/ Lookup formulas.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
rajkumar88 397 on at

Like
a
(0)

Report
Copy link

Link copied!

@yashag2255,

I was able to pull data with dummy excel file. However I have an excel file which is more than 3MB and PowerApps will allow only 2MB of content for each tabel. Can I create two excel files and lookup from bothe files and then display the information? Can you please let me know how can tweak abbove functiont hat you have mentioned?

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @rajkumar88

Yes, you can create two excel sheets and to make it a bit convenient you can collect both excel's data into the local collection at app start, this way you will have all the data at a single place and you can reference the same collection throughout your app.

You can use this expression at the app start as:

ClearCollect(MyCollection,ExcelDataSource1);Collect(MyCollection,ExcelDataSource2)

This combines data from both the sources.

Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
rajkumar88 397 on at

Like
a
(0)

Report
Copy link

Link copied!

@yashag2255,

can you please explain me a bit more? I am bit confused here. So I need to use clearcollect on "App start" right?

And then how can I display content based up on this?

I created a Gallery on a screen and a display form on the same screen. EX: Gallery will be on the left hand side and on the right hand side there will be a display form. The reaSON why I did like that is for user to view his/here lin items on same screen itself. IF they select from the gallery "using enxtarrowicon" then on the right hand side it should show detail information. Not sure how to achieve this?

Was this reply helpful? Yes No
rajkumar88 397 on at

Like
a
(0)

Report
Copy link

Link copied!

@yashag2255,

I have added clear collect on "App start", it does show the information. However it shows information of email starting letter from K only. I have emails starting from letter A.

My code: ClearCollect(Mycollection,Goals_final);ClearCollect(Mycollection,Goals_final2)

Goal_final: it has columns "Email", "Objective" , Objective_description"
Goal_final2: it has columns "Email", "Objective" , Objective_description"

Both the excel sheets have similar column names.

What Ihave noticed is that if append "Goal_final" to mycollection before goals_final2 as per my above code, then it is showing results only from 'Goal_final".
Not sure how to fix this now.

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@rajkumar88

So basically instead of referring to two data sources, we are collecting the results in one collection.

eg. ExcelSheet1 has 2000 records and ExcelSheet2 has 1500 records, so we are creating a collection at app start, that will have all the 3500 records at one place, i.e. inside the same collection.

Now you can use this collection in your gallery items directly.

Regarding the issue where you are seeing only goals_final2 records, it is because of second clearcollect, clearcollect removes all the existing data and fill in the new records, you need to modify your expression on app start as:

ClearCollect(Mycollection,Goals_final);Collect(Mycollection,Goals_final2)

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
rajkumar88 397 on at

Like
a
(0)

Report
Copy link

Link copied!

@yashag2255,

thanks for your reply. So based onup mycollection I can filter user.email and once they select thier items it should shows detail information on the right side of the screen.

For this can I use "display form" and make datasource as "Mycollection" ? and items = gallery.selected?

By doing this will it display the information based up on the collection?

Was this reply helpful? Yes No