Calculate second value display.

[tmas73]

Hello,

I have a popup menu value list that displays only the second field (oil_supply). The first one is just a serial number (000203). I also have a different field that calculates ( Filter ( Proper (oil_supply) ; "ABCDEFGHIJKLMNOPQRSTUVXYWZ" )) and only displays the first 3 letters from that field. The problem is it will calculate the first value field (Serial Numbers) instead of the second (oil_supply).

Is there a calculation that uses the second value field instead of the default first one?

Thanks

[Vaughan]

You need to reference the second field through a relationship, or auto-enter it into the main record when the value is selected.

[tmas73]

Thanks, but maybe I need to reframe my question. The calculation

( Filter ( Proper (oil_supply) ; "ABCDEFGHIJKLMNOPQRSTUVXY WZ" )) should return the first 3 letters of the field "oil_supply". The problem is that the oil supply is chosen as second value entry only. So the field "oils_supply" shows the right information (not the serial), but the filter formula, just uses the first value field and not the second one.

Attached a little file!

Thanks for the help!

Value_Formula.fp7.zip

[Vaughan]

The value list displays the second value, but it actually enters the *first* value into the field. Put another copy of the oil_supply field on the layout as a plain edit box and see what is actually being entered into it.

Like I said, you need to reference the second field through a relationship, or auto-enter it into the main record when the first value is selected.

The calculation

( Filter ( Proper (oil_supply) ; "ABCDEFGHIJKLMNOPQRSTUVXY WZ" )) should return the first 3 letters of the field "oil_supply".

There is nothing in this calculation that suggests it should return the first three letters of anything. It's saying "change the capitalistion to proper, then strip out everything except for capital letters". It will probably return only the first capital letter of every word in the field.

To return the first 3 letters of a string you need:

Left( text ; 3 )

[Vaughan]

Here is your sample file, changed so that the value list is generated form a separate table (the self join just confuses things).

Note that the menu on the ValueID field displays the name of the city, but the actual value is the ID, a number.

The short name field has been changed to a calculation field that references the related value (not the ID).

The Value field is an auto-enter that looks up the value based on the relationship.

Value_Formula_2.fp7.zip

[tmas73]

I think I did not formulate my question right. The problem is the calculation (( Filter ( Proper (oil_supply) ; "ABCDEFGHIJKLMNOPQRSTUVXYWZ" )) ) is using the second field value instead of the first one which is a serial. Attached a small sample.

Thanks for the help!

[Vaughan]

There was no file attached to your last post.

The last sample file you sent seemed to have nothing to do with your questions: it didn't have a field called "oil_supply".

Did you look at the sample file I sent to you? It illustrates a lot of the concepts.

[tmas73]

Sorry, thats what happens if not enough sleep. I did not see your file attached, so I created one to show the issue. Yes it did not have the same field labels.

I looked at your sample and seems to work great but the calculation should show each letter in each word. So for a name like New Direction it should say "ND"

Sorry for the confusion.

[Vaughan]

"the calculation should show each letter in each word"

That's very different from "should return the first 3 letters of the field" that you said earlier.

To get the first letter of the first three words, try this:

Left( middlewords( oil_supply 1 ; 1 ) ; 1 ) &

Left( middlewords( oil_supply 2 ; 1 ) ; 1 ) &

Left( middlewords( oil_supply 3 ; 1 ) ; 1 )

Edited October 21, 2008 by Guest