cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Capture Date in a given Status

Ok so here is what im trying to do,

I have an app that the user can change the 'Status' of each unique project. What i need is way to capture the Date when that Status is submitted so I can determine how long each project has been in that status.

 

Next, it has to be a record of each change. So that im not looking at an overwritten Date each time it is changed but rather a history of dates it has been changed.

 

This record has to be in an already created SP List, not on a new one.

 

Ultimately in the end, this will be analyzed in Power BI, so I can see how long each project has been in each stage.

If anyone can help how i can go about this i'd appreciate it. I've explored some options but wanted to reach out to the community as well, thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper IV
Helper IV

Re: Capture Date in a given Status

I'm not really sure I understand your criteria.  If you plan to use Power BI, it would be a lot easier to have it look like this.

 

ProjectStageDate
ABCStage11/1/2020
ABCStage22/1/2020
ABCStage3

3/1/2020


However, if you want to make it so it's only using the same record then I'd assume you'd have to structure it like this. 

 

ProjectStage1Stage2Stage3Stage4
ABC1/1/20202/1/20203/1/20204/1/2020

 

In this case, I'd make a patch function that records the date when the status changes.  You will have to implement some controls to make sure they don't activate the patch while screwing around.  IE: You can't go from stage 1 to stage 3.  From stage 2 you can't go back to stage 1, etc etc.

View solution in original post

3 REPLIES 3
Highlighted
Helper IV
Helper IV

Re: Capture Date in a given Status

I'm not really sure I understand your criteria.  If you plan to use Power BI, it would be a lot easier to have it look like this.

 

ProjectStageDate
ABCStage11/1/2020
ABCStage22/1/2020
ABCStage3

3/1/2020


However, if you want to make it so it's only using the same record then I'd assume you'd have to structure it like this. 

 

ProjectStage1Stage2Stage3Stage4
ABC1/1/20202/1/20203/1/20204/1/2020

 

In this case, I'd make a patch function that records the date when the status changes.  You will have to implement some controls to make sure they don't activate the patch while screwing around.  IE: You can't go from stage 1 to stage 3.  From stage 2 you can't go back to stage 1, etc etc.

View solution in original post

Highlighted
Frequent Visitor

Re: Capture Date in a given Status

I have given both those options a thought, heres my feedback:

 

We dont want to use the first option as it will enlarge our SP List too much (which we do not want) and the second option maybe be the route we take for now.

 

But, i was wondering if there was any way to record from PowerApps the 'Status' and 'Date' of change into SP like so:

 

ProjectStage RecordDate of Change Record
ABCListList
DEFListList
GHIListList

 

Where each List is a List, Record, or Table type of each change made in Stage an Date. Then when connected to Power Bi, because its a work horse with more larger sets, we expand data for analysis.

 

Let me know if this is possible cause ive been spending quite a bit of time looking for this solution.

Highlighted
Helper IV
Helper IV

Re: Capture Date in a given Status

@Fonzcorp Can you elaborate a bit more on why you don't want your list to get too long?   I know there is a 5k limit but with indexing you can get around that for reporting in Power BI.  With filters, it should be easy to manage/modify the list via Power BI and sharepoint can hold millions of records.  

 

To answer your question, it is probably possible to create a new sharepoint list for each new project in Flow if that is what you're asking but I don't think this would be a good solution at all and would strongly recommend you don't pursue it.  

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,005)