Add New Columns to Excel

[tfmarcomtf]

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?

[timl]

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]

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.

[timl · Accepted solution]

Hi @tfmarcomtf

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

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:

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]

Thanks @timl.

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

Much appreciated.

[tfmarcomtf]

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]

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!

[timl]

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]

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:

 

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?

[timl · Accepted solution]

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, "$", ""))