Need help with pedigree database 2

[secretaryat]

Hi, I'm not a beginner but this one has me stumped. I'm trying to build a pedigree database for horses in Paradox 10, and can't figure out how to do it without making it *gigantic*.

I've used TesioPower (

http://www.tesiopower.com

), you can download a free demo to see what I wish I could make mine do... the program would work for me but I haven't got $250 to shell out, so I'm trying to make my own.

What I have so far is a table with the following fields:

Horse Name
Age
Color
Gender
Sire
Dam

And I have the form done, it's real purty but I can't get past the first generation (the form goes to 6 generations; there are 64 horses in the 6th generation).

Ideally, I'd be able to type in a horse's name in the Sire field and have it bring up the full pedigree for the Sire, ditto with the Dam field. Say I want the 6-gen pedigree for Secretariat. I input Secretariat's info (he's a new record), sire is Bold Ruler, dam is Somethingroyal. Bold Ruler and Somethingroyal each have their own record in the table with their sires and dams and all that. I somehow need for the Sire field and the Horse Name field to be linked, so that when I type in Bold Ruler in the Sire position, it grabs the info from Bold Ruler's own record (as Horse Name) and lists his Sire and Dam in the appropriate positions on the form, and *their* sires and dams, and so on.

Basically, I need the Sire field (and the Dam field) to turn itself into the Horse Name field. I know it can be done (TesioPower works wonderfully this way!), but I don't know anything about writing code or any of that - I can link tables and that's about it I am willing to learn, if someone can tell me what I need to know and where to get that info. I have a set of books for Paradox 4, if that would help. Is this an ObjectPAL thing? Or what?

My other alternative, as I see it, is to have 130 fields in the table, and type in each pedigree in full, for each horse. There are about 50,000 horses total in my database, and... uh... no.

Am I being way too ambitious here, or can this be reasonably done? Any help will be much appreciated!

Thanks,

Nicole

 

[footpad]

Nicole,

You don't need 130 fields in your table. You just need to get the basic data model worked out and it's a bit more common than you might realize. Consider, for example, a grade school trying to track the relationships between many kids, their parents, and so on. Similarly, a homeowner's association may wish to publish a neighborhood directory where homes many have one resident, roommates, renters, live-in parents, and varying numbers of kids.

Let's assume your data model looks something like this:

   HORSES.DB
     +-->> LINEAGE.DB  --> HORSES.DB

Yes, HORSES is in the data model twice. If each horse has a unique ID, then LINEAGE can look something like this:

LINEAGE.DB
   1.  HorseID       I*
   2.  ParentID      I*
   3.  Relationship  A4  ; {Sire|Dam}

The second accurance of HORSES.DB in the data model lets you link back to the details and then let Paradox find them for you at run-time.

Tip: Use the data model dialog to flag the second occurance as read-only. It'll improve performance.

You'll need a bit of ObjectPAL to see the horse's full pedigree. While there are many ways to approach this, I'd probably use an array of variables of the same type as my ID, a longint in the case to track horse ID's.

This array would be my loop control; that is, I'd:

1. Initialize it with the seleected horse's ID
2. Locate that ID in LINEAGE
3. Copy all three ID's into a temporary table (to be used for viewing/printing purposes).
4. Add the parent's ID's into the control array
5. Remove the current horse's ID from the array.
6. Check to see if there are any remaining ID's in the array. If so, return to Step 2; otherwise, continue.
7. Open a form (or report) that uses the temporary table as a master table and let the user go from there.

If you're only interested in six generations, you can add a counter to the process to track the generations.

If you've not used Paradox in this way, I'd suggest starting with the end results so you know where you're headed with this. By that, I mean I'd design all three tables and at least rough sketches of the forms/reports you want in the end.

This will tell you what you need to do and the where it'll come from. One you get that, then it's a simple matter of working through the mechanical processes of getting everything set up.

Hope this helps...

-- Lance

P.S. I hope this makes sense. It's a little hard to describe off the top of one's head. If you've got any questions, please feel free to send an email to the address on my profile. Note: E-mail delivery may be flakey for a day or two; the server's motherboard has fried a couple of hard drives and we're trying to rebuild it now. If it bounces, please try again in a few days.

 

[Langley]

Well, I can figure out how to populate a form with the information using objectPal, but I think you need to give each horse a unique number to make things easier. Have a look-up table that only has the unique number (don't use autoincrement) and the Horse name. Use that lookup table to populate your main table. This is a first stab - just a brainstorm really - and I need coffee

Lookup.db
---------
HorseNum N*
HorseName S50


Main.db
-------

HorseNum N* - tied to HorseNum in Lookup.db
Age N
Color A10
Gender A1
SireNum N - tied to HorseNum in Lookup.db
DamNum N - tied to HorseNum in Lookup.db

I guess I would use a bunch of unbound fields (i.e. CurrentHorse, Gen1SireNum, Gen1Sire, Gen1DamNum, Gen1Dam, Gen2Sire1Num, Gen2Sire1, etc...) and a pushbutton. Use a tCursor to step through however many generations you want and fill all the fields. I haven't give much thought to reports, but that's another issue.

Here's a snippet of code to give you the idea, but no matter how I slice it, it's gonna get big by the sixth generation.

var

tc tCursor
curSire number
curDam number

endvar

if not tc.open(":myalias:Lookup.db&quot
then errorShow()
endif

; the primary fields can be a two field holding table tied to the look-up.db


if not tclocate("HorseNum",CurrentHorse.value)
then msgStop("Not Found","Horse not in lookup table&quot
return
endif

curSire = tc."sireNum"
curDam = tc."damNum"

if not tc.locate("HorseNum",curSire)
then gen1SireNum.value = "9999"
gen1Sire.value = "Unknown"
else gen1SireNum.value = tc."HorseNum"
genSire1.value = tc."HorseName"
endif

if not tc.locate("HorseNum",curDam)
then gen1DamNum.value = "9999"
gen1Dam.value = "Unknown"
else gen1DamNum.value = tc."HorseNum"
genDam.value = tc."HorseName"
endif

(Use cut and paste, change the field names, use the NUM values for reference, and produce the reams of code needed to fill each field for 6 generations).

This is not exact, and there is probably a better way to do this using an array - I just haven't had time to think about it.

Mac

"Do not delve too deeply in the arts of your enemy and so become ensnared by them"

langley_mckelvy@cd4.co.harris.tx.us

 

[secretaryat]

Ahh... my eyes are glazing over... time to hit the books and/or the help files, and get myself an ObjectPAL education! Thank you both so much! I'll have some more coffee and let these ideas sink in a bit, and will post again with more questions or my success story

Nicole

 

[HOTLIPS]

Hi
For what it worth and what I remember I completed a dog database and pedigrees to 5 generations some years ago...basically my concept was.. every dog has parents... and set up a sequential set of queries .. one for each generation ... using the main table and copying it a few times and using aliases.... using queries with joined tables ...also Objectpal code .... worked a treat.....sorry I cant give more detail as it was some years ago and I cant quite remember with digging it out.

 

[secretaryat]

Thanks hotlips - if you ever get around to digging that out, I'd love to have more detail Do you mean that you had multiple instances of the main table, linked together? One for each generation, perhaps? I'm not sure that would be practical in my case - the main table will have 50,000 horses when it's finished - but maybe by then I'll have a more powerful computer

I'm still working on learning ObjectPAL - I have a set of old Borland books - but the going is slow.

Thanks again,

Nicole

 

[Langley]

Nicole, there are two books that I recommend. Paradox 9 Power Programming by Mike Prestwood (available at his website -

http://www.prestwood.com)

and Paradox Queries by Dan Erhman (out of print, but available used on

http://www.abebooks.com).

Mac

"Do not delve too deeply in the arts of your enemy and so become ensnared by them"

langley_mckelvy@cd4.co.harris.tx.us

 

[HOTLIPS]

Hi Nicole
50,000 records should not be a problem......
query the table for horse name and father name and mother name ...(just 3 fields... the file size will not be large ) ....copy this answer table for each generation ..ie mothers parents, fathers parents, grandmothers parents , grand fathers parents... etc
then in a series for linked queries one for each generation (because u need the answer table from the 1st query to use in the next generation query...... and so on for 6 generation.... then a final query based on the horse pedigree u want and the last anser table will give u a full pedigree.
Hope this helps