Concatenated field from multiple related records - no result shows

[Inkling]

I am trying to populate a text field (ObjMeasAgg) by using a value list based on a text field in multiple related records (ObjMeas).

 

I created the value list and it is working fine. I defined the ObjMeasAgg field to auto-enter using the formula:

 

Substitute ( ValueListItems ( Get ( FileName ) ; "ObjMeas" ); "¶"; ", ")

 

with a text result and set indexing (storage) to 'none'.

 

I cannot get any results to appear automatically in the ObjMeasAgg field, although when I did a ReplaceContents calculation using the same formula, the result appeared as intended.

 

I feel there is something obvious that I must have overlooked, but can't for the life of me figure out what it is.

 

Any suggestions?

[David Head]

You say that you have a "value list based on a text field in multiple related records". What is the basis for that relationship?

 

An auto-entered calculation get triggered when the record is created. Does the basis for the relationship exist at that point?

 

It would be good to know more about your tables and how the list is defined.

[Inkling]

Thank you for replying - I am sorry it has taken me so long to get back to you.

 

Here are the details:

 

I have an object table (ObjTable) which includes an object measurements field (ObjMeas). ObjTable is linked to another table (ObjMeasTable) by the Object record serial number (ObjRecSerNo) matching a field in the ObjMeasTable (ObjMeas). Records in the ObjMeasTable are created via this relationship.

 

The information entered in an ObjMeasTable record is aggregated in the field ObjMeasTable:ObjMeasAgg (for example "4.5 inches [width]" is the aggregate of value, unit and type fields). ObjMeasAgg serves as the basis for a value list ObjMeasList which I want to concatenate and use to populate ObjTable:ObjMeas field, so the entry would look something like this: "7 inches [height]; 4.5 inches [width]".

 

I defined the ObjTable:ObjMeas field as a calculation field with a text result and used the following calculation: Substitute ( ValueListItems ( Get ( FileName ) ; "ObjMeasList" ) ; "¶" ; ", " ).

 

The result is that I get a question mark in the ObjMeas field. I checked the ObjMeasList value list and it works fine. I double checked the calculation formula: nothing amiss there. I checked the match fields - no problem there.

 

I must be missing something (probably obvious) but at the present I am stumped. Thank you for offering to help.

[Inkling]

I should add that I have also tried this with the result field defined as a text field with an Auto Enter calculation and "do not replace field contents" unchecked. In this case the field simply remains blank.

[AHunter3]

You say the value list itself works fine (you format a field with that VL and click on it and values do indeed drop down?). I assume you've carefully checked the name of the value list against the reference to it in ValueListItems () to verify that you didn't make a typo? And you say result type of your calc field is "text". Hmm, is storage set to "Unstored — calculate as needed"? (It should be, as should any calc field utilizing ValueListItems, unless the elements in that value list are never going to change. But the "?" makes me think this is not your problem. You should not get a "?")

[Inkling]

Thank you AHunter3!

 

I hadn't checked the "Unstored calculate as needed box" - when I did the results appeared.

 

Thanks again I appreciate your reply.