In a database containing numeric fields with names such as StWk950, I need to calculate fieldnames in a text list to produce a report showing the values contained in these fields.
when the formula fieldvalue("StWk"+fvWeek) produces the field name StWk950 (because fvWeek=950). This repeats the first record’s value of 203 in the whole text list, while the same fieldname StWk950 hardcoded outputs each record’s value correctly (203, 53, 46 etc).
Using a formula to calculate a fieldname does not seem to work properly in the text list formula although the same formula works correctly in other circumstances, for example in the formula workshop.
I have rewritten this formula in every possible way I can think of and the best I can do is to get the top record’s value repeated.
The list just sees that as an expression, like the tab() functions in that formula. It doesn’t see a field name anywhere among it’s parameters. Maybe you could use a lookup( function, if you have a suitable key field.
Yes, lookup( worked! Thank you, Dave. I wish I’d asked before beating my brains out for the last few days.
It doesn’t see a field name anywhere among it’s parameters.
But forgive me, I am confused. It has found the value in the first record in the field in the formula, 203, so it must have resolved the fieldname. As I understand it, the contents of the Text List formula panel is used by Panorama X to build the array it uses to output the list. I’d expect the fieldvalue( formula to return the correct value in every element of the array, and not in the first record only.
I haven’t tested this myself, but I suspect it is showing you the value from the first record, because the first record was active at the time. If some other record were active, I think it would show you the value from that record.
