Identify most recent record in a table for each customer

[nlachance]

I have a table that records all donations made by members of my organization. Each member can have multiple donations over time. The table records every donation made by all members. I need to be able to report on the most recent donation made by each individual member.  I have tried grouping and then only displaying one (most recent) record, but that just shows me one recent record - not the most recent record by customer. I am sure there is an easy answer but I am not finding it... anywhere. I would so appreciate anyone's help who might have the secret to doing this.

[kpcollier]

What type of datapage are you using? Do you want to display only the most recent record, or have the most recent record on top? Do you want the user to only see their own donations, or others as well?

If it's a Tabular Report, you only want to display 1 record which is the most recent, and don't want others seeing others donations... I set your tabular report up to filter results on load. Pick the User column to filter by, go to the Advanced tab, and check the 'On Load, receive parameter'. Set this to the auth field that matches the User column values. 

Then go to the Results Page Options screen. For default sort order, put the Date field and flip the direction of sorting. Then go to the Advanced tab. For 'Total records returned', put 1. 

If it's a different workflow or datapage, I might be able to help still.

[cheonsa]

On 6/9/2023 at 10:53 AM, nlachance said:

I have a table that records all donations made by members of my organization. Each member can have multiple donations over time. The table records every donation made by all members. I need to be able to report on the most recent donation made by each individual member.  I have tried grouping and then only displaying one (most recent) record, but that just shows me one recent record - not the most recent record by customer. I am sure there is an easy answer but I am not finding it... anywhere. I would so appreciate anyone's help who might have the secret to doing this.

Hi @nlachance,

You can create a Tabular Report, then in the Results Page, include only one field (Member's name). There is an option in the  Select Results Page Fields to show distinct records only. You will just need to add a Calculated field to get the most recent donation by each member.

Here's the sample formula:

SELECT (Donation) FROM TABLENAME WHERE Member_Name = target.[@field:Member_Name] ORDER BY DESC

 

For more details, you may check these links:

https://howto.caspio.com/release-notes/caspio-30-0/

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

https://howto.caspio.com/function-reference/

[nlachance]

These are helpful, but I need a report that shows the last record for all users (not limited to a single user). Here is the use case, applied to another table. This table contains all of the road pick-up activities each of our Road Adopters has entered into the database. We have a rule that says each road adopter must report on activities quarterly. I want to run a report that shows me the last time each adopter reported so that I can send emails to those who have not reported and remind them to report. I can do a lot of Excel work to make this happen, but I figure there must be a way to run a report each month that shows the name of each road adopter and when they last reported.

[Queso]

Hi @nlachance, you may need to use an Application Task/Triggered Action set up to send the emails. You can watch this Caspio webinar to have an idea about the feature: 

 

[nlachance]

@Queso -- Yes. I can see how a task might enable me to create a report. I am struggling with the logic to actually implement. I want to go to my pickup  logs, group them by adopter, and then select the most recent report (by date) for each adopter. I could then write the adopters and dates of last pickup to a report and email it. 

Do you have any idea how to create that logic?

[Rohit4321]

Thanks for your kind information. It will surely give me important knowledge. I will look forward from it surely

[Tubby]

On 7/5/2023 at 11:21 AM, nlachance said:

@Queso -- Yes. I can see how a task might enable me to create a report. I am struggling with the logic to actually implement. I want to go to my pickup  logs, group them by adopter, and then select the most recent report (by date) for each adopter. I could then write the adopters and dates of last pickup to a report and email it. 

Do you have any idea how to create that logic?

This is what I came up with.  But I am not sure if this is the most optimal way to do this.


Test table records:

Result:

The WHERE in the SELECT block of table variable might not make sense but that is just my way to set the fields of the table variable but have it as a clean slate. If I don't do that, I will need a delete block before I run the loop.

Now on the top of For Each, I selected the distinct adopters so we can group the results based on it and make sure that the number of records that it will insert to the table variable is the same as the count of unique adopters.

Then the last SELECT blocks simply sorts the records of an adopter based on a date field or a timestamp and gets the top most to get the most recent.
 

[CoopperBackpack]

Hello @nlachance,

I am not sure that I got your inquiry correctly.

For example, there is the table:

It is possible to create a Tabular Report that is based on this table or a Report based on the table that stores unique members.

In case you build a Report on a Donation_table, you need to use the 'Distinct option' feature:

In the Calculated field you may select the most recent date. 

For example:
 

SELECT Donation_Date FROM Donations_table WHERE Member_ID = target.[@field:Member_ID] AND Donation_Date = 
(SELECT MAX(Donation_Date) FROM Donations_table WHERE Member_ID = target.[@field:Member_ID] )

The output:

 

If you have further questions, please update this thread.