Multiple SetFields and Relationships problem

[Jim McKee]

I am having trouble setting up relationships which will be used in SetField script steps in a Lotto number picking solution. Only the relevant fields to the problem I'm having are listed below:

Two Tables:

History (complete historical lotto results - currently 367 records)

Fields:

Ball1 (the number drawn for Ball1)

Ball2 (the number drawn for Ball2)

Ball3 (the number drawn for Ball3)

Ball4 (the number drawn for Ball4)

Ball5 (the number drawn for Ball5)

Ball6 (the number drawn for Ball6)

BonusBall

Frequency (49 records, 1 for each ball number)

Fields

number (the ball number)

win_hits

bonus_hits

A script imports a data file from the lotto site into the History table (works great), and zeros-out the win_hits and bonus_hits fields in the Frequency table (works great). I want the script to then step thru each record in the History table and increment the win_hits field in the record in the Frequency table that matches the number in the Ball1 field, then the Ball2 field, Ball3 field .. Ball6 field. Finally, increment the bonus_hits field in the record in the Frequency table matching the number in the BonusBall field. So, I believe we're talking about 7 relationships here: 1 each from History::Ball1 -> Frequency::number .... History::Ball6 -> Frequency::number, and a seventh relationship from History::BonusBall -> Frequency::number.

I'm performing this operation from a layout in the History table (call it "History") which simply shows records from "History".

No matter how I structure the relationships, I get an error when the SetField steps begin: "The operation could not be completed because the target is not part of a related table."

My last attempt was to create 7 TO's of the History table and a matching set of 7 TO's of the Frequency table, with each pair linked on equality from a single History::Ball(N) field to the Frequency::number field.

History Ball1::Ball1 --> Frequency1_number

History Ball1::Ball2 --> Frequency2_number

History Ball1::Ball3 --> Frequency3_number

History Ball1::Ball4 --> Frequency4_number

History Ball1::Ball5 --> Frequency5_number

History Ball1::Ball6 --> Frequency6_number

History Ball1::BonusBall --> FrequencyBonusBall_number

So:

SetField [Frequency1_number::win_hits ; Frequency1_number::win_hits + 1

SetField [Frequency2_number::win_hits ; Frequency2_number::win_hits + 1

SetField [Frequency3_number::win_hits ; Frequency3_number::win_hits + 1

SetField [Frequency4_number::win_hits ; Frequency4_number::win_hits + 1

SetField [Frequency5_number::win_hits ; Frequency5_number::win_hits + 1

SetField [Frequency6_number::win_hits ; Frequency6_number::win_hits + 1

SetField [FrequencyBonusBall_number::win_hits ; FrequencyBonusBall_number::win_hits + 1

I've not been able to get my brain around what's not working. What is the correct way to structure the relationships to make the SetField operations work?

TIA for your input.

[-Queue-]

The main problem I see is that you're relating the contents of each ball field to the ball field number in the Frequency table. Say, for example, Ball1 has a value of 6, this will relate to Frequency::number when number is 6, not 1.

[-Queue-]

I think you can do this with only one relationship, from a calculation field GetAsNumber(Get ( ActiveFieldName )) related to the Frequency::number field, and loop through a layout containing only the Ball# fields. But I can't get it to work in the trial version. I'll try later on v2 at home.

You said you have 49 frequency records. Are there actually 49 balls and not merely 6 with a bonus one?

[-Queue-]

Okay, this one works. You just need a global to hold the numeric result of the current field name. Then you only need one relationship to populate the Frequency fields.

I didn't do anything with the bonus ball, btw, just the numbered ones.

Lotto.zip

[Jim McKee]

hey Queue

Thanks.

What's got me really stumped here is that in FM6, I'd simply set up 7 relationships back to the number field in Frequency, i.e., one from each of the 7 Ball fields in History. Why this won't work in 7 has me completely baffled. I shouldn't need to use GoToField[Next] steps. The data is all there in the 7 fields, so the relationships back to the "number" field in Frequency should work.

Another thought I had is that maybe I don't understand the whole issue of "context" enough, although when the SetField steps are called, the script is on a layout in History which is not controlled by any relationship .. just the bare, unadulterated History records. So, it should work.

This is messing with the few brain cells I got left. Seriously, my gray matter is twisted in a knot.

Any other suggestions? Thanks!

[-Queue-]

Jim, did you read my first comment? You wouldn't just set up a relationship from each ball field to frequency. The value within each ball field would have to match a value in the frequency field, which wouldn't necessarily be the correct related record. It isn't the field names you're matching; it's the data in those fields.

So if Ball3 contained 4, then it would be related to the record in Frequency containing 4 in its number field, i.e. Ball4. This is not what you want, unless you are relating a Ball Number field in History that contains the correct number. This is why I set up the single relationship based on the name of the field, which is much easier, I think.

[Jim McKee]

Queue ..

Thanks again.

I guess I didn't explain myself well enough.

The Frequency table has 49 records .. i.e., 1 record for each ball number. Think of the field i've named "number" in the Frequency table as an ID field.

Record #1: number = 1

Record #2: number = 2

.

.

.

Record #49: number = 49

So, whatever value from 1-49 appears in whichever of the Ball1, Ball2, Ball3 ... BonusBall fields in records in the History table, that number does in fact have a matching record in the Frequency table. Think of this as if you were updating an Inventory file from Invoice Items. Each Item in the Inventory table has a unique ItemID. Each record in the Invoice Items table carries the ItemID of it's parent record in the Inventory table. So when you're updating Inventory availability, you can use a SetField operation to decrement the InStock value in Inventory. I'm doing the same thing, except, in the History table I have 7 fields, each with its own relationship back to, let's call it, a "parent" record in the Frequency table. In FM6, the steps would be:

Go To Record [First]

Loop

SetField [ball1 to Frequency_number::win_hits, Ball1 to Frequency_number::win_hits + 1}

SetField [ball2 to Frequency_number::win_hits, Ball2 to Frequency_number::win_hits + 1)

SetField [ball3 to Frequency_number::win_hits, Ball3 to Frequency_number::win_hits + 1)

SetField [ball4 to Frequency_number::win_hits, Ball4 to Frequency_number::win_hits + 1)

SetField [ball5 to Frequency_number::win_hits, Ball5 to Frequency_number::win_hits + 1)

SetField [ball6 to Frequency_number::win_hits, Ball6 to Frequency_number::win_hits + 1)

SetField [bonusBall to Frequency_number::bonus_hits, BonusBall to Frequency_number::bonus_hits + 1)

Go To Record [Next, Exit after last]

End Loop

I hope this is clearer than my original post.

My hunch here is that there is still something I'm not understanding about how relationships work in FM7.

Thanks a lot, Queue.

[-Queue-]

Ohhhh, so you're updating the frequency of the number selected, not the Ball#. My bad. That makes much more sense. In that case, the attached works, too. I'm not sure why your relationships aren't working. I'd have to see the file for that.

Lotto.zip

[Jim McKee]

Queue ...

Just as I suspected, the problem was related to the context of the layout from which the SetField steps are performed.

The current layout must be in the context of the table occurrence of the "left key" (in FM6 parlance). So, in my situation, it cannot simply be from any layout with fields from the History table.

I set up a table occurrence pair based on your suggested use of a relationship of a History_n_number global -to- Frequency_number. As soon as I created a new layout which uses the context of the "source" key (the TO from History in the relationship), everything worked great!

FM7 is more and more feeling like a 3-dimensional chess game. Ugh!

Queue, thanks for all your help on this. Much appreciated.