Three-way formulas


#1

How do you create three-way formulas in Pan X?

For example, if the formula is:

  1. Cost of Sales = Volume (units) x Unit Cost, e.g. $100 = 50 units x $2.00/unit;

how can I enter a new value in the Cost of Sales field (the calculated field) to calculate 2) a new volume figure, given that the Unit Cost figure is unchanged, or, 3)

how can I calculate a new Unit Cost figure, given that the Volume figure is unchanged?


#2

In the Data Sheet view, each field can have a formula attached that is activated on data entry to that field.

Give each involved field the formula to run the calculations appropriate for it.

So entry into Unit Cost would trigger Volume = Cost of Sales/Unit Cost

While an entry to Cost of Sales would be Unit Cost = Cost of Sales/Volume.

Applied in the Data Sheet, the formulas would be triggered in all forms allowing data entry into those fields. If you want to make the formulas run only under specific circumstances, you could create a form just for that purpose and build the formulas into its text editors.


#3

An important detail - what JamesCook has described is code, not a formula. The code would need to be put into the Code section, not the Formula section.

A problem I see is that you have not specified a way to determine whether scenario 2) or 3) is to be used. In other words, you can have only one indetermite value. So when you enter a new value in the Cost of Sales field, how is Panorama to know whether you want to calculate the Volume or the Unit Cost? It can’t do both at once, only one or the other. You have to provide some way to tell Panorama which you want to calculate – perhaps an additional field that specifies what result you want calculated.