Only show last record for particular Patient or Member

[Jarmizee]

We have patients that change service types as the decease progress or regress. We would like to show only the last update for all the  patients in our tabular result datapage.  

Example data and logic below:

Member_ID 123 has 3 records with the update_dates = 01/01/2023 Service_type = Inactive, 05/01/2023 Service_type = Monitor and another record 11/01/2023 Service_type = Critical

Member_ID 231 has 1 record  with the update_ dates = 02/01/2023 Service_type = Monitor

Member_ID 456 has 2 records with the update_ dates = 06/01/2023 Service_type = Monitor and another record 11/01/2023 Service_type = Critical

 

If we run the report for the current date 11/30/2023 the results should show

Member ID             Last update              Service Type

123                               11/01/2023                  Critical

231                                02/01/2023                 Monitor

456                                11/01/2023                 Critical

 

But if we ran a date rage of <=07/01/2023 the result should be as follows

Member ID             Last update              Service Type

123                               05/01/2023                  Monitor    (Since this is the last record < the defined date range)

231                                02/01/2023                 Monitor

456                                06/01/2023                 Monitor   (Since this is the last record < the defined date range)

 

 

I hope I can get some assistance on this. Thank you!

[PotatoMato]

Hi @Jarmizee, you may need to create a log table where you will insert the changes from the other tables and use triggered action. You can check this one: 

 

 

[CoopperBackpack]

Hello @Jarmizee,

I believe this is achievable with calculations on the DataPage and parameters to pass/receive dates.

I have some questions:

1) Based on your example, what is the expected result if a user searches for the records from 01 Jan 2023 to 01 Feb 2023? There are no records for the ID = 456 within this range.

Do you need to display all the IDs but just leave the last update empty? Or do you want to display only the list of IDs where the last update exists within the date range?

2) How many fields do you need to display on the Report? In your example, you mentioned 3 fields (Member ID, Last update, and Service Type). I assume that a real Report can include a bigger number of fields.

3) Are the search date fields required? Can the user leave the 'Date From' or/and "Date To" field blank?

4) Is it possible that there are 2 records with the same date for the ID but all other data is different?
For example:
123                               11/01/2023                  Critical
123                               11/01/2023                  Monitor

5) Are there other search fields except Date From/Date To? And if so, can they be blank?

6) Do you store the Last update date as the Date without time or it is Date and time?
 

[Jarmizee]

On 12/4/2023 at 7:14 AM, CoopperBackpack said:

Hello @Jarmizee,

I believe this is achievable with calculations on the DataPage and parameters to pass/receive dates.

I have some questions:

1) Based on your example, what is the expected result if a user searches for the records from 01 Jan 2023 to 01 Feb 2023? There are no records for the ID = 456 within this range.

Do you need to display all the IDs but just leave the last update empty? Or do you want to display only the list of IDs where the last update exists within the date range?

2) How many fields do you need to display on the Report? In your example, you mentioned 3 fields (Member ID, Last update, and Service Type). I assume that a real Report can include a bigger number of fields.

3) Are the search date fields required? Can the user leave the 'Date From' or/and "Date To" field blank?

4) Is it possible that there are 2 records with the same date for the ID but all other data is different?
For example:
123                               11/01/2023                  Critical
123                               11/01/2023                  Monitor

5) Are there other search fields except Date From/Date To? And if so, can they be blank?

6) Do you store the Last update date as the Date without time or it is Date and time?
 

Hello, thank you for your reply. Let me answer some of your questions to further help us.

1. Only 231 would have a result if 01-01-23 to 02-01-23 is the date range. Also display only the list of IDs where the last update exists within the date range.

The customer will know when they started their service so if ID = 789 lets say last update is 01-01-24 (since they are new) If we were to run the report for 2023 he wont be in there. Our plan is not to have a FROM only a <= DATE. So again if we say 12-31-23 it will look back and see all the last update of each ID. But on the other hand if we run a 12-31-24 just as an example of future date. Then all 4 IDs will show with their last "Service Type" accordingly. 

 

2. Yes it will be bigger. We will have them grouped by community and that community can have these IDs under them. 

example: Community A has 123, 456 & Community B has 321, 789. The results will have the "Enable data grouping by this field" checked

Depends on who is doing the lookup they have only a few on their list vs hundreds depending on the size of the community

 

3. As mentioned there wont be a FROM but only a <=TO. 

4. No this should not happen unless they made a mistake but we will not allow double entry for that ID in the same day.

5. The only other search criteria is the date and the community name to cascade the IDs of the patients under that community.

6. Out time stamps are Date and Time this also eliminates the 2 records on the same date.

 

Thank you again for your reply sorry I didnt see your guy's response.