Unanswered

Best Data Source?

(0) Share

Report

Posted on by CTASS

I have written an app using two related sharepoint lists that is working pretty well. I have written it by googling around for help since I don't really know what I am doing. The problem that I am running into is the very onerous and frustrating task of trying to update my data source. Every week I get an excel report from our software that has new information that needs to replace the last week's data. Here are the problems I have encountered:

- Sometimes I can copy the data in my excel sheet and paste it into the last cell of my sharepoint list and it works. More often than not it does not work so...

- I wrote a flow that takes my excel rows and creates new items in my sharepoint list. This works BUT also inexplicably re-creates the rows I most recently deleted.

- I also wrote a flow to try to check everyday for list items dated previous to "today" and delete them and it either doesn't work (when the trigger is set to work automatically) OR deletes every row (when I use the manual trigger).

- I tried switching to dataverse/tables built into power apps but also cannot simply copy and paste into those tables so I assume using dataverse will be just as fraught with difficulty as sharepoint is.

- Most recently I am trying to use excel itself as my data source but now getting messages about there being "no key column." Googling around I find suggestions to upload the table to dataverse which puts me right back where I started. Plus I understand excel is not the most robust data source for power app use.

I would welcome suggestions for how best to handle data that is frequently changing. The software we use for daily business is old so I do not think there is a way to connect to it directly, plus there are changes I make here and there once I have the data in excel to make the data more friendly to my app use.

Thanks!

Categories:

Building Power Apps

All responses (6)

Answers (0)

Sort by

developingteal 392 on at

Like
a
(0)

Report
Copy link

Link copied!

If you want to stay with sharepoint lists, I would recommend writing a power automate flow for the usecase you are describing. Exporting from excel into sharepoint. I need more info about
"This works BUT also inexplicably re-creates the rows I most recently deleted."

Where did you delete the rows? In sharepoint? If so I would make a master sharepoint list with all the data you ever have put in, double check that if an excel row equals one of those rows to not put it into the new list.

I am not sure if this is any help at all to you, but it definitely seems like automate+apps should be able to get a solution for what you are describing.
CTASS 69 on at

Like
a
(0)

Report
Copy link

Link copied!

I don't necessarily want to stick with Sharepoint, specifically because I haven't had much luck with my attempts at writing flows.
This is the flow I have written for finding and deleting "old" data. It is set to run at 11:11 pm everyday and delete any row whose "OUTDATE" column is "today" or earlier.

The formula for the Get Items filter query is OUTDATE le
'formatDateTime(utcNow(),'MM/dd/yyyy') . The Apply to each is using the only choice available in the Dynamic content which is body/value from Get Items. And in Delete Item the Id is items('Apply_to_each')?['D']
When I tested this, it deleted all the rows in the sharepoint list.
Previously I had been going through and deleting old rows by hand.
This is the flow I wrote to upload new rows for the coming week into the Sharepoint list:

Here is the List rows info:
This worked when I tested it today, but last time I did it it did add on a bunch of rows that I had just deleted. But I do remember seeing later on a warning that some the rows I was deleting didn't actually "delete." I don't remember the message I got, but I remember the frustration of not being able to quickly, easily, and reliably make changes to sharepoint lists the way I can in excel.
That's why I was asking what is the best source to use when the data needs frequent updates. Is there any source besides excel that I can just cut and paste?
Thanks for your help!
developingteal 392 on at

Like
a
(0)

Report
Copy link

Link copied!

Yeah I would recommend excel if it needs frequent updates - it is most likely where you are most comfortable anyways. I would recommend sql server if you are frequently updating a lot of data, but you most likely would be less comfortable with sql and learning all of that.
developingteal 392 on at

Like
a
(0)

Report
Copy link

Link copied!

There is not really many other data sources where you can cut and paste to answer your questions - I can't think of any off the top of my head
CTASS 69 on at

Like
a
(0)

Report
Copy link

Link copied!

This morning I went into my sharepoint list and deleted all the old items by selecting them all and clicking delete. I paid special attention to the message at the bottom of the screen, which did tell me that one record had an "enquiry" that failed and was not deleted. Sure enough, when I refreshed that item was still there. So I tried deleting it again and the message at the bottom said "1 item deleted".
Then I ran my flow to add next week's new data, and it was successful. But when I went to my sharepoint list and hit refresh to check my new data, it was there BUT so were all the records I had just deleted and a few older than that!!
This is so frustrating! Can anyone help? This isn't major league data here - I'm only dealing with about 250 items at a time and only 9 columns - simple stuff like invoice #, name, date, etc.
CTASS 69 on at

Like
a
(0)

Report
Copy link

Link copied!

I just did some more looking around and perhaps this is happening because I have older versions of this power app that also used the same sharepoint list. Would that cause it to be "checked out" by another user (even though I am the only user?)