Question about table design (Time Tracking)

(0) Share

Report

Posted on by memphis111

Hi, everyone,

I'm not getting on like this at the moment.

I have created a ticket system where the people can log in tomorrow, take a break, come out of the break and log out.
I write this time information in a table in Dataverse
This means that the information is written into the table as a separate data record.
So 4 lines per day

I have calculated columns where the time between the individual breaks is calculated.

Now to my question:
How do I get it that I get a reporting per week?
So I would like to have the weekly list, Monday to Friday the duration of the work per day.

Since I have all the entries in the database, I could read the entries with the SUM function and filter (from PowerApps)

With a SQL server, I could write the 4 entries to a new DB every day using a script.
Unfortunately, that doesn't work with tables, right? Of course I could do this with FLOW, but I don't have a FLOW Premium license

Is there any other way?

greetings

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Ram Prakash Duraisamy 5,877 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @memphis111,

Each and Every day the User will have new RECORD created in week wise ? if yes then Create a ROLLUP field in the Parent table and Calculate for a week.

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @memphis111 :

Do you want to group the data in the table by week number?
If so, I've made a test for your reference:

GroupBy( Ungroup( With( {WeekdayList: ForAll(Sequence(365,0,1), { StartDateTime:DateAdd(Date(Year(Today()),1,1),Value,Days), EndDateTime:DateAdd(Date(Year(Today()),1,1),Value+1,Days), WeekNum:WeekNum(DateAdd(Date(Year(Today()),1,1),Value,Days)) }) },//Get a list of all dates in the current year and calculate the week number for each day ForAll( WeekdayList, { TheWeekNum:WeekNum, NewGroup:Filter(YourDataverseTable,'TheDateTimeColumn'< EndDateTime && 'TheDateTimeColumn'>=StartDateTime) } ) ),//Group the records in the table by day, and get a WeekNumber for each group of records "NewGroup"),//Since there are 7 days in a week, all records in every 7 group have the same weeknumber. If you need to merge them, you must first ungroup them so that each record in the table has a week number "TheWeekNum",//Reuse Weeknumber to group all records "ANewGroup" )

Best Regards,

Bof

Was this reply helpful? Yes No
memphis111 54 on at

Like
a
(0)

Report
Copy link

Link copied!

That's perfect, thank you ...
But a quick question, where do I use the formula?
I put the formula in a gallery, it now looks like this

How do I get the values from my table linked to them? My table is structured as follows:

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @memphis111 :

First,the formula I provided is only a preliminary grouping of the data. If you want to do summary calculations, you also need to nest the AddColumns function.For example:

I assume you want to add a count column to get the number of records in each group:

AddColumns(GroupBy( Ungroup( With( {WeekdayList: ForAll(Sequence(365,0,1), { StartDateTime:DateAdd(Date(Year(Today()),1,1),Value,Days), EndDateTime:DateAdd(Date(Year(Today()),1,1),Value+1,Days), WeekNum:WeekNum(DateAdd(Date(Year(Today()),1,1),Value,Days)) }) },//Get a list of all dates in the current year and calculate the week number for each day ForAll( WeekdayList, { TheWeekNum:WeekNum, NewGroup:Filter(YourDataverseTable,'TheDateTimeColumn'< EndDateTime && 'TheDateTimeColumn'>=StartDateTime) } ) ),//Group the records in the table by day, and get a WeekNumber for each group of records "NewGroup"),//Since there are 7 days in a week, all records in every 7 group have the same weeknumber. If you need to merge them, you must first ungroup them so that each record in the table has a week number "TheWeekNum",//Reuse Weeknumber to group all records "ANewGroup" ),"CountRows",CountRows(ANewGroup))

Secondly,you could put this formula into a datatable control's items property to show the data.

Best Regards,

Bof

Was this reply helpful? Yes No
memphis111 54 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi there,

was traveling for a few days.
Thank you again everything went as I wanted it to.
Have a good time

Was this reply helpful? Yes No