Calculation "Field 2 = Field 1" remains empty

[JDH]

This question modifies a post I entered yesterday under Portals. I believe I have narrowed the problem down to this issue.

 

I have a series of four fields set to function as cascading menus. Obviously the cascade depends upon the value entered in the first field. While I can enter a value into that field manually or via a simple drop down menu, there is another field in a related table that always has the value that needs to be displayed in this field.

 

I want the value entered in Field 1 located in Table 1 to be auto entered into Field 2 in Table 2. Again, the two tables are related so that I can create new records in Table 2 via a portal Table 1.

 

What I have done so far

I have defined Field 2 as a calculation field with the formula Field 2 = Table 1::Field 1

 

The Problem

While this calculation does work, it does not do so reliably. There are a number of occasions when Field 2 remains blank, even though there is a value entered into Field 1.

 

I had assumed (apparently incorrectly) that the formula used would essentially cause Field 2 to actively mirror the contents of Field 1. But it appears that some action must take place to trigger the calculation, and I am at a loss to know what it is. I did discover that I can trigger the auto-enter by manually entering something in the second field in the cascade sequence, but this defeats the purpose of the cascade.

 

Is there a better way to mirror or auto enter contents between fields?

 

Thanks for your help!

JDH

[AHunter3]

Table 2 doesn't automatically obtain a related record just because Table 1 did so. The related Table 2 record comes into existence -- assuming that you've checked the checkbox for "allow record creation via this relationship" on the Table 2 side of the relationship definition -- only when you set some field in Table 2 to some value. That field can't be a calculation field (you can't set a calculation field to a value) and it can't be the field (or fields) used in the relationship def itself, but it can be pretty much any other field.

 

So if Table 1 is Invoice and Table 2 is InvoiceLIneItems, and InvoiceLineItems has these fields in it: Serial Number (auto-enter), Foreign Serial (connects it to Invoice), Date Created, Quantity, Item Code, Description, Price, Extended Price (a calculation field), Date Modified, and Notes, you can cause a Table 2 record that is related to the Table 1 record to spring into existence by setting a value to any of the following fields: Date Created, Quantity, Item Code, Description, Price, Date Modified, Notes.

 

If you have a calc field Field 2 defined as Table 1::Field 1, it will have value in it instantaneously as soon as the record is created by setting one of the other fields to a value.

[JDH]

Sorry, I'm fuzzy on this. Just want to make sure I understand you correctly.

 

That field can't be a calculation field...[/Quote]

 

By "that field," are you referring to a field in Table 2 that "receives" the value from Field 1 in Table 1? If so, then I'm not clear on how this corresponds to the statement made in the last paragraph ("If you have a calc field Field 2 defined..."

 

You can cause a Table 2 record that is related to the Table 1 record to spring into existence by setting a value to any of the following fields: Date Created, Quantity, Item Code, Description, Price, Date Modified, Notes.

 

If you have a calc field Field 2 defined as Table 1::Field 1, it will have value in it instantaneously as soon as the record is created by setting one of the other fields to a value.[/Quote]

 

I'm assuming that the phrase "setting a value" and "defining a calc field" are essentially the same action?

 

If so, do these terms/action refer to

 

The formula that is entered in the Specify Calculation box? If so, would the formula be different from the Field 2 = Table 1::Field 1 that I have used? If "setting the value" refers to something else, then I'm not sure what you mean. OR

 

Inserting a field in Table 2, then define it as Table 1::Field 1 in the Field/Control Set up box? If this is what you mean, then it does succeed in getting the value to show, but it still doesn't trigger the cascading menus, because they are based on the fields that are native to Table 2. The menu hierarchy is based on the definitions created in Define Value List > Use values from X > Use values from first field option.

 

I think I'm making this harder than it needs to be, but I'm just not seeing it. Thank you for looking at this!!!

 

JDH

[AHunter3]

I'm assuming that the phrase "setting a value" and "defining a calc field" are essentially the same action?

 

No, not remotely so.

 

Settting a value is doing this in a script:

 

Set Field [someTable::SomeField, "SomeValue"]

 

or, manually, by clicking your little cursor into the field SomeField and typing in "SomeValue".

 

Defining a calc field is not done in a script, and can't be done manually, it's what you do in Field Definitions (File Menu, Define, Database, Fields).

 

Setting a field is a VERB. Something you DO as part of everyday database use & data entry. SET the field "Notes" to "I am leaving some notes here right now".

 

Defining a field is establishing your NOUNS. Defining your structure. This calculation field simply IS (does not get SET to, just plain old "IS") the result of multiplying the quantity times the price. I call it "Extended Price". I cannot SET it in a script and I cannot type into it and change its value. It's a calculation field. Its value is always already whatever the quantity is times whatever the price is.

[JDH]

You mean I have to know GRAMMAR to understand FMP? I'm sunk!!

 

I'll have to work through your explanation. Thanks for getting back to me.

 

JDH