Stuck in Filemaker

[lansdowne]

I import the results for UK handicap races into Excel but it has grown to a large spreadsheet and has its limitations. I am the wrong side of cemetery hill and decided to keep the grey matter ticking over learning filemakerpro to import the resuts. I have successfully imported a small test database of results. So far I have the form in the database with the tables and relationships below but when I try to make a layout to view the form  I get the horse name and form I get 1 outing and when I scroll through the same horse displays again with it's next run. I think it's because I have failed to fill the foreign keys in the horse_Race table. I also want to add a Todays runners table but am not sure how to relate it to the existing tables Is it possible to achieve these aims in filemaker or am I barking up the wrong tree. I am at an Impasse but I'm sure it's to do with the relationships somewhere? Tables as follows:-

Course:-   pk_Course_ID, Course

Horse:-    pk_Horse_ID,Horse

Races:-     pk_Race_ID,Course,Rdate,Rtime,Going,Age,Furs,Class,Ran

Horse_Race;- pk_Run_ID,fk_Course_ID,fk_Horse_ID,fk_Races_ID,Course,RDate,Rtime,Going,Age,Furs,Class,Ran,Pos,Drw,TBtn,Horse,Wgt,MARK,GRD,WA,AA,BHB,BHBAdj,RATING,PPL

Relationships from primary key in each table to foreign keys in Horse_race table.

 

 

My aims are as follows.

1. To view EACH individual horse and its FORM in date order latest run at the top

2. To add a todays runners table with races and runners from each of the days races that would loop through each horse and search the database to display the horses and their last 3 ratings latest on the right plus the TOP 9  RATINGS FROM THE LAST 3 RATINGS IN ORDER 

 

How do I add the todays runners table which has the following data

Date  Time,Course,Furs,HorseNo  Horse

How will it be related to the tables I already have?

Many thanks

Davey H

Edited August 20, 2013 by lansdowne

[eos]

Is it possible to achieve these aims in filemaker or am I barking up the wrong tree. I am at an Impasse but I'm sure it's to do with the relationships somewhere?

 

 

That's absolutely feasible with FileMaker; in fact there would be several ways to do it.

 

I could give you a longish explanation, but if you have a database with some sample records that you could post, I'd rather demonstrate how to do it.

[lansdowne]

Many thanks for your reply.

I have attached a filemaker database

 

The Declarations  is the table I want to add that show the horses running on a particular day.

It would have the following fields:

 

Dec_ID    Date            Course    Time      No              Horse

    1        25/08/13        YORK      15:05    1             Pintura

                                                                 2            Norse Blues  

                                                                 3            Frog Hollow

 

 

                                               

I want to loop through each race and runners to show the ratings by each runner with their last 3 ratings in a line latest race on the right (Race Ratings 1). Also to display the ratings in order of all runners. (Race Ratings2).

Each horse has 3 Ratings BHB,BHBAdj,RATINGS.

I was ambitiously hoping to use a drop down box that would allow me to move between each type of rating and the display would change accordingly. If that is not possible then BHB would be the preferred rating to display.

 

I would appreciate your help and advice, particularly if you feel the results data could be broken up into a more suitable setup.

 

Many thanks

 

Lansdowne

 

 

 

 

 

HORSE.zip

[eos]

I would appreciate your help and advice, particularly if you feel the results data could be broken up into a more suitable setup.

 

Hi Lansdowne,

 

the relational structure isn't (totally) correct, but that's the lesser problem. The real problem is that the Horses_Races table has fields for foreign keys, but the entries have not a single value between them. This means you cannot relate those entries to their parent tables (Race (and implicitly Course) and Horse), except by name (or a combination of data). And how do you plan to integrate new data?

 

Here's an example of how you could find all starters for a given race, find their last three races and summarize their results. Note how much global selection data is necessary to target a specific race; if the keys were present, you could just go to a race in the Races table and take it from there.

 

Anyway, you can either print or export these data, or import them into a Declarations table (though that is literally redundant, since it's all derived data).

HORSES_eos.fp7.zip

[lansdowne]

Eos,

 

Thank you for your reply and sample database. Being a newbie most of it is above my head, but it is a move towards my final goal.

 

I have saved a copy of your database and am slowly looking through to see if I can understand what you have done.It may take me some time.

 

It's fantastic to see what can be achieved with Filemaker and I'm feeling much more optimistic thanks to you.

 

thanks once again.

 

Lansdowne

[lansdowne]

As you see I've had a fiddle with the original file. I love the layout with the date,course etc selectable and the race displaying,brilliant!!.

 

I wanted to add the declarations table but even though it is there in the tables and relationship graph I can't "find" it anywhere to import records to it. I would like the same setup for the declarations and to display each horses last 3 runs but sorted into rating order. I've tried having a go myself but being a complete newbie have

so far failed, although I have now bought some tutorial videos which I have started to go through.

 

Any help would be much appreciated.

 

Lansdowne

 

 

tabledeleteFORMLINEHORSES_eos.zip