13 Replies Latest reply on Jan 5, 2014 10:53 AM by MichaelShearer

# Finding a match

### Title

Finding a match

### Post

I'm trying to find a match in field "sbc20" that matches a variable in field "Winner 1". When it does match, put the number 20 in field "M20". All fields are on the same table. I had it working but I didn't use a variable. I'm thinking this may be the way to go.

When it is done with "sbc20" it will need to go to "sbc19" and put 19 in "M19" and so on to "M10". It's probably an easy one but it has me stumped.

Thx

Mike

• ###### 1. Re: Finding a match

I'm trying to find a match in field "sbc20" that matches a variable in field "Winner 1". When it does match, put the number 20 in field "M20". All fields are on the same table.

I'm afraid that description is missing some detail. The value in sbc20 matches a value in field "Winner 1" in a different record of the same table. And then you put 20 in the field M20 of what record? the record with the value in sbc20 or the record with the matching value in Winner 1?

• ###### 2. Re: Finding a match

Sorry 'bout that

Winner 1 is the winner of the game (football). Since it can be different teams Winner 1 is from a value table ( that would be the variable). Each game is worth points from 0 for the winner to 20 for the looser based on which field (sbc20 - sbc10) it is entered in. The program needs to check Winner 1 against sbc20 if they match put 0 in M20 if it equals the looser 1 put a 20 in M20. Then go to box sbc19 if sbc19 equals Winner 1 put a 0 in M19, if it equals loser 1 put a 19 in M19 etc... thru sbc10.

All records and fields are on the same table. I would think the best way to do this is have FM find all records that has the winner in sbc20 and put 0 in M20 and then find all records with the loser in it to put a 20 in M20. Then do the same thing for sbc19 etc...

But... it might be better to have FM go to each sbc20-sbc10 and check if sbc20 is a match with W1 put a 0 in M20. If it is a match with L1 put a 20 in M20. If sbc20 = 20 do nothing. I have put numbers in each sbc representing the points that field is worth.

Oh, just to be clear, each record contains all the fields

• ###### 3. Re: Finding a match

Since it can be different teams Winner 1 is from a value table ( that would be the variable).

But you said: "All fields are on the same table." In both of your posts

That would seem to contradict what I have just quoted.

just to be clear, each record contains all the fields

You you truly have just one table, this is the only possibility. But this is not at all clear that this is the case.

If you have a single table and field 2 needs to show a particular value if the value in field 1 has a specific value, then you do not need any script or any finds as Field 2 can be defined as a calculation field to show different values depending on the value of Field 1.

• ###### 4. Re: Finding a match

Now I'm confused and I'm looking at the table. LOL

I've attached a picture of the page in layout mode.

Right now I go into each sbc field that has a pick and put a letter in it. Then I have a script that finds the letter A and replaces it with team A. Then it does B etc...

Each "sbc" field has a number in it to represent the number of points it is worth (20 down to 10. The team replaces the number. When the games are over I input the winners and losers. I need FM to check the "sbc" content against the "Winner" and "loser" and insert the number of either 0 (when it is a correct pick) or the value of the box (when it is a wrong pick).

I already have the scoring set up. It adds the "M" fields and the Super Bowl Score over or under amount (using an absolute difference).

I'm open to making other tables to make this work.

• ###### 5. Re: Finding a match

Is it possible that you have just one SBC field with multiple repetitions?

Are you selecting the winner from from Game 1 and putting it in SBC 20, the winner from Game 2 into SBC 19 and so forth?

If so, there seem to be more SBC fields than you have games.

And yes, this looks like something that needs several related tables instead of a single table. Properly set up, selecting the winning team for a specific game should not need any script at all.

But I'd need to understand a lot more about what you are trying to do with this layout before I could describe that different set up in detail.

• ###### 6. Re: Finding a match

Here is the way it works:

Let's make you and me a member. The Pats are playing the Rams. I think the Pats will win so I pick them in sbc15. You think the Pats will win, you pick them in sbc20. If the Pats win I get zero points in M15 and a "W" is placed in sbc15. You are given 0 points in M20 and a "W" is placed in sbc20.

If the Pats loose an "L" is placed in sbc15 for me and 15 points are placed in M15. You will have an "L" in sbc20 and 20 points in M20.

In week 1 of the playoffs there are 4 games so 4 of the 11 fields are used. All 11 sbc fields are used by the time the Super Bowl is played.

• ###### 7. Re: Finding a match

But what is the significance of "15"? why would this data be put in fields with 15 or 20 in the field name? Why not sbc16?

Just trying to figure out the rules. (And I don't know if you are running bets, a pool or some kind of fantasy football, but they are all something with which I am very unfamiliar so please don't assume that I know anything beyond the fact that we have teams playing football, the top ones make it into the playoffs and they finally produce two teams to play in the super bowl.)

That said, this really looks like data that should be entered into different records where you have them in different fields. That doesn't necessarily require a change in the appearance of your layout, but can make it much easier to control how data in one place affects the data that appears in another.

• ###### 8. Re: Finding a match

Not a problem. I dreamed this one up myself. You should see me explain it to people.

The sbc fields are worth corresponding points from 20 (sbc20) to 10 (sbc10). To begin with there is a number from 20 down to 10 in the sbc fields. This is so you can tell what each field is worth. I assign each team a letter. FM replaces the letter with the team name.

The M fields are not used until the games have been played. What I'm trying to do is get FM to find the winner of the game and put a 0 in the M field that corresponds to the sbc field (sbc20 goes with M20).So if you pick a winner in sbc20 FM will put a 0 in M20 and put a "W" in sbc20. If your pick is a looser FM finds the team and puts a score in the M field (20 in M20 etc), and an "L" in the corresponding sbc..

So it works like this: I receive your picks with the letters that represent the teams. Say I make the Pats team A in game one, and you pick the Pats to win in sbc12. This means you have bet 12 points that the Pats will win. But remember this is scored like golf. If the Pats win you are given 0 points. FM finds the field (in this case sbc12) and compares it to the winning team. In this case you pick correct so FM puts a 0 in M12 (which is just a container for the number) and places a "W" in the place of the Pats in sbc12.

If the Pats loose FM compares the looser and the Pick in sbc12 and puts a 12 in M12 and an "L" in sbc12. You have 12 points.

I'm going back east tomorrow and won't be back until the 30th, so I won't be able to work on this until I get back. I'll check in while I'm back there to see if you have it. If not I can try again.

Mike

• ###### 9. Re: Finding a match

Frankly, there are so many things there that I'd set up differently in terms of tables and fields that I'm at a loss to know where to start.

And I really don't have the time right now to try to figure this out to a point where I can offer specific suggestions on what to do differently. I can recommend that you invest some time in learning more about FileMaker and relational databases as a way to get a better grasp of the issues.

• ###### 10. Re: Finding a match

As you can see there are 11 "sbc"  fields. Each of them are  worth points from 20-10. This is where  the teams are picked. If   you  pick a team in box 20 you are betting 20 points they will win. If you pick the same team in a lower  "sbc" you are betting less points on them.

The "M" fields are where the points are placed.  If your pick is correct you are given 0 points and if your  pick is incorrect  you are given the points you risked.

I'm back on the 31st so I'll  be back on this.

• ###### 11. Re: Finding a match

Instead of fields, you could set up buttons--each set up with a script parameter to enter a different amount (the "wager") when tapped, but enters it into the same field each time. To "bet" 20 points, tap the button for 20 points and 20 is entered into the "wager" field. To bet 10 points, tab the button for 10 points.

A drop down list could also be used to specify the amount of points "wagered".

And having the amount wagered in a single field for any wagered point score will make your subsequent work with this data much easier.

• ###### 12. Re: Finding a match

I'm kinda starting over. Maybe if we take this in parts instead of the whole it will be easier.

I have two files, FBC and SBC Teams. I have two of the files related. Since no one but me has Filemaker I need to input the teams into the fields 20-10 for each club member. Those fields are in FBC. I type in A-H to represent the team they pick.

The teams are located in SBC Teams. I want the letters in the fields in FBC to be replaced by the names in SBC Teams.

So, FM needs to go to every field in FBC that has the letter "A" and replace it with Pats located in SBC Teams, field Team A. Then it needs to do the same with team B etc. In all records.

I hope this explains this part.

Thx

Mike

• ###### 13. Re: Finding a match

I got it to work. I had to add the Teams fields (A-H) to the layout then it was pretty simple. I also had to have it do a find all members before going to the next field.

So now I'm off to get the rest working.