AnsweredAssumed Answered

How can I use a calculated field in a value list?

Question asked by jonasmn on Apr 21, 2016
Latest reply on Apr 21, 2016 by jonasmn

I have problem using a calculated field in a value list, FM tells me that it is not possible because the field can not be indexed due to it having a calculated value from another related table.


File 1 - table CROP, that contains crop data, such as Swedish name, Latin name, subspecies, etc.

File 2 - table BATCH, that contains crop data (from the table CROP in file 1 above), start date, harvest date, manager responsible, etc.

            table Z_BATCHESTOTREAT, a table with batches that will get some kind of treatment, such as chemical or biological treatment etc.

File 3 - the layouts, scripts, etc.

In File 3 I have a layout called BATCH where I enter the batch data each time a production of a new batch of crops (plants) starts.

In the same file 3 I have another layout called BATCHES TO TREAT, where new records are entered each time a new treatment is done to a batch. In this layout I want to have a value list where the user can select among the active batches. In order to see the correct batch, the user should be able to read the ID_batch and the Swedish Name + Latin Name + Subspecies. Since only two field can be shown in value lists, I have concatenated the Swedish Name + Latin Name + Subspecies (SSP) in the table CROP in the field z_ConcatSwedishAndLatinAndSSP.

In the table BATCH in file 2 I have the same field z_ConcatSwedishAndLatinAndSSP (calculation, text) that simply copies the value from table CROP to table BATCH. A relationship. This works fine!

Now to the problematic part. In the layout file, I have the layout BATCHES TO TREAT I want to value list that displays the different batches. When I select the value list ONLY to contain the ID_batch, the list is correct and works fine. But when I also select that the list also should display a second field with data from the field z_ConcatSwedishAndLatinAndSSP, FM says that the value list will not work, because the field is not indexed, and the reason for not being indexed is that it is related field.


Is there a workaround for what I want to acomplish?




Jonas Möller Nielsen