Help with relation / calculation

Classic Servoy
1

Hi all

I am having some trouble figuring out the best way of approaching a calculation field.

I am building a cash book type form into a solution our client uses for contact and stock management. I want to allow them to enter lines for each purchase they make, some of these purchases are for stock, some not (other costs, computer consultancy, postage and shipping, accountancy fees etc.) They are moving away from an Excel spreadsheet where each row is a cost, and each column is a category of cost (stock, postage, etc.). This means in Excel that one line might have costs broken down into several columns. Since these columns are hard coded in Excel, it would be ugly to transfer directly to Servoy as is. I don’t want 25 columns hard coded with various costs in Servoy. I would much prefer to have the currently named columns as a category field, and define the category field (with 25 potential values) for each line. The trouble is that some lines have multiple categories, and values.

I have so far approached this by making a table called cash_lines which has 12 relevant fields to define lines, and one analysis column for the category. cash_lines has a pk (cash_line_id) and a fk (cash_line_fk) and relates tto its-self (cash_line_id = cash_line_fk). I then create a cash_line record for a payment, and am able to create related cash (sub) lines which are defined as hidden when viewing the full list. You can click a button on a line, and see all the related sub-lines in a popup window.

The problem I have is that I need the total of the main cash line field to be either typed in, or the result of a summary of all the related sub-lines. Therefore the field needs to be a calculation (summary of the related lines), or NOT a calculation - if you type into it.

The way I was going to do it is not very elegant. Make the field a calculation summary of the related records or the result of an override field if something is typed into the override field, and then make the override field visible and the calculation field invisible if the client clicks into that field on a main line.

It seems though that there must be a more logical, or elegant way to do this.

Any pointers would be appreciated.

Thanks

Bevil

2

you can have calculations that keep user stored values.
the only thing you need to do then is just return nothing “return;” in your calculation
if you see that it is user input.

So somehow you have to know that… so something like

if (userDidInput)
{
 return;
}
else
{
  return calculatedValue;
}
3

Thank you Johan

I will give it a whirl…

Bevil

4

Just to follow up.
How does one “code” the detection of the ‘userDidInput’ ?
Should there be a button the user clicks to say use this entered value or should one use the Action ?
Are there multiple ways of doing this?
What is a good approach (robust that is)?

5

that can be a bit tricky yes.

1 thing that i can think of is storing it in 2 values.

1 calculate it and store the value also in a sibling column

Then when a recalc happens, first get the value itself again and compare it with the stored value in the other column.
If that is still the same then you can recalc it and if that is not the the same it is overwritten.

6

use an onUpdate table event on the subline to update the summary value on the line.

If there are no sublines, it will never get triggered.

greg.