Posting a calc'ed result from form to table

[Bill Harriff]

I have a multi-tabbed form in a Job-costing app that has a calc'ed result that is basically the sum with additional markups of 7 other imputs from various fields and embedded tables on the various tabs. It's just the total cost of the job. I need that value in a variety of other reports and forms, but because it's just a form calculation I can't "see" it when I creating these reports and building expressions or placing fields from the various related tables. So far I've just recreated the formula in each report where it's needed but it sure would save a lot of time it I could figure out a way to post that calc'ed result to a table that is keyed with the job docket # when the entry is saved. I forseen a 2 field table with just "docket_number" & "total_jobcost" for the fields. Can this be done?

It also would solve another problem - I'm getting errors of a penny or two in many of the re-calc's, so one re-useable value should stop that problem.

One other thing about this: specific job dockets often get re-visited with revisions or additions, so if the total cost value changes I need to also figure out how to re-post to the same line in the posting table as having two different totals for the same job docket number would wreak havoc.

Any ideas sure would be appreciated.

[Tom Cone Jr]

RE: Posting a calc'ed result from form to table

Bill,

How about something like this?

If you force the user to push a 'save record' button on your form, that button's script could be used to open the new posting table, begin entering a new record, assign values to the two fields, end entering the new record, and then close the new posting table.

You could use a logical variable with your form to track whether the current transaction is new or an edit to an existing transaction. If it's new, you follow the sequence above. If it's an edit, you add logic to the script to search for the previous entry in the new posting table and then 'change' it instead of entering a new one.

-- tom

[John Lemisch]

RE: Posting a calc'ed result from form to table

Bill:
another idea (which we use frequently), is to place the parent table field i.e., "total" on the form and set the properties to "hidden". Create an onsave event to set the value of that field to the value in the calc->total field.

[Mick Magnuson]

RE: Posting a calc'ed result from form to table

We also track job costs by our internal part number. This cost accounting factor is based on various other calculations such as material, overhead, outside costs, labor direct and indirect, scrap rates etc. etc. etc. We process these figures on a monthly basis (min) and use a Summarize feature to create a table containing all the part numbers, each with its asscociated detailed costs.

During the rest of the month when sales quotes new orders they can get the base price to quote using the figures from the previous month.

The reason I use a summarize to create a table and not by an individual record post, is that if a constant value in the calculation changes, such as a material surcharge, the entire summary table is updated at the same time. Not just when someone changes and saves a record.

Mick

[Bill Harriff]

RE: Posting a calc'ed result from form to table

I'm still working on solving the problem of "posting" the results of a calculation on a form back into a field in the table the form is tied to. The attached sample works and is based mainly on John Lemisch's idea from his msg # 56274. The onform calc is "ProdTotal" and the field in the table that it gets saved to at Onsave is ProdCosts_Total. As per John's idea I put the ProdCosts_Total field on the form but with "Hide" set in its property sheet.

But a minor problem comes up with this method - if there are any markups or adjustments made to Production Costs they don't show up on the form until after it's been saved. Is there another event rather than Onsave that I could use to trigger the post? Maybe an OnArrive into the ProdCosts Markup field? Better yet would be if I could make it happen as soon as Calc->ProdTotal was any value greater than 0. This would mean that it would update/change as the user tabbed thru and entered numbers in the subtotal fields that supply the values for the calculation but I don't see a problem for that - is there?

One other problem with this sample - I can't get negative values to work in the ProdCosts Adj field (eg., -.1 which is a 10% discount for the customer). Any ideas about why it doesn't work?

Thanks for all the help with this.

[Jay Talbott]

RE: Posting a calc'ed result from form to table

Hi,

Two comments. If you want to fire a script, why don't you put it in the CanExitForm event on the form?

Second, your -.10 will work if you lengthen the field in the table stucture from 4 to 6 and do the same on the format of the field on the form.

Regards,

Jay Talbott

[Bill Harriff]

RE: Posting a calc'ed result from form to table

Thanks for the help for all of this. I did wind up using the post to a hidden field from the master table and just changed the event to ONDEPARTing the browse table entering line items that are getting calc'ed. Sure cleaned up the number of calc's done in the forms and made the final totals match throughout all forms and reports. And thanks for the info on getting negative amts to both work & display correctly