Can this be achieved

[kitley]

Can anyone help me or show me the way to do this with filemaker please.

Having looked for all the templates and examples I cannot find anything even remotely close to what i want to achieve with filemaker.

Before i explain further,i will just summarise the extent of my knowledge and ability.

I previously used Excel to collect data in conjuction with macro enabled word documents,I am quite advanced in vb scripting which has

helped when in comes to filemaker scripts,but i still consider myself to be a learner in that particular department.

The reason I switched to filemaker I think is quite obvious because of the data handling capability and speed of excel.

So i have collected a huge amount of data over the years and this dedicated to National Hunt Racing in the United Kingdom,of which i am

a great enthusiast but not a big gambler because i just appreciate the sport.So in other words this is a non commercial usage data medium

and just purely a personal Diary and reference gazzette.

Here I might just add a post comment,I find it strange that here in the united kingdom there are not apparently so many Filemaker users

or enthusiasts and believe me I am doing my best to spread the word.

Ok, further to my cause:

Having all the necassary data collected in a flat database.I have experimented with several templates/tables/portals/lookups/globals etc.

the list of attempts is endless.Of course all the times i have done this I have maintained the integrity of my main flat database.

All the different connotations i have tried over the last 12 months have failed to deliver what i need from this data collections.

I have written endless groups of scripts,that have functioned to the letter,but eventually realised I have being going off at tangents

and not really achieving the 'Goal'.But this has served it purpoise in giving me plenty of hands on experience.

So what i am asking,and in my opinion it is a big ask is? 'is the anyone here with the interest or more to the point the compassion to

throw me a lifeline'.Or perhaps tell me that this is just not possible.

Onwards To The Problems and hopefully you will bear with me.

I have the main database containing 280.000 records spread over a time lapse period of 8 years containing 20 fields.

1) 'id' field auto enter serial

2) 'a' field Horses Name text (female in red type)

3) 'b' field Trainers Name text

4) 'c' field Jockeys Name text

5) 'd' field Class Type text (Range 1-6) equals the class of the race.(number field optional)

6) 'e' field Type Text equals abbrieviated desciption of the race type.(ie: NH = Novices Hurdle or Hc= Handicap chase)

7) 'f' field dist text race distance abbrieviated(ie: 2m= 2miles or 2m6f=2miles 6 furlongs)

8) 'g' field runners text the number of runners in the field

9) 'h' field value text race value in numbers represents (Gb Pounds)

10)'i' field time text returned time value for the race winner(2m42=2minutes 42 seonds)

11)'j' field Location text Where the race was held

12)'k' field position text the position that the horse finished in the race

13)'l' field date text the date the race took place

14)'m' field going text the state of the ground sometimes abbriated (G-F = good to firm)

15)'n' field age text the age of the horse

16)'o' field weight text the weight the horse carried abbrieviated (11-9= 11 stones 9 pounds)*yes this sport still uses imperial weights.

17)'p' field odds text the starting price that was returned for the horse

18)'q' field dist text the winning distances between the horses related to their finishing position

19)'mark'field mark num this is an invaluable field for me which i have as a numbered field(all the other fields are optional and can be changed

to suit a solution).this field is a record of how many horses in the event go on to win further events.

20)'comments' text self explanatory.

Ok this summarises the fields and their content.I know the obvious comment maybe primarily some fields should have different attributes

but i have left them as text fields,and as one knows they can easily be changed as a solution evovles(hopefuly).

What i require this Master Table to do.

My primary instinct and ideal scenario is:The records in this table would be 'Global'so that data entry is updated in every other tangent table

would be updated with data entry into the master table.

So What subsiduary tables do i need?.

Basically I would be happy,correct that, very happy if: i had one, or ecstatic with two if they capable of displaying.

Child table 1 Would display the Horses Name in a unqique instance but display all his races from the master table in race order which on

average a horse will probably have 25-30 races in it's history.Ah I know what the comment will be here I think,display this through

portals.this i Have tried but have run into several obstacles.The first obstacle is How to accomodate the amount of portals

within the range of a viewable page and it is here i will point out i have used subscript fonts in conjunction with magnified

page views.So it is maybe a better option to achieve this 'Goal'.The other stumbling block i had with this way of doing it was

How to display not just tyhe horses position in the race but his or hers race opponents in the race.which leads me to the next Table.

Child Table 2 Would display an auto serialised record of indidual races.The range of runners in a race would be anything between 1-40

which i presume would be done by using Field Repitions,here i may add i have spent weeks and indeed months,Euthologising

with templates with field repitions/script enabled i could on and on.

Can this be done in Filemaker.am I literally Flogging a dead horse(pardon me).

I have the feeling if it can be done the template would be very versatile and very adaptable to other sports or different racing type events.

Any opinion advice or better still inaugurating your knowledge into the attached template would be supremely appreciated.

many thanks and best wishes to everyone here on this forum.

Dave

EXPERIMENTAL.zip

[Fitch]

Yes it can be done.

If I may offer a bit of advice: post a single short, specific question. Very few people are going to read a such a long post.

Those of us who answer lots of questions often have the problem of too little detail supplied; but too much detail can make it equally difficult to answer a question!

[David Jondreau]

1) It's not repeating fields you need, or a Master table. It's a set of related tables. In the end you won't have your single flat file, instead you'll have multiple tables related to each other by a numeric key. At the most basic, those tables will likely be:

Horses

Races

Horse Race Join

People

Locations

You can get more complicated (Race Lengths, Race Types, People Horse Join, etc) but I think the above is a good place to start.

2) You will need to import the data into your new tables and set all the "foreign keys" which are numbers you'll be using to identify each of the above entities. This can be a bear, but is necessary.

3) It sounds like you're trying to put all this information on one layout. You'll need a half dozen or so layouts to make a solution of this size close to usable.

4) Don't use repeating fields for what you've proposed.

[kitley]

Thank you for your response

having paid due attention to your constructive advice.

I take on board your guideines and will continue.

From this I have also established that repeating fields have no place in this solution.

Which will save me from tunneling any more energy in that direction

many thanks

[kitley]

oh for a moment i thought you were going to offer me constructive advice sum=0

postscript:It is so ironic that on forums there are always those who are Corrective Content Editors.Rather than considerate advisors.Still if it turns you on so be it.

By the number of views seems to be climbing.

[Fitch]

I don't want to dissuade you from asking questions. I was in fact trying to be constructive: in my experience, you'll get more answers and faster answers if you keep your questions clear and concise.

Welcome to the forums, by the way. And I say that without irony.

[kitley]

ok peace

but still no advise just words

[Fenton]

OK, here is a rough preliminary separation into tables; Horses, Races and HorseRaces (existing table). The unique horses were extracted, using Sort on Horse, Export summarized by Horse. Then imported as a new table. I'm still using Horse Name as the relational key, which is kind of lame, and will fail if 2 horse have the same name (ever). It would be better to use a serial ID.

Races was more difficult, as they don't have a name (or an id). What I did was concatenate some fields which I thought would identify it, use that in a self-relationship to identify a "race", then export some fields, to put into Races. I then used the same concatenated field as a "temporary" relationship, in order to bring the new auto-entered serial RaceID into the HorseRaces table. At which point the temp relationship was done, so I switched it over to the real ID one.

Yes, not a very good explanation. But it's (much) easier to do than explain. I don't really know whether you "get" the data all as a flat file (as you have), or whether you want to "enter" the data into the new structure. If the later, then you don't really need to do what I did.

More fields could be brought into Races, anything that applies to the Race itself; then removed from the HorseRaces table (unless needed for something else). I didn't do all that.

HorseRaces_fej.fp7.zip

[Fitch]

1. It will simplify development if you name your fields horse, trainer, jockey etc., rather than a,b,c.

2. It's not crucial to break everything into a normalized data structure, although in principle D.J. is correct. (And I agree you should avoid repeating fields.)

Leaving your structure as is, you can easily isolate a given horse or race using a Find, either manually or scripted. I would recommend you turn on the auto-complete option for certain fields (horse) to ensure correct data entry.

3. Portals have their place, but lists give you some flexibility that portals do not. E.g. it's very simple to sort a list. If you do want to quickly access a lot of portals, a tab object can be useful.

I could go on, but again, I maintain that YOU will get more benefit out of FM Forums by addressing a single specific issue at a time.

[kitley]

hey I have to say a very hearty thanks to you for all your effort and the ammended table which i shall take great interest in exploring.

The world would be a better place with more persons as yourself in it.

I will take some time to assess and understand these changes and post a reply again.

Many many thanks to you.

best wishes

dave

[mr_vodka]

Tom offers you advise on how to get the best chances of getting good responses; even so kindly as saying:

If I may offer a bit of advice: post a single short, specific question. Very few people are going to read a such a long post.

and being the stand up kind of guy that he is, tells you that you misunderstood his intentions.

I don't want to dissuade you from asking questions. I was in fact trying to be constructive: in my experience, you'll get more answers and faster answers if you keep your questions clear and concise.

and Furthermore, even welcome you

Welcome to the forums, by the way. And I say that without irony.

All for what?

but still no advise just words

Yeah with an attitude like that, it just makes me want to drop everything to help you...

[Vaughan]

oh for a moment i thought you were going to offer me constructive advice sum=0

Bzzzzt. That was not the correct answer.

[Søren Dyhr]

I maintain that YOU will get more benefit out of FM Forums by addressing a single specific issue at a time.

I couldn't agree more, but often are we exposed to spreadsheet jockeys (no pun intended) who have been tempted by sirens song originating from the marketing of filemaker, which deliberately alluring says that a natural progression actually exist between spreadsheets and the filemaker discourse.

This might have some truth in it some 12 years ago, before the relational approaches wormed itself into the very heart of the tool, and now makes such a jockey knowing enough to be dangerous.

This knowledge makes the questioner ask too abstract questions, ignoring that developers trying to answer usually plays a little word game with the statements looking for "items" among "sets" to get an idea of the relational structure that might fit the solution. The context and purpose is here crucial instead of the too often presented table a's and b's - which says next to nothing about the measures they come in!!!

--sd

[kitley]

Many thanks for the structured layouts;

I am very gratefull and thank you personally for your time and effort.It is very impressive and I cannot thank you enough.

best wishes