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 > <[email protected]> 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 > [email protected] HTTP://wlfraed.home.netcom.com/ > > _______________________________________________ > 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/[email protected] _______________________________________________ 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/[email protected]
