Posting

[James Peterson]

From a form, how does one select a calculated field, that is the linking key field, to post to another table?
In this case, from the Invoice Entry Form, we want to post the Grand Total, to a linking key field in the Customer table.

[Tom Cone Jr]

RE: Posting


Are you saying you use the invoice grand total as the link field connecting the customer to the invoice? What happens if two customers order the same items?

--tom

[James Peterson]

RE: Posting

The calculated field on the invoice form is numberic and named "grand_total". This is a calculated field summing the line item totals plus tax minus discount.

I want the above "grand_total" to post (add) to the "total_billed" field in the customer table.

[Ken L Herrold]

RE: Posting

Jim:

I did this by add/posting via field rules linking on the customer id number IN THE BROWSE table. Each charge would post as an 'add'. This won't, of course get sales tax if that is a form calc field.

My line item table has the price, extension, AND tax so posting the total to the cusotmer record is a piece of cake.

Ken

[Tom Cone Jr]

RE: Posting

James,

Ken's idea is probably simplest. Instead of posting the calculated grand total from your form, post each line item individually by field rule, if that's possible in your app.

If you have something like a delivery charge that applies to the entire order, and not each line item individually you may have to go another direction. You might put these kinds of things in another child table for the 'orders', and post them individually to the customer table by field rule.

If you really want to use the calc field on your invoice form and arrange to add (or subtract) that value from the current value of a running_total field in your customer table it's possible to do using an Xbasic script which runs when the invoice record is saved. It's complex however because you would want to handle increases and decreases, and you would not want to change the customer total every time the invoice record changes, but only when the invoice calc field value changes... and then only by the amount of the change, not the actual total on the edited invoice record. This could all be managed in Xbasic by capturing the initial value of the calc field on your form as each editing session begins, and then comparing that value with the final value as the editing session ends, but it's going to require some effort to get the script right.

Hope this gives you some ideas.

-- tom

[James Peterson]

RE: Posting

Thanks for the help. I believe I will take the easy way and put all charges possible as line items.

One other question. The manual refers to "linking key values". I assume this is the same as "linking field" for a set?

[Tom Cone Jr]

RE: Posting


James,

'Linking Key Fields' in the Post Field Rule context refer to the same kinds of fields that are used to link tables into sets. However, it's not necessary that the table you are posting TO and the table you are posting FROM be previously linked into a set in order to define a post field rule. All that's necessary is that the two tables have at least one field in each record that's 'common' to both tables... otherwise Alpha Five will have no way of finding the correct record to update when it tries to post the desired value there. No sets are required to use post field rules. Furthermore, the linking key field in each record should not be the field you are trying to update, which is what I understood your first message to be saying. More commonly it's a customer id number (in the invoicing situation you are dealing with), and not the running_total. -- tom