How can I add all given date with same ID and Name?

[Cherry]

Hi everyone, 

I need help on Caspio datapage report. I created a submission form for add record in which the user will select a date range together with a code name. The output is to add all the dates. This record has the same employee ID and employee name. How can I add all given date with same ID and Name? Please see details below.

For example:

Employee ID : 91QD5ABY

Employee Name: Vince Dy

Date: 11/1/2022 - 11/5/2022                Code Name Assigned: 01         Total Days: 5

Date: 11/6/2022 - 11/8/2022                Code Name Assigned: 03         Total Days: 3

Date: 11/9/2022 - 11/9/2022                Code Name Assigned: 01         Total Days: 1

Date: 11/10/2022 - 11/12/2022          Code Name Assigned: 04         Total Days: 3

Date: 11/13/2022 - 11/22/2022          Code Name Assigned: 05         Total Days: 10

Date: 11/23/2022 - 11/24/2022          Code Name Assigned: 11         Total Days: 2

Date: 11/25/2022 - 11/30/2022          Code Name Assigned: 07        Total Days: 6

Total Days in Period= 30 days

Expected Output: 11/1/2022-11/30/2022

 

Thank you so much.

 

Refer table.

This is the submission form for Add Record.

 

Current Output.

[CoopperBackpack]

Hello @Cherry,

One of the options is to use a Pivot table DataPage (if it is available in your plan).

For example:

 

This article can be helpful: https://howto.caspio.com/datapages/reports/pivot-table/

The second option is to utilize the Distinct feature https://howto.caspio.com/datapages/reports/creating-a-report-datapage/

However, it has the list of limitations (on your screenshot the inline edit and inline delete are enabled, though editing is not available with the Distinct feature)

 

Basically, it is possible to get the min and max date. I am afraid it is not clear to me whether you need to display data for each month. So, have you applied the Search option on the Report? 

Do you really have separate fields for each code name?

[Cherry]

Hi @CoopperBackpack,

Thank you for the response. 

My answers for your questions.

• Yes, I need to display the data for each month
• It is only 1 field (Code Name) but as dropdown in my submission form. I only used Calculated Field to display separate in my report

 

[CoopperBackpack]

Hi @Cherry, 

Actually, it is possible to get all the values for the Report using Calculated Fields.
The data source for this report can be a table with the list of unique employees. All the calculations will be linked to the Employee_ID. 

However, in this case all the values are just calculated values and there is no sense in editing/deleting them by using Inline Edit/Inline Delete. 

Perhaps, you need a separate table that stores unique employees and data per each month in one record. 

This table can be populated by Trigger or by a Task. 

[CoopperBackpack]

Hi @Cherry, 

in case you just need to display data for the end-user, you can use the Distinct option as I mentioned. 

Another approach is to built a Report based on the table that stores unique employees.

For example I have these 2 tables:

 

 

The Report is created on the 'Employees_test_report' table. So, Employee ID and Employee Name are taken from that table. 

All the other fields are Calculated fields.

Date From:    SELECT MIN(Date_From) FROM test_report WHERE Employee_ID = target.[@field:Employee_ID]

Date To:          SELECT MAX(Date_To) FROM test_report WHERE Employee_ID = target.[@field:Employee_ID]
01:                      SELECT SUM(Total_Days) FROM test_report WHERE Employee_ID = target.[@field:Employee_ID] AND Code_Name_Assigned = 1
02:                      SELECT SUM(Total_Days) FROM test_report WHERE Employee_ID = target.[@field:Employee_ID] AND Code_Name_Assigned = 2
Total Days in Period:       DateDiff(day, [@calcfield:1], [@calcfield:2]) +1
Totals Days:                          SELECT SUM (Total_Days) FROM test_report WHERE Employee_ID = target.[@field:Employee_ID]

The output: 

I am not sure if it works for you. I just shared a couple of ideas.