Looking up values

[pjp]

I have a table called EvaluationTranches in which I store summary data about the valuation of groups ("tranches") of patents. The patents receive a rating on one of five levels: Excellent, Good, Fair, Poor, or No Value. I hold these five rating levels in a another table, called RatingLevels.

For each record in the EvaluationTranches table, I have a repeating field called ratingLevelsRep[5] in which I would like to store the each of the different rating levels mentioned above (Excellent, Good, etc.) as text values.

I want to use a lookup instead of just having a relation to the RatingLevels because this is transactional data that must be kept even if the RatingLevels table changes in the future.

I am trying to work out how to do the definition for the lookup of these fields. Can anyone help me with an elegant solution?

Many thanks!!

One note: I don't necessarily have to use a repeating field in the EvaluationTranche table. If necessary, I can define 5 different fields.

[comment]

I am not sure I fully understand this. Do you select the actual rating level as a text value, or as number pointing to a record in RatingLevels?

If the former, you could have a single text field auto-enter a calculated result of

ValueListItems ( Get ( FileName ) ; "YourValuelistName" )

That only takes one field, and it will keep working if they add more levels to the rating system.

If you are using a pointer, then you can keep the history in the RatingLevels table. If the system ever changes, you add new records for the new levels, and mark the existing ones as obsolete - but you keep them.

[Søren Dyhr]

I'm strugling with this here:

I want to use a lookup instead of just having a relation to the RatingLevels because this is transactional data that must be kept even if the RatingLevels table changes in the future

Does it mean, that the scale larter might become more finegrained???

--sd

[pjp]

Thank you both for your fast response and insightful questions. I will try and explain the issue in more depth.

This is part of a larger system that I am developing. In other parts of the system, for example where a technical expert must evaluate an individual patent, the expert must choose a rating level from drop down value list, which is driven from records of the RatingLevels table.

However, in this part of the solution, where we are preparing reports to the company owning the patents, we may want to adjust our terminology so that it is the same as the terminology of the owner. Therefore, I wanted to be able to initially populate these fields with the default text rating levels, but then allow the user to over-ride these text entries if necessary.

Additionally, as you have hypothesised, the system may gain greater granularity (or more likely we may change the default names for the rating levels) in the future, and I do not want these new defaults to over-write the data for existing reports that have already been sent out to patent owners, but I would want the new defaults to be written into the fields for new reports.

I hope that this has clarified the explanation.

Thank you for the suggestion on using the ValueListItems function. I will investigate this. There are two issues that I see with it: (i) I don't like putting a value list name hard coded inside a function call, because someone could easily edit the value list name in Filemaker without editing the function call, causing an error (ii) this puts all the values in one variable in a delimited list; I wanted them in separate variables or separate repetitions of a variable for using in merge fields in the report to the patent owner.

[comment]

Well, if you don't want to use the value list name, you could create a "x" relationship to the RatingLevels table, and get the list of all values by using the List() function. Then, if you really wanted to, you could put each value in a separate repetition of a calculation field. I can't see any advantage in doing that, especially if the number of values may change at some point.

Storing the values directly into a repeating field is difficult, because repeating fields do not auto-enter well.

Using different terms to describe the same value seems to me a separate issue. Perhaps the file I have posted here could give you an idea about that.

Whatever you do, if someone who don't know what they are doing are allowed to get into the system, they could break that too.