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 ,

Yes - set a Variable for the total in form 5 when you load the app.

When you update the sheet, the value would be the Variable plus the value in the field. You can do this directly by simply changing the Update property of the card or use a Patch.

Happy to explain further. Not on my PC at the moment.

[LordGrim]

Hi @WarrenBelz 

Thank you for taking the time to reply my questions 😥

I understand what you are saying, but I failed to mention that the work done column will get updated only in that sheet.

For Example: 

User 1 updates the work done column from the sheet 1 screen/table, this update will stay in that sheet/table only, it won't go to table 5. It will go in table 5 eventually but not immediately.

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}

The 32 will remain in "sheet 1" and then it will get updated in  next input.

For Sheet 5 or total I have a summation formula in excel that calculates the value of the other 4 table.

[WarrenBelz]

Hi @LordGrim ,

If I understand you correctly, you need a Variable for each of the sheet work done totals set probably at Screen OnVisible and then add the input to that to write back to Excel.

You can control all of this with Variables if you want to store the current values and then add the input to it.

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]

I see two options here:
1. Insert Another column in your excel that does the addition/cumulative work. That is one column will contain the old figure and the new column will contain the addition   

Column1	Column2
15	15
20	35
16	51

2. The second option is do a Variable (lookup) OnVisible of the screen

Set(MyOldFigure,LookUp(Table1,IDColumn=InputText1.Text, Column1)

In this case, the IDColumn represent one of the Columns on your Table1, InputText1.Text can be inserted or can be part of the textfields on your form.

The variable will MyOldFigure will hold the old in figure that was previously saved.

The next thing to do is to add the variable MyOldFigure to the UPDATE of the DATACARD. So if the DataCardValue in the UPDATE Property is DataCardValue12, then it will be:
 DataCardValue12+ MyOldFigure

If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

[LordGrim]

@eka24 @WarrenBelz 

Option 1 won't work for me.

I get "Invalid Aurgument type" when i added "Set(MyOldFigure,LookUp(Sheet1,WorkDone='DataCardValue12_1.Text', WorkDone)" onVisible in the edit screen.

[WarrenBelz · Accepted solution]

Hi @LordGrim ,

Apart from needing an extra closing bracket on your formula (and I have not other wise looked at it), you don't need to do any of that - you already have it on the screen when you open it.

If the control name is DataCardValue12_1, you just need

Set(
   WorkDone1,
   Value(DataCardValue12_1.Text)
 )

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]

@WarrenBelz  & @eka24 

Thank you very much for answering my questions and helping me solve the problems.

Thank You 😍