variables & calculated field names

[sawhillr]

I have a sequence of scripts that needs to repeat itself about 400 times. Each of the 400 versions should look at a different related field. A sample of the field name would be MLB01, MLB02, MLB03, NFL01, NFL02, NFL03, etc. In each case, the field name is called on multiple times within the script steps. I would like to build a variable to handle this so that I only need to set the MLB/NFL portion separate of the 01/02/03 portion.

[color:blue]SetVar ($sport="MLB")

SetVar ($num=1)

SetVar ($fn="Sport::" & $sport & "0" & $group)

[color:blue]$sport will stay the same for 20 iterations while [color:blue]$num will change every iteration. The [color:blue]$num change can be handled with a counter that resets [color:blue]$num at the end of each iteration.

So, my problem is how to use a calculation to create a field name that acts like a field name. If I do as listed above, [color:blue]$fn will result in text that reads [color:purple]"Sport::MLB01" and not an identifier that calls the contents of the field.

I have read several posts (here and elsewhere) that talk about this but have found no success with any of them.

Can anyone help?

Many thanks

Roger

[bruceR]

Anytime you have field names like something01, something02, something99, MLB01, MLB02, MLB03, that it's a classic indicator of a basic design problem. Spend your time fixing the design and you won't have this problem. Start by telling us more about the database and its purpose.

[sawhillr]

wouldn't surprise me if I suffer from basic design issues, have been self taught and my needs have grown sporadically over the years.

Database 1 is a sports score DB that stores daily scores (up to 20 games per sport per record) for 6 different sports. Each field (MLB01, MLB02, etc) contains blob of data representing the 2 teams and their scores.

Database 2 is a aggregator of data from various other DBs including the sports DB. For reasons too long to go into, I need to use a script that gets the score data from the sport DB and uses SetField to enter the data into the final fields which will be used by a variable data program to populate a document in a page layout program.

bottom line, each game's data is currently in separate fields and now I need to get that data into the aggregator.

Let me know if you need any more info.

Thanks

[comment]

If I do as listed above, [color:blue]$fn will result in text that reads [color:purple]"Sport::MLB01" and not an identifier that calls the contents of the field.

Have a look at the GetField() function.

I too suspect your structure is far from optimal - but I didn't understand your explanation well enough to make a recommendation.

[bruceR]

"Each field (MLB01, MLB02, etc) contains blob of data representing the 2 teams and their scores."

None of these fields should exist.

Instead you should have game records with contents something like this. Just a starting point. Games table with fields GameID, Date, Location. For a given GameID there would be two join records, each with:

GameID TeamID Score

[bruceR]

Hate to be an enabler but for this:

$fn will result in text that reads "Sport::MLB01"

You can use evaluate($fn) or as mentioned by Comment, getField($fn)

[sawhillr]

many thanks everyone. I freely admit my brain is still back in the days of the original flat file DB mentality of Filemaker. I have used it here and there over the years and have been thrust back into it to fend for myself. This project is probably too far up the river to go back and restructure but I appreciate the advice and will look into changing my ways going forward.

For those that suggested GetField, I had tried it but with no luck, will go back and see if I missed something and/or try Evaluate.

Again thanks for your patience and support

[bruceR]

LIke this.

eval.fp7.zip

[bruceR]

many thanks everyone. I freely admit my brain is still back in the days of the original flat file DB mentality of Filemaker. I have used it here and there over the years and have been thrust back into it to fend for myself. This project is probably too far up the river to go back and restructure

Probably gonna be a whole lot easier to fix than do all that scripting.