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
_______________________________________________
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/B088D54E-9A44-4D3A-B439-EF418E82E69F@gmail.com
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
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
On Thursday, December 29, 2011 11:59:18 AM James Bull wrote:
> 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
>
What database engine are you using? Post the SQL (along with the bizobj) for
us to read and maybe we can help.
If you have the SQL right then it is a matter of creating and using variables
or just grouping in the report.
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/10195755.yrl5iflvWh@linux-12
On 12/28/11 4:10 PM, James Bull wrote:
> Wondering if I could get some pointers.
I will try!
> I have tables setup as
> Parent(person)
> -ChildA(many: medications)
> -ChildB(many: allergies)
Pretty standard.
> 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.
You need to group on the parent's id, and put the "detail" of that parent in the
group header for that group. Here's an example group definition (in rfxml format
which you could carefully copy/paste into the <Groups> section of your report's
.rfxml file)
<Group>
<expr>self.Record.patient_id</expr>
<ResetPageNumber>True</ResetPageNumber>
<startOnNewPage>True</startOnNewPage>
<Objects>
<GroupHeader>
<!-- once group defined, open designer
and add detail here. -->
</GroupHeader>
<GroupFooter>
</GroupFooter>
</Objects>
</Group>
You'll also need groups for the allergies and medications children, and to put their
detail in their group headers. Leave the detail band empty. Here's a starting example
for the allergy group:
<Group>
<expr>(self.patient_id, self.allergy_id</expr>
<ResetPageNumber>False</ResetPageNumber>
<startOnNewPage>False</startOnNewPage>
<Objects>
<GroupHeader>
<!-- once group defined, open designer
and add detail here. -->
</GroupHeader>
<GroupFooter>
</GroupFooter>
</Objects>
</Group>
> 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:
You don't actually need to fill the dataset with all repeated data, but that is the
safest way. The reportwriter will need data for relevant fields while it processes
the "records".
>
> ( {'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} )
Sorry, I didn't look at your dataset when composing my reply, but it looks like you
have all the needed data to make your initial report.
>
> 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.
Please let me know if I'm on the right track in helping you, after you've tried the
grouping like what I've described.
By the way, I do have it on my list to enhance the report writer to allow the Cursor
to be a bizobj, and the reportwriter could then figure out how to iterate the bizobj
and the children. But it is a much simpler implementation how it is.
Paul
_______________________________________________
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/4EFCC603.8040600@ulmcnett.com
I use SQLite.
Below are bizobjs; done using classdesigner, added datastructure as needed.
In both allergies and ptmeds(medication).
class AllergiesBizobj(dabo.biz.dBizobj):
def initProperties(self):
self.super()
self.Caption = "Allergies"
self.DataSource = "allergies"
self.KeyField = "pkid"
self.DataStructure = (
("pkid", "I", True, "allergies", "pkid"),
("ptid", "I", False, "allergies", "ptid"),
("allergywhat", "C", False, "allergies", "allergywhat"),
("allergydscrp", "C", False, "allergies", "allergydscrp"),
)
def afterInit(self):
self.DataSource = "allergies"
self.KeyField = "pkid"
self.LinkField="ptid"
self.addFrom("allergies")
self.addField("allergywhat")
self.addField("allergydscrp")
self.addField("pkid")
self.addField("ptid")
self.ParentLinkField="ptid"
self.FillLinkFromParent="True"
self.NewRecordOnNewParent="True"
class PtmedsBizobj(dabo.biz.dBizobj):
def afterInit(self):
self.DataSource = "ptmeds"
self.KeyField = "pkid"
self.addFrom("ptmeds")
self.addField("mednote")
self.addField("stop")
self.addField("medfreq")
self.addField("meds")
self.addField("meddose")
self.addField("start")
self.addField("pkid")
self.addField("ptid")
self.LinkField="ptid"
self.FillLinkFromParent="True"
self.NewRecordOnNewParent="True"
ptds is patient dataset
allergiesds = ptds.execute("""select d.ptid, d.fname,d.lname, allergies.allergywhat, allergies.pkid as allpkid, ptmeds.pkid as medpkid, ptmeds.meds
from dataset d
join allergies
on d.ptid = allergies.ptid
join ptmeds
on d.ptid = ptmeds.ptid""")
Thanks for any help you can offer. I suspect groups are what will be required but so far no luck in getting what I want.
Happy New Year from Australia
James
On 30/12/2011, at 2:21 AM, John Fabiani wrote:
> On Thursday, December 29, 2011 11:59:18 AM James Bull wrote:
>> 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
>>
> What database engine are you using? Post the SQL (along with the bizobj) for
> us to read and maybe we can help.
>
> If you have the SQL right then it is a matter of creating and using variables
> or just grouping in the report.
>
> 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/10195755.yrl5iflvWh@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/00C2F57E-037F-4CF2-BB62-381777068706@gmail.com
Thank you for your help,
I have tinkered with grouping but never quite got what I was looking for, probably not understanding groups properly.
Have done as you suggested, this results in an appropriate list for allergies, but the medication is repeated twice (once for each ?item in the allergy band)
eg.
-allergy 1
- med 1
- med 2
- allergy 2
- med 1
- med 2
what I am looking for is
- allergy 1 -med 1
- allergy 2 -med2
-med3 etc
medication list tend to be much longer than allergies.
Does each new group nest below the group immediately above? or could the top level group have two groups directly below? -does this even make sense?
I presume rw.Cursor can only be run once for a report, in other words one cannot run separate sql queries and append the data for each run to same report?
Again, thanks for your help and such an excellent framework,
All the best for the new year,
james
On 30/12/2011, at 6:56 AM, Paul McNett wrote:
> On 12/28/11 4:10 PM, James Bull wrote:
>> Wondering if I could get some pointers.
>
> I will try!
>
>> I have tables setup as
>> Parent(person)
>> -ChildA(many: medications)
>> -ChildB(many: allergies)
>
> Pretty standard.
>
>
>> 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.
>
> You need to group on the parent's id, and put the "detail" of that parent in the
> group header for that group. Here's an example group definition (in rfxml format
> which you could carefully copy/paste into the <Groups> section of your report's
> .rfxml file)
>
> <Group>
> <expr>self.Record.patient_id</expr>
> <ResetPageNumber>True</ResetPageNumber>
> <startOnNewPage>True</startOnNewPage>
> <Objects>
> <GroupHeader>
> <!-- once group defined, open designer
> and add detail here. -->
> </GroupHeader>
> <GroupFooter>
> </GroupFooter>
> </Objects>
> </Group>
>
> You'll also need groups for the allergies and medications children, and to put their
> detail in their group headers. Leave the detail band empty. Here's a starting example
> for the allergy group:
>
> <Group>
> <expr>(self.patient_id, self.allergy_id</expr>
> <ResetPageNumber>False</ResetPageNumber>
> <startOnNewPage>False</startOnNewPage>
> <Objects>
> <GroupHeader>
> <!-- once group defined, open designer
> and add detail here. -->
> </GroupHeader>
> <GroupFooter>
> </GroupFooter>
> </Objects>
> </Group>
>
>
>> 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:
>
> You don't actually need to fill the dataset with all repeated data, but that is the
> safest way. The reportwriter will need data for relevant fields while it processes
> the "records".
>
>>
>> ( {'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} )
>
> Sorry, I didn't look at your dataset when composing my reply, but it looks like you
> have all the needed data to make your initial report.
>
>>
>> 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.
>
> Please let me know if I'm on the right track in helping you, after you've tried the
> grouping like what I've described.
>
> By the way, I do have it on my list to enhance the report writer to allow the Cursor
> to be a bizobj, and the reportwriter could then figure out how to iterate the bizobj
> and the children. But it is a much simpler implementation how it is.
>
> Paul
>
> _______________________________________________
> 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/4EFCC603.8040600@ulmcnett.com
_______________________________________________
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/DA45CEA5-FEB4-42AE-974A-9A51DBAB12E1@gmail.com
On Sunday, January 01, 2012 08:56:46 AM James Bull wrote:
> Thank you for your help,
> I have tinkered with grouping but never quite got what I was looking for,
> probably not understanding groups properly. Have done as you suggested,
> this results in an appropriate list for allergies, but the medication is
> repeated twice (once for each ?item in the allergy band) eg.
> -allergy 1
> - med 1
> - med 2
> - allergy 2
> - med 1
> - med 2
>
> what I am looking for is
> - allergy 1 -med 1
> - allergy 2 -med2
> -med3 etc
> medication list tend to be much longer than allergies.
>
> Does each new group nest below the group immediately above? or could the top
> level group have two groups directly below? -does this even make sense?
>
> I presume rw.Cursor can only be run once for a report, in other words one
> cannot run separate sql queries and append the data for each run to same
> report?
>
> Again, thanks for your help and such an excellent framework,
> All the best for the new year,
> james
You know you can have more than one grouping.
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/1820206.ghaprc96aV@linux-12
On Sun, 1 Jan 2012 07:51:00 +1100, James Bull
<jamesmhbull@gmail.com> wrote:
>I use SQLite.
>Below are bizobjs; done using classdesigner, added datastructure as needed.
>In both allergies and ptmeds(medication).
>
>
>class AllergiesBizobj(dabo.biz.dBizobj):
> def initProperties(self):
> self.super()
> self.Caption = "Allergies"
> self.DataSource = "allergies"
> self.KeyField = "pkid"
>
> self.DataStructure = (
> ("pkid", "I", True, "allergies", "pkid"),
> ("ptid", "I", False, "allergies", "ptid"),
> ("allergywhat", "C", False, "allergies", "allergywhat"),
> ("allergydscrp", "C", False, "allergies", "allergydscrp"),
> )
>
> def afterInit(self):
> self.DataSource = "allergies"
> self.KeyField = "pkid"
> self.LinkField="ptid"
> self.addFrom("allergies")
>
> self.addField("allergywhat")
> self.addField("allergydscrp")
> self.addField("pkid")
> self.addField("ptid")
>
> self.ParentLinkField="ptid"
> self.FillLinkFromParent="True"
> self.NewRecordOnNewParent="True"
>
>class PtmedsBizobj(dabo.biz.dBizobj):
> def afterInit(self):
> self.DataSource = "ptmeds"
> self.KeyField = "pkid"
> self.addFrom("ptmeds")
>
> self.addField("mednote")
> self.addField("stop")
> self.addField("medfreq")
> self.addField("meds")
> self.addField("meddose")
> self.addField("start")
> self.addField("pkid")
> self.addField("ptid")
>
> self.LinkField="ptid"
> self.FillLinkFromParent="True"
> self.NewRecordOnNewParent="True"
>
>ptds is patient dataset
>allergiesds = ptds.execute("""select d.ptid, d.fname,d.lname, allergies.allergywhat, allergies.pkid as allpkid, ptmeds.pkid as medpkid, ptmeds.meds
> from dataset d
> join allergies
> on d.ptid = allergies.ptid
> join ptmeds
> on d.ptid = ptmeds.ptid""")
The first thing I see is that there is no sort order specified on
the result set. Without that, you'd have a hard time doing any report
break points. Oh, and do you need the allergy and medication primary
keys (I presume pkid is primary key) in the results? For a report
probably not.
... order by d.ptid, allergies.pkid, ptmeds.pkid
or
... order by d.lname, d.fname, allergies.pkid, ptmeds.pkid
and then treat d.ptid (or d.lname + d.fname) as a first level break, and
allergies.pkid (if you output it, otherwise use allergies.allergywhat)
as a second level break.
firstLevel = None
secondLevel = None
for rec in resultset:
if rec.ptid = firstLevel:
outLine.= ["", "", ""] #same patient, don't repeat data
else:
outLine = [str(rec.ptid), rec.lname, rec.fname]
firstLevel = rec.ptid
if rec.allergieswhat = secondLevel:
outLine.append("") #same allergy, don't repeat it
else:
outLine.append(rec.allergieswhat)
secondLevel = rec.allergieswhat
outLine.append(rec.ptmeds)
# format the output line using the fields from outLine
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/
_______________________________________________
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/hpavf75ar8jqp3hh882g24g1bnbslfch10@4ax.com
Thanks for that, I'm a bit of a newbie so will work through this;
james
On 01/01/2012, at 12:01 PM, Dennis Lee Bieber wrote:
> On Sun, 1 Jan 2012 07:51:00 +1100, James Bull
> <jamesmhbull@gmail.com> wrote:
>
>> I use SQLite.
>> Below are bizobjs; done using classdesigner, added datastructure as needed.
>> In both allergies and ptmeds(medication).
>>
>>
>> class AllergiesBizobj(dabo.biz.dBizobj):
>> def initProperties(self):
>> self.super()
>> self.Caption = "Allergies"
>> self.DataSource = "allergies"
>> self.KeyField = "pkid"
>>
>> self.DataStructure = (
>> ("pkid", "I", True, "allergies", "pkid"),
>> ("ptid", "I", False, "allergies", "ptid"),
>> ("allergywhat", "C", False, "allergies", "allergywhat"),
>> ("allergydscrp", "C", False, "allergies", "allergydscrp"),
>> )
>>
>> def afterInit(self):
>> self.DataSource = "allergies"
>> self.KeyField = "pkid"
>> self.LinkField="ptid"
>> self.addFrom("allergies")
>>
>> self.addField("allergywhat")
>> self.addField("allergydscrp")
>> self.addField("pkid")
>> self.addField("ptid")
>>
>> self.ParentLinkField="ptid"
>> self.FillLinkFromParent="True"
>> self.NewRecordOnNewParent="True"
>>
>> class PtmedsBizobj(dabo.biz.dBizobj):
>> def afterInit(self):
>> self.DataSource = "ptmeds"
>> self.KeyField = "pkid"
>> self.addFrom("ptmeds")
>>
>> self.addField("mednote")
>> self.addField("stop")
>> self.addField("medfreq")
>> self.addField("meds")
>> self.addField("meddose")
>> self.addField("start")
>> self.addField("pkid")
>> self.addField("ptid")
>>
>> self.LinkField="ptid"
>> self.FillLinkFromParent="True"
>> self.NewRecordOnNewParent="True"
>>
>> ptds is patient dataset
>> allergiesds = ptds.execute("""select d.ptid, d.fname,d.lname, allergies.allergywhat, allergies.pkid as allpkid, ptmeds.pkid as medpkid, ptmeds.meds
>> from dataset d
>> join allergies
>> on d.ptid = allergies.ptid
>> join ptmeds
>> on d.ptid = ptmeds.ptid""")
>
> The first thing I see is that there is no sort order specified on
> the result set. Without that, you'd have a hard time doing any report
> break points. Oh, and do you need the allergy and medication primary
> keys (I presume pkid is primary key) in the results? For a report
> probably not.
>
> ... order by d.ptid, allergies.pkid, ptmeds.pkid
> or
> ... order by d.lname, d.fname, allergies.pkid, ptmeds.pkid
>
> and then treat d.ptid (or d.lname + d.fname) as a first level break, and
> allergies.pkid (if you output it, otherwise use allergies.allergywhat)
> as a second level break.
>
> firstLevel = None
> secondLevel = None
> for rec in resultset:
> if rec.ptid = firstLevel:
> outLine.= ["", "", ""] #same patient, don't repeat data
> else:
> outLine = [str(rec.ptid), rec.lname, rec.fname]
> firstLevel = rec.ptid
> if rec.allergieswhat = secondLevel:
> outLine.append("") #same allergy, don't repeat it
> else:
> outLine.append(rec.allergieswhat)
> secondLevel = rec.allergieswhat
> outLine.append(rec.ptmeds)
> # format the output line using the fields from outLine
> --
> Wulfraed Dennis Lee Bieber AF6VN
> wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/
>
> _______________________________________________
> 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/hpavf75ar8jqp3hh882g24g1bnbslfch10@4ax.com
_______________________________________________
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/A729D510-25C9-4E10-9D6E-E078E97AFA4C@gmail.com