On Thursday, December 29, 2011 11:10:10 AM James Bull wrote:
> Wondering if I could get some pointers.
> I have tables setup as
> Parent(person)
> -ChildA(many: medications)
> -ChildB(many: allergies)
> The report I am trying to produce aims to have Parent details then below
> this 2 lists of childA and childB - to provide a summary report of
> medication one may be on and allergies. My problem is as I understand it,
> reportwriter needs denormalized dataset - which to me means each 'row'
> contains all the same data with one unique element; so in the example
> dataset below the first 3 'rows' are unique on meds(and medpkid) and retain
> same allergy etc:
>
> ( {'meds': 'medication-test1', 'pkid': 21, 'allergy': 'penicillin-test',
> 'medid': 465, 'ptid': 465, 'medpkid': 217}, {'meds': 'medication-test2',
> 'pkid': 21, 'allergy': 'penicillin-test', 'medid': 465, 'ptid': 465,
> 'medpkid': 471}, {'meds': 'drug-test', 'pkid': 21, 'allergy':
> 'penicillin-test', 'medid': 465, 'ptid': 465, 'medpkid': 472}, {'meds':
> 'medication-test1', 'pkid': 240, 'allergy': 'sulphur-test', 'medid': 465,
> 'ptid': 465, 'medpkid': 217}, {'meds': 'medication-test2', 'pkid': 240,
> 'allergy': 'sulphur-test', 'medid': 465, 'ptid': 465, 'medpkid': 471},
> {'meds': 'drug-test', 'pkid': 240, 'allergy': 'sulphur-test', 'medid': 465,
> 'ptid': 465, 'medpkid': 472} )
>
> So in report I am trying to list for each unique person(ptid) a list
> allergies: pencillin,sulphur and list meds: medication-test1 etc, instead
> as the rw Cursor iterates over each element in tuple I have long list of
> allergies essentially repeated in current example for each unique
> medication.
>
> Have tried grouping but still cannot achieve what I envisage.
> What am I doing wrong? Any help would be most appreciated.
>
> regards
> james
In general I create the sql manually. And in this case you will need to create
the joins with childA and childB.
tempCur = self.PrimaryBizojb.getTempCursor()
tempCur.execute("select nameofFields from ParentTable join childATable a on PK
= a.FK join childBTable b on PK = b.FK")
MyDataSet = tempCur.getDataSet()
IOW's create the sql needed in advance that meets your need. I'm not
completely sure what is needed in your case - but SQL is very powerful and you
should be able to get the data in the right format.
Johnf
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/1636495.Cf1BzCZmVv@linux-12
