lookup calculation

[Poruchan]

Hi,

 

In one table (jt_rank) I have a list of the top 20 jockeys and trainers: [j_name] [j_rank][t_name] [t_rank].

 

On my racing form layout I have fields from another table:

 

[gate] [horse] [jockey] [jock_rank] [trainer] [train_rank]

 

Could someone show me how to put the corresponding rankings where they need to go. Also, I was thinking about returning a value such as ‘nr’ for ‘not ranked’ if that were the case.

 

I started by making the fields on the racing form calculations and then played around with Lookup(sourceField{;failExpression}) … I didn’t get the result I wanted — I wonder, would I be on the right path?

 

Many thanks in advance

 

[Miss A!]

Hi, 

 

I am no expert but just i am trying to understand your challenge - So you got two tables but are all the fields on your layout from one table and what is the relationship field between the two tables?? 

[comment]

Because of the suboptimal way your ranking table is structured, you will need to define two relationships from your other table (Performances?) to the Ranks table (using two occurrences of the Ranks table):

Performances::Jockey = Ranks::j_name

 and:

Performances::Trainer = Ranks 2::t_name

Now, if you place the two related fields, Ranks::j_rank and Ranks 2::t_rank on the layout, you will see the related ranks of the jockey and trainer, respectively.

 

 

Note: we are assuming here that the jockey/trainer names are unique. Ideally, the relationship would be based on meaningless unique IDs, but I presume from your earlier posts that's not practical in your case.

 

 

Also, I was thinking about returning a value such as ‘nr’ for ‘not ranked’ if that were the case.

 

You could use conditionally formatted texts for this and avoid defining two calculation fields for the purpose of display only.

[Poruchan]

Hi, 

 

I am no expert but just i am trying to understand your challenge - So you got two tables but are all the fields on your layout from one table and what is the relationship field between the two tables?? 

 

Hi Miss A,

 

I'll try to answer your question (so far Comment has been getting me through this process and I'm just holding the hammer)

 

The basic horse info comes from one table [new_race] the statistics are shown via a portal from [perfomance]. I wanted to show jockey and trainer ranking, and I found this info online. It changes monthly so I will need to update it.

 

So I created a simple table [jt_ranking] with the fields {ID} {Jockey} {J_rank} {Trainer} {T_rank}.

 

Then I went to make the relationship between the 2 tables and this is where warning bells first went off. Connect what to what? So I created a new field in the first table (a foreign key) which made [jt_ranking] ------< [new_race]. But the relationship between the 2 table seems meaningless.

 

So now I am trying to apply Comment's recommendations. I missed the idea the in the table the jockeys and trainers would be unique.

[Poruchan]

Comment,

 

It worked -- of course. And the form is not looking bad at all.

 

 

racing_form2.pdf

[Poruchan]

Comment,

 

So far I have always made an effort in each objective before coming here hat in hand. I've been teaching for 25 years, so I get that. However, I have one more step to go before I move on to the next stage which will be getting FMP to do most of the calculations that I now have Excel doing. I'm really anxious to start that --- so,

 

To finish the form I would like to add 'finished in the money / races run'

 

Races run was a simple summary. For 'finished in the money' I will need to count from the field {finish} each time the value is 1, 2 and 3. I assumed this was not a summary, but a calculation. 

 

Can you show me how to do this?

[Poruchan]

It seemed like simple magic form a layman's point of view, but I couldn't find a simple calculation to get there. So, I created 3 new fields [First] [second] [Third]. I made them calculations: If(Fin = "1";" 1";"0") etc. Then I made summary fields and totaled them. It worked.

 

I feel like one of the monkeys that produced the works of Shakespeare. 

 

[comment]

I'm a little lost here regarding the context. Try using something like =

not IsEmpty ( FilterValues ( "1¶2¶3" ; List ( Performances::Finish ) ) )

as your test. 

[Poruchan]

Yeah, I don't know what I did or why it works. Since I couldn't figure it out (originally I just wanted to show 4/10 for 4 finishes in the money out of 10 starts)- I'm okay with what I got -- it's like they have in real forms showing ie. 10 3 0 1 to represent 10 starts, 3 wins, 0 2nd place, and 1 third.

 

But I will try out what you showed me so I can learn what's what.

 

I'm calling it done for now -- well, there is a little real estate left (I'll upload the latest copy) when I come back to it, I'd like to put a similar stat under the one above, but this will show the starts, 1st, 2nd, and 3rd places at today's distance ( in the sample just 1800 m in the dirt). Then there would be zero space left. 

japanracingblog_sample.pdf

[comment]

 

originally I just wanted to show 4/10 for 4 finishes in the money out of 10 starts

 

Then you would have to filter the other way round, and count the values that pass the filter =

ValueCount ( FilterValues ( List ( Performances::Finish ) ; "1¶2¶3" ) )

to get the "4" in your example.