Dynamic Updating and Storage of Value Lists

[rexproctor]

Hello Everyone and Anyone,

I am a newbie to FileMaker and I am a little lost with value lists. I believe what I am trying to do is simple, but the explanation is not. So, I will do my best.

I am creating a template data entry interface for scouting football games. Each game will be a database in and of itself. Each play(record) in the game entered has a set of fields like down, distance, hash, formation, etc. I would like to have formation be a pop-up list that dynamically updates as new formations are scouted. The new formation is then added to the value list and can be selected when it appears in the game again. I have gotton this far, but I run into trouble when I save and clear the database to scout the next game, my value lists are empty. I would like to create a "master" value list for each field in a "Master" value database. So, each week, we can use these pop-up values over and over, thus increasing the accuracy of the data entry while constantly building these lists in the background.

I hope this makes sense.

Thanks a bunch.

[DanBrill]

One way would be to have your value lists stored in another table that you don't clear out at the end of the game.

By why are you starting with a new empty database every time? I'd want to keep that information so I could make cross-game comparisons and watch for trends, etc. Just add fields for the date and location of the game and then each game has a unique identifier.

Just a thought,

Dan

[rexproctor]

Thanks for replying Dan,

Storing the list in another table is exactly what I would like to do, but I can't figure out how to get the values to be stored in another table where the pop lists are populated and displayed in the data entry screen part.

You are quite right in the trend analysis thoughts. I shouldn't say that I want to clear the games, but I want to clear the data entry template. I was playing with the unique identifier idea this morning. This is definitely the way the way to do it.

Thanks,

Rex

[DanBrill]

Ah, well this has to do with the way you create your value lists:

Go to Layout mode

Click the Value List Icon in the toolbar

Choose New

Select 'Use Values from Field'

Select a table

Choose a field

The value list will consist of all unique elements in that table::field. In FM6 I have a file I just call CustomValueLists.fp5 with a series of fields, each one a different value list. The file itself has 51 records, which is the longest value list -- 50 states plus Wash. DC.

But with FM7's multi-table files -- and a limit of 1,000,000 tables per file -- it makes sense to have each value list have its own table. So if you want a value list of formations, create a table called formations, and put in one field, FormationName. So create your value list on Formations::FormationName.

On your layout you'll have a formation field. Set this field to have a drop down menu based on your formation value list. The user enters this field and selects from the list.

Now the next part of your question is how to have a new formation added to the value list as the user adds encounters a new one. One way to do it is to have a button next to the field that says 'add new formation.' A script then opens a dialog box, gets the name of formation from the user, stores it as a global (gNewFormation) creates a new record in your formation table, and sets the FormationName of the new record to gNewFormation.

But there is probably an even cooler way to do it now. FM7 Developer is shipping with a plug in that will let you run a script when exiting a field. You could trigger a script that compares the value of the Formation to the FormationName value list. If the formation name doesn't appear in the value list, then it could be added automatically as a new record to Formations::FormationName. I've never tried this, but there is no reason it shouldn't work. All part of the new world of FM7.

Cheers,

Dan