JCapper Message Board

JCapper 101
-- Table Schema question

Home	Register Log In

Table Schema question

Tom C
4/26/2010
12:59:12 AM

Jeff,

I would like to use some preset and dynamic filters in building SQM UDMs but don't see them listed in the table schema. Does this mean I can't use them in SQM mode?

Tom C

ryesteve
4/26/2010
9:39:54 AM

Can you give an example? Most of the preset filters are functions of factors that are (or can be) available in SQL mode.

Tom C
4/26/2010
11:01:54 AM

Steve,

There are a number of filters that appear on the Key Factors Report such as PCLASS, TPACE, BRIS3 and LASTRACEBRISFIG. In other words, I use more than half of the factors and filters on this report at times.

I also use the dynamic filters called FimsInLastFour, MorningLineRank and Late Pace(last start).

Thanks for your help,

Tom C

jeff
4/26/2010
12:06:10 PM

--quote:

"There are a number of filters that appear on the Key Factors Report such as PCLASS, TPACE, BRIS3 and LASTRACEBRISFIG. In other words, I use more than half of the factors and filters on this report at times.

I also use the dynamic filters called FimsInLastFour, MorningLineRank and Late Pace(last start).

Thanks for your help,

Tom C"

--end quote

Hey Tom, Before giving your question a specific answer, I think some background info about the SQL Mode interface and table schema itself might be in order.

First, here's the Table Schema link:
http://www.jcapper.com/helpdocs/starterhistorytableschema.html

To work with any given factor, use the field in the Table Schema for that factor.

In SQL Mode, there are two types of factors:

1. Native Factors
Factors that are "native" to the table... meaning those that have a field name in the table specifically for "grabbing" that factor. Examples of field names for "native" factors would be RankJPR, GapUPR, ValAlchemy, RankMline, etc.

When designing the Table Schema I tried to make as many of the factors being handled by preset and dynamic filters native/available as possible by giving them their own fields in the table.

But keep in mind that the table is limited to 255 fields per record... making it impossible to include everything found in JCapper when run in Playlist File Mode.

2. User Assigned Factors
In addition to factors that are "native" to the table, there are 105 table fields allocated for User Assigned Factors... 35 factors times 3 fields per factor... Why 3 fields per factor?... A field for numberic value, a field for rank, and a field for gap.

The program download package comes with a default factor setup where the interface is pre populated with 35 default factors.

A complete interface is provided... available from the User System Definitions Screen... making factor setup fully customizable by the user.

If you want to use a factor, and that factor is not "native" to the table, and it is also not one of the default factors, then you would need to use the interface to bring that factor into the table by assigninging it to one of the 35 F-Factor slots in the table schema.

Of course, this means removing one of the default factors to make room for it first.

Keep in mind that when you do this, you will likely need to edit your custom report layout too. You don't want to replace factorA on your report layout with factorB and leave the column header labeled in such a way that makes you think you are still looking at factorB.

After making the change, you will also want to rebuild your databases from scratch. You don't want to be looking at existing data for factorA in the table thinking that it's factorB.

Ok. With that background info out of the way, let's dive in and try to answer your question specifically.

Morning Line is one of the "native" factors in the table. There are fields in the table schema named MLINE and RANKMLINE which provide handles for using Morning Line in a UDM.

AND RANKMLINE >= 3

The above sql expression used in a UDM will cause your UDM to ignore the top two (public) ML choices.

As for PCLASS, TPACE, BRIS3m, LASTRACEBRISFIG, FimsInLastFour, and Late Pace, if any of those happen to be one of the 35 User Assigned Factors in your factor setup, then you would use the F-Factor Slot Number as your "handle."

Example, suppose for the sake of argument that TPace in your factor setup is in Slot Number 11...

You could "grab" rank for TPace in a sql udm with the following expression:

AND RANKF11 = 1

I hope I've managed to answer the question here in a way that makes sense.

-jp

.

Tom C
4/26/2010
12:24:16 PM

Jeff,

Thanks for clearing this up for me. I have found most of the factors I mentioned in the factor setup tool except for FimsInLastFour.

Can you tell me how I can use this filter in SQL mode?

Thanks,

Tom C

jeff
4/26/2010
2:22:36 PM

There currently isn't a handle for FimsInLast4 in sql mode.

That said, I'm planning on publishing a new program update sometime within the next 24-48 hrs.

I've added an active handle for FimsInLast4 in UPR Tools. That means that in the new program update you'll be able to use "3 Translate" as a "Behavior" in The IVTable Wizard (UPR Tools) to "translate" FimsInLast4 into a UserFactor.

From there, it would be a matter of assigning your UserFactor a slot number in your SQL-F-Factor Setup.

-jp

.

~Edited by: jeff on: 4/26/2010 at: 2:22:36 PM~

JCapper Message Board

JCapper 101 -- Table Schema question

JCapper 101
-- Table Schema question