Linking records in an aggregate based on a value in an entity
Application Type
Traditional Web
Service Studio Version
11.11.6 (Build 44614)

Is it possible to link to entities that have a 1:n relationship and select the data in the second entity (n) so that the resultant record set is 1:1.

E.g.   I have two entities names Patients and Appointments.  Their relationship is 1:n because 1 patient has many appointments attached to it.  What I want to accomplish is linking from the Patient only to the latest Appointment based on the AppointmentDate in the Appointments entity.

I can do this in advanced Sql but I would prefer to accomplish it in an aggregate, if possible.

You can see that the problem below is there are multiple rows per patient.  I want to select only one appointment, which is the latest one.  I also have an attribute named SessionNo (integer). If I could somehow select max(SessionNo), it would return only one record, but I cannot find a way to do this.

Your assistance would be appreciated.

Leon

Solution

I thought we don't have to modify the table structure. But glad you solved your problem.


Hey, there is one way to do that is, first, you will on the aggregate selected the max records to be 1. Because you want just one record, then on the sort you are going to sort by the session number and sort by the appointment date! where you are going to get the last appointment with the session number higher :)

Tell me if you have more doubts and if this is what you want

Best regards,

Márcio

Hi,

If i got it correctly, then try below

Step 1 set maxrecord=1 in aggregate.




Step2  Sort with date desc.

It will give you one record with max date.

what do you say about it :)  

Amit Jain



Champion

Hi Leon Holmes,


Follow the steps:

   1. Apply Group by First Name 

         Your Output will be like below image :

    2. Now apply Max property at appointment date.


Final Output : As you can see in below image latest appointment date in output. 

Hope this help,

Cheers

Thank you Marcio and Amit.

However, setting Maxrecords to 1, limits my output to only that 1 record and I need all of the Patient records and only 1 of the Appointment records.

Regards,

Leon

Thank you Rahul.  You suggestions comes close to a solution, except that the aggregate now only outputs the group values, i.e. FirstName and Max(Date). I need access to other attributes as well.

Do you perhaps know of another way to accomplish this.

I am thinking of placing the LatestSessionNo on the Patient entity that I can use in the filter to reduce the Appointment records to 1. But this involves quite a lot of development work.  I was hoping I can have a simpler solution.

Regards,

Leon

Hi,

you can do one workaround, 

Select aggregate with sort with patient ID asc and Date desc and get all records. After that use for each loop and insert the first record in the same record list and avoid others if repeat again in the loop using a variable.




And after that use this new record list.

Hope it can help you to solve your problem with low changes.

AJ

Hi all,

of all above answers, only last one of @Amit Jain will work, if you want to use only aggregates.

But this might be a good example for choosing SQL widget over aggregate to avoid post processing your aggregate result.  If same patients have many appointments, you do a lot of unnecessary retrieving from the database.

Dorine

Hi,

I know you said you know how to do it with sql, Leon.  But this is for community members that maybe don't.

See attached an oml of a possible way for doing this with a single SQL node.

Dorine

QDRRetrieveHighest.oml

Did you check the last option, it required few more codes and your issue can be resolve.

AJ

Admit,

Yes, your recommendation is a functional solution, thank you.

Dorine,

Thank you for the example.  This is a good solution and I am keeping your example for future cases, which come from time-to-time.

However, I have solved this case with a work-around.  I have put an attribute on the Patients entity, which holds the LatestSessionNo.  I then use this in a filter to select the Appointment record with the LatestSessionNo.  Although it uses up space in the database, it is a more simple solution and perhaps more efficient.

Thank you both for your input.  I have learnt from it.


Best regards,

Leon

Solution

I thought we don't have to modify the table structure. But glad you solved your problem.


Dorine,

Yes, I needed a speedy solution so I went ahead and did it this way, which was before I got your response.

Regards,

Leon

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.