Saving Calculated fields to table from form

[ashghai]

Hiu

I have a calculated field in a form that works out the commission.
I need to set this value to a commission field in a table to use on reports for showing commissions owed to agents.

I cant seem to find a way to do this.

Please help.. im fairly new to alpha 9

Thanks
Ash

[Keith Hubert]

Re: Saving Calculated fields to table from form

Hi Ash,

Have you looked at Calculated field in Field Rules?

[ashghai]

Re: Saving Calculated fields to table from form

Hi Keith
Yes i have.. but the calcs are too complex for that facility i think!

I have tried to save the calc into a variable but cant seem to find a way to select a calculated value.

Regards
Ash

[Keith Hubert]

Re: Saving Calculated fields to table from form

Hi Ash,

If you can get a value into the field then you should be able to export it to Excel.

You cannot export the value of a calculated field from a form or browse.

[ashghai]

Re: Saving Calculated fields to table from form

Hi Keith

Im not trying to export the value to excel.

What i want to do is write back a calculated value from a form to a field in the table.

ie,,
calculated fields in a form

comm1 = if(calc->F>calc->B,((calc->B*.12)+(0.5*calc->G)),0)
comm2 = if(calc->F=calc->B,(calc->F*.12),0)
comm3 = if(calc->F<calc->B,(calc->F*.1),0)
comm4 = calc->comm1+calc->comm2+calc->comm3
comm = if(tbl_orders->Ostage="cancelled",0,calc->comm4)

i want to write comm into a table called tbl_orders, and field name Ocomm.

Regards

Ash

[Tom Cone Jr]

Re: Saving Calculated fields to table from form

Ash,

1) tell us about the complete context in which this is occurring. Is the target table open? Is the record pointer in that table pointing to the desired record? What "mode" is the table in? Are you creating a new record, or updating an existing record ? Is this table external to the tables supporting the form?

2) you will need to select an "event" to trigger the write. Are you thinking this would be a button push or something else?

As you're pondering these questions you might check the help file topic <tbl>.change_begin() to see an easy example of how one might "write" a known value to a field in a table.

[Garry Flanigan]

Re: Saving Calculated fields to table from form

If the user must push a button to save the record then you can have code to set a value to a field before saving. Assume you have a field comm1 in your table, then something like:

t = table.current(1) ' get a pointer to table
t.comm1 = if(calc->F>calc->B,((calc->B*.12)+(0.5*calc->G)),0)
parentform.commit 'saves record

Hope this helps.

[ashghai]

Re: Saving Calculated fields to table from form

Hi
Thanks for the replies

I am trying to do it as a on exit event.. the form is already using the table im trying to update, and the record is being updated and not a new one created.

The form is in normal system default mode.

all i need to do is to write the calculated field to the field in the record.

Hope im explaining it right!!

Regards
Ash

[Garry Flanigan]

Re: Saving Calculated fields to table from form

Did you look at my code?
This works whether entering a new record or changing an existing record.
After every edit you must save changes or they are lost!

[ashghai]

Re: Saving Calculated fields to table from form

Hi Gary

yes i had a look at that.. but i have one field called Ocomm, which needs to be set to the calculated value comm.
However, comm is calculated from a number of calculations as shown above.

I was hoping that i can take the final value comm and write this to the field in the table.?

or do i put the whole calculation routine inside your code somehow..

Sorry i must sound a little thick!!

Regards
Ash

[Tom Cone Jr]

Re: Saving Calculated fields to table from form

Ash, I think the OnExit event for the form won't work. It fires after the form closes. The calculated display field vanishes when the form closes. Suggest you review the list of available "events" and choose another. If data entry is occurring perhaps the forms's CanSave event would be a better choice ?

See the help file topic "Object Events" for a list of form events and an explanation on when they each occur.

[Tom Cone Jr]

Re: Saving Calculated fields to table from form

Ash, there are two ways to write known values to the table.

1) you can "set" the text or value property of the object displaying the field in your form layout; or

2) you can use a pointer to the table supporting the form and reference the desired field using the pointer.

the syntax for these two sequences is different, as you might expect.

Is the target field being displayed in your form layout? If so, what's the object name that is displaying the target field?

[Steve Andrews]

Re: Saving Calculated fields to table from form

Ash,

One other thing you may can consider would be a UDF. If the complex calculation could be put within a UDF with the necessary parameters passed to it, that UDF could be accessed on the form and accessed by a new calculated field of the table. Rather than writing from the form to the table through events, this approach may simplify the calculation considerably.

The UDR approach would definitely be feasible if the only things which the calculation depended on were fields from the table. If, however, you needed, say, a rate specified on the form but not available from a table, the UDR approach may not be what you would need.

Steve

[ashghai]

Re: Saving Calculated fields to table from form

Hi

Tom, The target field is not being displayed, but can be.. its called Ocomm. in a table called tbl_orders

Steve, not all the info i need for the calc are in table, UDF may not be possible

[Tom Cone Jr]

Re: Saving Calculated fields to table from form

Since no object in the form is available the first approach I mentioned is not available, either. So using code to talk directly to the table supporting the form is the approach to take:

it may be as simple as this:

In canSave event for the form:

tbl_ptr = table.get("tbl_orders")
tbl_ptr.Oocomm = Calc->comm