Hi All,

I have tables, PatientMedicalStaff, MedicalStaff and TypeValue.

PatientMedicalStaff has a FK of MedicalStaff (FK_MEdicalStaffID) and MedicalStaff has a FK of TypeValue (FK_TypeValueID)

now what I want to do is get all records in PatientMedicalStaff (for partiular Patient) where TypeValue is equal to Consultant.

I am guessing this can be done by RelationsCollections but I cannot figure it out.

I have created the following code. where restrictions is an array of string.

            RelationCollection relationsToUse = null;
            IPredicateExpression filter = new PredicateExpression();
                    
            if(restrictions != null)
            {
                relationsToUse = new RelationCollection();
                relationsToUse.Add(MedicalStaffEntity.Relations.PatientMedicalStaffEntityUsingFK_MedicalStaff);                 
                relationsToUse.Add(TypeValueEntity.Relations.MedicalStaffEntityUsingFK_MedicalStaffType);
                
                filter.Add(PredicateFactory.CompareValue(PMS.DAL.PatientMedicalStaffFieldIndex.IsDeleted, ComparisonOperator.Equal,false));
                filter.AddWithAnd(PredicateFactory.CompareValue(PMS.DAL.PatientMedicalStaffFieldIndex.FK_PatientID, ComparisonOperator.Equal,Manager.CurrentPatient.Patient_ID));
                filter.AddWithAnd(PredicateFactory.CompareValue(PMS.DAL.TypeValueFieldIndex.TypeName, ComparisonOperator.Equal,"MEDICALSTAFF"));
                filter.AddWithOr(PredicateFactory.CompareRange(PMS.DAL.TypeValueFieldIndex.DisplayValue,restrictions));

            }
            else
            {               
                filter.Add(PredicateFactory.CompareValue(PMS.DAL.PatientMedicalStaffFieldIndex.IsDeleted, ComparisonOperator.Equal,false));
                filter.AddWithAnd(PredicateFactory.CompareValue(PMS.DAL.PatientMedicalStaffFieldIndex.FK_PatientID, ComparisonOperator.Equal,Manager.CurrentPatient.Patient_ID));
            }

            ISortExpression sorter = new SortExpression();
            sorter.Add(SortClauseFactory.Create(PMS.DAL.PatientMedicalStaffFieldIndex.IsPrimary,SortOperator.Descending));
                
            mPatientMedicalStaffCol = new PatientMedicalStaffCollection();
            mPatientMedicalStaffCol.GetMulti(filter,0,sorter,relationsToUse);

Any help would be greatly appreciated.

I am using SelfService.

Thanks Hameed

Its ok. No need to reply I've fixed the issue.

It just need some more persistance and standing out of the box.

Thanks

Glad it's solved!