circular calculations

[mse]

Hi everybody!

Excuse my novice's ignorance, my problem is probably simple and have been solved long time ago.

I am contemplating a kind of chemical notebook by modification of the excellent free FileMaker Solution Framework <<ftp://ftp.apple.com/filemaker/solutions/FileMaker_Solution_Framework.sit>>

Standing on the terminology used therein, there is a many-to-many correspondence between data in two database files, Invoices (portal with Quantity, Price, Amount, etc.) and Products (Price, Default Order Quantity, etc.), and a join file LineItems where calculations are performed. The configuration allows calculation of "Amount from the Quantity" (= Price x Quantity) and "Amount from the Price" (= Price x Quantity) by modification both Quantity and Price values without affecting the data in the Product db file. Suppose I'd like to know also "Quantity from the Amount" (=Amount / Price) and "Price from the Amount" (=Amount / Quantity), all three (Quantity, Price, Amount) are the subjects of modification from within the portal in the Invoices db file (again, not affecting the Product db file). How can I do that ?

The Amount is not editable. I also attempted a pop-up list/buton combination but did not succeed in.

Thank you for your thoughts

[LiveOak]

In general, overrides of calculated amounts use another field stacked graphically with the calculated field. A typical example is:

Qty (number)

Price (number)

Subtotal (calculation, number) = Qty * Price

To override the calculation and allow a subtotal to be entered directly:

Qty (number)

Price (number)

SubtotalEntry (number)

Subtotal (calculation, number) = Case( IsEmpty(SubtotalEntry), Qty * Price, SubtotalEntry)

Stack the SubtotalEntry field behind the Subtotal field, make the Subtotal field opaque, and in Field Format, uncheck the "Allow Entry" option for Subtotal. What this does is makes it impossible to click into the Subtotal field. When you click on the stacked fields, you will click into the stacked SubtotalEntry field instead.

-bd

[mse]

The problem is I need a live data to arbitrary recalculate also either

Qty (calculation, number) = SubtotalEntry (number) / Price (number)

or

Price (calculation, number) = SubtotalEntry (number) / Qty (number)

[LiveOak]

Same answer. You must find a set of entry and calculation fields using the technique I described. If you enter both values and this generates a logical conflict, you must define the logic to break the tie. FileMaker has no built-in provision for a field to be a combination of calculated and entered. This is easily done if an entry is made into a new QtyEntry or PriceEntry field. You just have to decide how to handle the case where both are entered, one has to be ignored.

-bd

[kennedy]

I've built systems in C++ where you have two fields that are directly related by calculation... you can set either one and both change to the corresponding values. A simple example would be two on-screen fields: TempF and TempC, where both show the temperature specified by the user, one in Farenheit and the other in Celsius... the user can set either one and both change accordingly.

FileMaker *could*have* directly supported this with something akin to an editable lookup field, such that the lookup calc fires whenever the *other* field changes... but not when you directly edit the field.

Without that, I think you have to keep two extra fields for each... one for entry and one for the date/time of the entry. Then you compute based on whichever was set later. Ick. Even with that, it still would look-and-feel funny as the old input value would reappear on entry. Double-ick.

Anybody worked out a more satisfying solution?

.

[jasonwood]

Kennedy

I haven't completely thought this through but it might give you some ideas...

Set it up like this: calc fields on top, entry fields behind, disallow entry into calc fields... just as you implied.

Now place an invisible button over the calculation fields. When you click it, it clears the other entry field, and places the cursor in entry field beneath it.

[kennedy]

Good idea... that would fix the user experience.

Not too satisfying from the programmer experience though!