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

Add New Columns to Excel

I am creating a company wide IOU tracking app. Our current system is just pen and paper and an excel sheet. 

 

I have the app able to accept a users input, find their name in an excel sheet and add the new iou to their outstanding balance. I also have it write the current sessions iou to a column next to the total. 

 

What I would like to be able to do is have the app create a new column for every new day, any iou's that occur on that day would be filled in the proper dated column. The next day, a new excel column would be added and any iou's would occur and be added to the correct day.

 

Is this possible with powerapps? Is there a better solution?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Add New Columns to Excel

Hi @tfmarcomtf

The way I would approach this would be to store the staff details in an Excel table that looks like this.

image.png

I would use a numeric staff id number to uniquely identify the staff members.

 

To store the IOUs, I would create a table like so:

image.png

When a staff member pays off an IOU, you would record this as a negative value in the IOU table.

For any moment in time, you can calculate the grand total by using the Filter and Sum functions.

 

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-aggregates

Super User
Super User

Re: Add New Columns to Excel

Hi @tfmarcomtf

This issue here is that the data card is attempting to write "$6" (ie a string value) to the GrandTotal field, whereas the Excel spreadsheet is expecting just the numeric value 6.

The thing to check here is the Update property of your datacard. This will reference the text input control for GrandTotal and it'll look something like DatacardValue2.Text.

If you change the Update formula so that it removes the "$" symbol, this should solve your problem. The formula would look something like this:

Value(Substitute(DatacardValue2.Text, "$", ""))
13 REPLIES 13
Super User
Super User

Re: Add New Columns to Excel

Hi @tfmarcomtf

 

It isn't possible to create Excel columns at runtime in the way that you describe.

In terms of data structure, the correct way to do this is to design your app so that it adds data as rows, rather than columns.

 

@Drrickryp has a blog post here that might be useful to you.


https://powerusers.microsoft.com/t5/PowerApps-Community-Blog/Relational-Database-Design-fundamentals...


Alternatively, if you could share a screenshot of the output you're trying to achieve, we can better advise you as to how your source Excel tables should look like.

tfmarcomtf
Level: Powered On

Re: Add New Columns to Excel

Hi @timl

 

Thanks for the quick reply. I suspected as much. 

 

Basically, we have about 140 staff members. Their current IOU totals will be listed next to their name under the GrandTotal column heading. When they initiate a new IOU it gets added to their total under GrandTotal, but we would also like to track each instance of IOU by date for each user. I a good way would be to add a column every weekday and any IOUs for that day would be logged . 

 

Not sure adding rows would work in this situation since there are so many staff members.

 

2019-02-12 09_10_04-Window.png

Super User
Super User

Re: Add New Columns to Excel

Hi @tfmarcomtf

The way I would approach this would be to store the staff details in an Excel table that looks like this.

image.png

I would use a numeric staff id number to uniquely identify the staff members.

 

To store the IOUs, I would create a table like so:

image.png

When a staff member pays off an IOU, you would record this as a negative value in the IOU table.

For any moment in time, you can calculate the grand total by using the Filter and Sum functions.

 

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-aggregates

tfmarcomtf
Level: Powered On

Re: Add New Columns to Excel

Thanks @timl.

 

I will try this method out and let you know if it works out for me.

 

Much appreciated.

tfmarcomtf
Level: Powered On

Re: Add New Columns to Excel

Hi @timl,

 

Staff currently have to scan their ID badge to display their current balance and be provided the option to create a new IOU. The ID badge info is formatted like this:

 

###,FirstName LastName,Department

 

I am using this unique identifier to lookup/filter staff. I have Scan1 = IOUID. All staff have a starting balance that needs to be taken into account. So I have tested by setting up Table 1 as you showed, but added a column for their current balance. I have a second table as you have laid out. Upon pressing the checkout and confirm button, I have a Patch that looks up the users name based on scan1 and updates the current balance with the new iou they entered. This works fine.

 

Patch(StaffNames, First(Filter(StaffNames, StaffID1 = Scan1)), {GrandTotal: Sum(currentBalance, enterTotal)})

 

I have a Collect that adds the individual iou records to the table.

 

Collect(IOUTable, {IOUID: Scan1, Amount: "$" & enterTotal.Text, IOUDate: dateNow.Text})

 

This seems to work so far, but I would like to configure it so the table and Powerapp inputs are number/currency fields. When I change these I start to get errors and things stop working. Not sure how to configure those correctly.

 

Also, I understand that a payment by the employee would be registered as a negative value. However, staff are intended to use this app at certain locations to 'self' checkout. What is stopping them from just inputting the negative amount and basically stealing whatever items they choose? Currently we have this issue with people not actually recording their ious but I feel like this is worse.

 

Is there any way to force a positive value, and then I could create an IOU management app for those in charge of the IOU list, to be able to revise the records when the staff member pays their IOU?

tfmarcomtf
Level: Powered On

Re: Add New Columns to Excel

Hi @timl,

 

I have figured it out.

 

I have added an IsMatch to the checkout displaymode to check if the user text input starts with a "-". If it does, it will disable the button, forcing the user to enter a positive value.

 

I can now create a second app for the IOU list managers to have full control over editing fields without ever having to open the excel sheet.

 

Thanks for your help!

Super User
Super User

Re: Add New Columns to Excel

Hi @tfmarcomtf

Glad you figured out the validation issue!

Glad you

With regards to making PowerApps recognise the IOUTable.Amount field as numeric, one way to do this is to pre-populate a spreadsheet with numeric values in that column, and to use the formatting option in Excel to format those cells as numeric. If you then reconnect/refresh the data source in the designer, PowerApps should treat that field as numeric.

tfmarcomtf
Level: Powered On

Re: Add New Columns to Excel

Hi @timl,

 

Thanks again. I was able to fix that issue as well. 

 

I am now having another issue. As I mentioned I am going to have another management app for those in charge of the IOUs to make changes and edit records (specifically for when people pay back their ious). 

 

I am running into a problem where the data card, when edited, will not write back to the excel source. It produces an error asking for a Number. See attached below:

 2019-02-13 07_55_33-Window.png

 

I have set the text format to number and even set the default to value.

 

Default:

"$" & Text(Value(ThisItem.GrandTotal, "[$-en-US]###0.00###"))

 

Format:

TextFormat.Number

 

The excel column is set to currency.

 

What mistake am I making here?

Super User
Super User

Re: Add New Columns to Excel

Hi @tfmarcomtf

This issue here is that the data card is attempting to write "$6" (ie a string value) to the GrandTotal field, whereas the Excel spreadsheet is expecting just the numeric value 6.

The thing to check here is the Update property of your datacard. This will reference the text input control for GrandTotal and it'll look something like DatacardValue2.Text.

If you change the Update formula so that it removes the "$" symbol, this should solve your problem. The formula would look something like this:

Value(Substitute(DatacardValue2.Text, "$", ""))

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 291 members 3,993 guests
Recent signins:
Please welcome our newest community members: