How to use multiple table from one excel file as data source

[LordGrim]

Hi,
I am creating an app where I am using one Excel file as data source. The Excel file has five sheets each of theme has exact same table.

So, What I am trying to achieve is, I want to add the 5 tables as the data source in five screen.
User will be able to edit the fields in the first four screen, and on screen 5 it will show the cumulative result from the previous 4 screens/tables input.

The problem i am facing is I can't seem to add all the tables as data source in my app.
I have added two tables and the "data screen, detail screen and edit screen" shows all the data from the table.
When I added third table as data source the data screen doesn't show the data from the table.

I am fairly new to PowerApps and I am still learning, So i have very little idea as to how things work.
Any suggestion/solution to my problem would be great help.

Thank You.

[WarrenBelz]

Hi @LordGrim ,

You need a Collection group

ClearCollect(col1, Table1);
ClearCollect(col2, Table2);
ClearCollect(col3, Table3);
ClearCollect(col4, Table4);
ClearCollect(col5, col1, col2, col3, col4)

This will show the combined records of the first four lists.
Do you then need to put all of this in Table5 as new records?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up. 

[LordGrim]

Hi @WarrenBelz 

Thank you for replying.

But I am lost as to how I should do it. Where will I input the formula!

You need a Collection group

ClearCollect(col1, Table1);
ClearCollect(col2, Table2);
ClearCollect(col3, Table3);
ClearCollect(col4, Table4);
ClearCollect(col5, col1, col2, col3, col4)

This will show the combined records of the first four lists.
Do you then need to put all of this in Table5 as new records?

=>yes.

[eka24 · Accepted solution]

If I understand you well, I suggest you check the excel tables again to see if you have automatic unique powerapps I'd columns inserted into those tables that are not appearing in your powerapps.

Again be sure they are not just excel sheet but proper excel Tables.

Finally if everything above is fine but still not seeing the table in the App, you can delete those tables connections for the 3 tables and recreate them again and reconnect.

[WarrenBelz]

Hi @LordGrim ,

In a button or any other control that initiates an action before you want to see the result.

If you send me a screenshot and I brief description of your process, I will look at it for you.

[LordGrim]

Hi @eka24 

Thank you for replying.

I followed your suggestion and the 4 table showed up correctly, but when added fifth table it showed up as blank table data were not displayed, this table has a simple summation formula to show a previous 4 tables inputs cumulative value.

Also I have noticed that the "BrowseGallery1" > "NextArrow1" buttons onSelect function 'Select(Parent)' only works for the sheet 1/the first screen.

[LordGrim]

HI @WarrenBelz 

Here is my work flow concept.

(I have an Excel file where I have five table.)
1. I want to add 5 tables as the data source in five screen. Each table will have detail and edit screen.
2. User will be able to edit the fields in the first four screen/table.
3. screen 5 it will show the cumulative result from the previous 4 screens/tables input. Or I won't have a screen five I just want the data to be updated directly in the excel file based on the input provided by the user in the four table/screen I want in the app.

Here is what I have got so far.

[WarrenBelz]

OK @LordGrim ,

That is a little different to what I imagined. I thought you wanted all the records in table 5 - you want a total of sum numeric columns?

You can do that by simply adding up the values from the fields in the other screens. So if the fields are WorkDone1 to 4 and you have a field WorkTotal totalling this, the Default of this field would be

Value(WorkDone1.Text) + Value(WorkDone2.Text) + Value(WorkDone3.Text) + Value(WorkDone4.Text)

 To get the value in the field, you use

Reset(WorkTotal)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[eka24]

Now that tables have been added, I want to ask do
1.You have data already in your table 5. Because you said it is connected but no data is showing in powerapps

    There should be record in the table5

2. On the cummulative of the other 4 tables into Table 5, I suggest you do that in the datasource itself instaed of powerapps screen. That is do the adding or adjustment in the excel table 5, then the result would show in  powerapps screen 5 as accumulation. If i understand you, you want to add various columns in the 4 tables and put the result into table5

3. I am saying this because the various forms may be entered at different times

[LordGrim]

@eka24  @WarrenBelz Thank you both for replying to my post, I got the app ready and sorted .

I want to add one more feature to my app now, I don't know if this can be done or not.

Note: I just want the output of one column "Work Done" from my app.

What I want to do now is, when user updates the number in the work done column, I want it to be updated automatically with previous data. When he updates the column the next day he will see 0 or the previous days value in that column and he will input the progress he made on that day only.

For Example: 

User 1 updates the work done column from the sheet 1 screen/table,

Day 1:  work done = 10

Day 2: Work done = 15 {user will input 15, in excel i want it to update as 25(10+15) in work done column}

Day 3: Work done = 7 {user will input 7, in excel i want it to update as 32(25+7) in work done column}

Can be done in PowerApps?