Author: James Bull
Posted: 2011-12-28 at 19:59:18
Thanks, I have created the sql manually and although not an experienced user of sql think I am on the right track.
The UI displays exactly want I want by using a number of grids to display the child tables of allergies and medications; that was no effort at all given how user friendly dabo is. Trying to produce a report containing that same information is what has me confused right now. I am sure there is a simple solution which I cannot see yet.
regards
james
On 29/12/2011, at 11:25 AM, John Fabiani wrote:
> 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: Dabo-users@leafe.com
> 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
_______________________________________________
Post Messages to: Dabo-users@leafe.com
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/EC780A6D-EAC5-4A88-9A02-8C9C8F589C43@gmail.com