Invoicing totals

[barry685]

Alpha sports uses group calculations for deriving the invoices totals and balances, on their invoice form. Other than using posting in field rules to stored fields ( subtotal, Total, balance etc.) what other methods are available not using stored fields?

[Stan Mathews]

Re: Invoicing totals

Tablesum(), tablecount(), etc
Dbsum(), dbcount(), etc

[barry685]

Re: Invoicing totals

Do they have to be used at the set level, or can they be used at the table level in field rules?

[Stan Mathews]

Re: Invoicing totals

Must be used at the table level. There are no set equivalents.

They can be used in the field rules but I would suggest you work with the dbsum() etc for that. Much faster.

I always question why it is necessary to store such information in a table when the results are always obtainable with a calculated field in a layout.

[barry685]

Re: Invoicing totals

Can you please give me an example for getting an invoice Subtotal, and where this would be set up using, tablesum() or dbsum() with the following parameters:
Table is "Inv_hedr" variable is Subtotal
Child table is Inv_detail
Field in Inv_detail to be totaled is Extension
Linking fields is Inv_ID (which is numeric).

[Stan Mathews]

Re: Invoicing totals

Tablesum() is easier because it doesn't require an index tag name.

Tablesum("Inv_detail","inv_id = "+inv_id,"Extension") would be the expression for a calculated field in Inv_Hedr to sum the values in the Extension field in matching records in inv_detail.

Suppose you have an index on inv_detail with the expression inv_id and the tag name is id.

Dbsum("inv_detail","id",inv_id,"Extension")

[barry685]

Re: Invoicing totals

If the linking fields are numeric (N7) how is the expression written?

[Stan Mathews]

Re: Invoicing totals

No difference how many places, the expression doesn't change if the field type is numeric.

If inv_id were character the expression would be

Tablesum("Inv_detail","inv_id = "+quote(inv_id),"Extension")

You can experiment in the interactive.

Choose a valid inv_id, do this in the interactive.

inv_id = 12345 'use valid number
? Tablesum("Inv_detail","inv_id = "+inv_id,"Extension")

You'll get a return like

= 1247.36

[barry685]

Re: Invoicing totals

Not working in interactive or in field rules.

[barry685]

Re: Invoicing totals

My apologies. Field name is extended not extension.

[Stan Mathews]

Re: Invoicing totals

I just created a table my_inv_detail.

Structure

Inv_Id	Numeric	7	0	
Prod	Character	7	0	
Price	Numeric	7	2	
Qty	Numeric	4	0	
Extension	Numeric	9	2

Populated some records....

INV_ID	PROD	PRICE	QTY	EXTENSION
1	 	2.99	3	8.97
2	 	3.45	1	3.45
1	 	7.36	2	14.72

And in the interactive

inv_id = 1

? tablesum("my_inv_detail","inv_id = "+inv_id,"Extension")
= 23.690000

? dbsum("my_inv_detail","id",inv_id,"Extension")
= 23.690000

my_inv_detail.zip

[barry685]

Re: Invoicing totals

After making the above changes to how thew Subtotal is calculated (in field rules). The invoice no longer recalculates the subtotal when the invoice is modified. I tried refreshing the subtotal object with the onchange event of the browse but it didn't do anything.

[Stan Mathews]

Re: Invoicing totals

The invoice no longer recalculates the subtotal when the invoice is modified.

Correct. That is the advantage of using a posting rule.

How else would the inv_hedr record know that the inv_detail linked records had changed? You could code the onsave event for the invoice form to recalculate the field rules for the inv_hedr table but that would process all records in the header table.

[barry685]

Re: Invoicing totals

Well, That's not good. Whats the point in using the tablesum or dbsum function in the field rules than? So other than posting, is the only other solution to use the total function on the Invoice form? And if I was going to print an invoice, I assume the field rules would not have an effect there either.

[SNusa]

Re: Invoicing totals

Just for kicks, try adding a button to the main form. (and try the object.refresh() method from there)
Also, without testing, I'm thinking parentform.Refresh_Layout() might take care of it??

I'm sure that you already know: If you simply drag a field (from the details table on the drag ad drop pane) down onto the main body of the invoice (outside the embedded browse, with the wizard enabled) a5 will create a working totals field. I suppose you could also write code to generate totals and place the code on some objects event that would occur after you exit the browse. ~ But why are you trying to accomplish this in other ways?

Also, resync, requery etc. could be related possible solutions, since you are trying to manipulate data at the parent level from within the embedded browse. I suspect this may have something to do with timing & indexes since you are relying on the table events to do the totaling. Actually, does the field rule calculation only happen only when the record is created (the first time?)

Honestly, I'm willing to bet that using field rules to calculate totals for the invoice is not an appropriate usage of table field rules anyways. (And at the very least, keeping totals on the form enables the form to do the work, so the database file is not constantly in use. ~ Even if you could get your method to work.)

From time to time, I have experienced unexpected results while using embedded browses. Possibly because the form's mode is set to auto???
(form properties ~ which is also the way most users have the a5 entry mode default set to. ~ insert / add / view are handled automatically)

As recently as last evening (working with v10.5) Strange things where happening on record level events that had been assigned in table field rule events.... (I wonder if this is somehow related.) ~ recently noticed that I couldn't trap (successfully write code for the on events) when the table was placed in an embedded browse.