Problem while defining calculation field

[renev]

I have a problem in defining a field hope someone leads me in right direction.

Items are imported to our database. We have 2 tables to accomodate this information.VPN table and MPN table

One VPN can have multiple MPNs. VPN and MPN tables are joined using VPN.

In MPN table there is a column current cost.

Now I want to have a column in VPN table called Target Cost.

The conditions for Target Cost:

1.It should take the minimum cost of the MPNs.

2. In any case the minimum cost is changed(current cost of the MPN updated by User) and became higher than the existing one it should retain the old value, otherwise if it is minimum first condition applies.

I created a column Target Cost in VPN table with following unstored calculation

If ( Est Cost < MPN table::Minimum Current Cost ; Est Cost ; MPN table::Minimum Current Cost )

and I get the value for Target Cost as question mark(?)

Where I'm gone wrong?

[comment]

Your calculation, as far as I can understand it, does NOT follow your description. You say you have a field named Current Cost in the MPN table - but the calculation mentions a field named Minimum Current Cost and another field named Est Cost in the VPN table of which we know nothing.

[renev]

Sorry..Might be my explaining way is not correct. Let me put it again.

In VPN table i have following columns

VPN

Item Description

Commodity

Type

Est Cost

In MPN Table the columns will be

VPN

MPN

ManufacturerCode

Current Cost

Minimum Current Cost(summary field, minimum of current cost)

Since one VPN can have multiple MPNs minimum current cost is minimum current cost per VPN.

Now in VPN table I have Est Cost which should take the minimum current cost from MPN table. If someone changes current cost of that MPN which is having minimum current cost and the minimum current cost now becomes higher than previous I should retain the previous value.(My manager in fact wants to manually update the Est Cost sometimes )

How can I achieve this?

[comment]

It cannot work exactly as you describe. An unstored calculation is unstored, it does not keep any data. It cannot compare its current result to the previous one, because there is no previous value. OTOH, a stored field will not update automatically when records in a related table are modified.

I would add a second field to the VPN table, an unstored calculation =

Min ( Est Cost ; Min ( MPN table::Current Cost ) )

This solves the problem of what to display. To populate the Est Cost field to its initial values, you'll need to run a script. However, if your manager manually updates the Est Cost to a value HIGHER than the calculated minimum, this solution will ignore it.

[renev]

Thanks for taking interest in my problem.

But right now the field is unstored calculation, how can I make it updateable field?

I get error message saying field is not modifiable.

[comment]

You cannot update the calculation field - but you CAN modify the Est Cost field, and the calc will reflect that.

[renev]

Thanks..I understood what you are trying to say.

But then I need to keep two fields in the layout.

One is like calculated estimated cost another one is estimated cost which user can modify.

Is that only option?

[comment]

But then I need to keep two fields in the layout.

Yes, but you don't need to show both of them. You could place the stored field behind the calculated one, and make the calc field non-enterable. Clicking on the displayed number will bring the stored field to the foreground for editing.

Is that only option?

It's almost never 'the only option' with Filemaker. You could also run a script every time a cost is modified in the MPN table to update the parent target cost. But you would need to make extra sure the script *is* run following such modification, and that the script is successful (e.g. that the parent record isn't locked by another user).

Another option would be to make the calc field into a button that updates the stored field and enters it. That would prevent the case of user seeing "150" but finding "200" in the underlying field.

However, looking at it from a user's point-of-view, I believe I would want to see the two numbers side-by-side. I'd even ask to color-code the cases where the estimate was too low in red, and the opposite cases in greeen.

[renev]

It is a good suggestion. I will work in that way to meet the requirements.

Thanks again.