VL calc field

[bcooney]

I've spent enough hours on this to know that I'm not "seeing the forest for the trees."

Attached is a sample file. I want Results::RecProducts_c to equal the value list "RPs". It works fine on the first Result record, but not on the second. What obvious thing am I missing?

I'm not using 8.5's list function, bcs I'm still in 8.0v3 and so is my customer.

TestValueList.fp7.zip

[comment]

Other than making your calculation unstored, I think you have it.

[bcooney]

Thanks for taking a look, comment. The good news is that setting the calc to be unstored fixes the problem in the sample file. Unfortunately, it doesn't in the "real" file.

At least I know my approach is sound and it's just something peculiar to the setup I have in the real file. Oddly, enough, if I put a child record from RecProducts on my Results layout, it shows the first of the child RecProducts. Also, if I set a field to use the value list (just to see if it's calcing ok), it's there and recalculating for each TestResult record. I think I'm safe to assume that the relationship is sound. I just can't create a calc field that is equal to the value list.

I need to walk away for a day. But if anyone has some common gotchas, similar to the unstored calc, pls let me know.

[comment]

Perhaps if we knew what you're trying to achieve here... That file is no hint. Why aren't there any portals?

[bcooney]

The sample file is just a piece of my system. I'm simply trying, for each testresult record to show the related recommended products.

In the system, there is a report that shows each testresult, several other fields for each test result and all the recommended products. The recommended products table is built by a report script. I don't think that mechanism is relevant to my problem.

I should point out that I'm updating an existing solution that had this mechanism working just fine. I didn't have a calc field, but I did set the recommended products field in Results to a value list using set field in the report script.

Here's what I changed:

- I needed the Recommended product names to be preceded by a bullet. So, I've created stored calc ProductName_dsp in Products. So, when a record is created in RecProducts, the Product Name is auto-enter calc to RecProducts::Products by ProductID to the ProductName_dsp.

That is all I changed. I have tried not using the calc field ProductName_dsp field and just sticking with the text field "Product Name", but same results.

I'm starting to worry that my file is corrupted. I see the related records from Results to Recommended Products, I can see the value list if I apply it to a field in Results, but cannot create a calculation field in Results that equals the value list. i can do it in my sample file, so the technique is solid.

[comment]

I'm simply trying, for each testresult record to show the related recommended products.

I think a portal is the best tool for this - in Browse mode. A printed report, OTOH, would be built from the RecommendedProducts table, with the TestResult data showing in a sub-summary part. And you don't need to calculate the product name in RecommendedProducts - it can be fetched from the related record in Products.

[bcooney]

Thank you so much for your help. The final report contains several sections and is best created from the Results table. It's an unusual report, bringing in data from several child tables to results. Therefore, I use value lists to populate text fields.

I'm trying today to use a one step script to set a new field "Test" in Results to any of the value lists that I have in this system. Some are related value lists, some are custom lists like "1/0". I can't get any of them to populate my text field. Weird. If I simply use set field Test text field to "1", that works.

I can't wait to report what stupid thing this was...

Update: Shouldn't I be able to use the Data Viewer to see a value list. If I create a value list, "1/0" and put the expression, ValueListItems ( Get (filename); "1/0") in the viewer, I see no values. Should I be very worried?

Edited March 4, 2007 by Guest

[comment]

Yes, union reports are always a problem - though in version 8 and up, sliding portals can provide a satisfactory solution in many cases.

What is your actual file name? I recall some problems caused by file names containing extra periods.

[bcooney]

Yes, it's a union report, and what a sense of achievement is was when I finally got it to work. I wish FM had the ability to set a field to grow as needed rather than slide up. As it is, bcs of many text field essays, I am always worried that something is going to get cut off.

Well, I recovered my file, and now my value lists are calculating OK, they display in Data Viewer and I can set a field to equal one. Interesting, I could set a popup to use one and that worked fine, but I couldn't use set field or create a calc that equaled any value list. I've always heard that a recovered file is not fully fixed. Any advice? (Please don't tell me to go back versions. Who knows when this corruption happened)

[bcooney]

BTW, my file name did have a period in it (filename 1.04b2). This would affect value lists?

[comment]

Please don't tell me to go back versions.

I can't speak from personal experience, but that seems to be the universal opinion here.

[comment]

my file name did have a period in it (filename 1.04b2). This would affect value lists?

I don't think it would affect the value lists themselves, but it trips up design functions that rely on the Get(Filename) function. If you enter the actual filename as a parameter in the ValueListItems function, I believe it will work.

[bcooney]

That really gives me hope that this wasn't a corruption issue, but rather that file name. Funny, I usually do 1_04b1, but got lazy this time.

[comment]

I ran some tests, and my conclusion is this:

If your filename is "My.File.fp7", the expression Get (FileName) returns "My.File".

However, when you use Get (FileName) as a parameter in any design function, the expression is truncated to "My".

This can lead to some 'interesting' artifacts:

Suppose you have two files open, one named "My.File.fp7" and another named "My.fp7".

A calculation field in My.File.fp7 defined as:

ValueListItems ( Get (FileName) ; "MyValueList" )

will return the items of a similarly named value list IN THE OTHER FILE.

ValueListItems ( "My.File.fp7" ; "MyValueList" )

returns items of the list in My.File.fp7.

ValueListItems ( "My.File" ; "MyValueList" )

returns items of the list in My.fp7.





It seems that only the last period is truncated:

ValueListItems ( "My.Fi.le" ; "MyValueList" )

returns items of the list in My.Fi.fp7.

Edited March 4, 2007 by Guest
Added more examples