Calculated Field in Invoice Header

[Jay Sims]

How do you put a "Total" field which is a calculated field into the invoice header.

Thanks,
Jay

[Stan Mathews]

RE: Calculated Field in Invoice Header

What/where is "the invoice header"? A table, a form, a report?

[Jay Sims]

RE: Calculated Field in Invoice Header

The Invoice is based on a form. The form is then based on a table set called invoice set. I have 3 tables in the set. Customer, Invoice header and invoice items.



Thanks,
Jay

[Stan Mathews]

RE: Calculated Field in Invoice Header

Thus my confusion.

" I have 3 tables in the set. Customer, Invoice header and invoice items."

Do you want to put the ""Total" field which is a calculated field" - on the "Invoice" in a header (whole invoice being a report based on a form, I suppose)?

Do you want to put the ""Total" field which is a calculated field" - on the form on which the "Invoice" is based?

Do you want to put the ""Total" field which is a calculated field" - in the "Invoice Header" table?

[Jay Sims]

RE: Calculated Field in Invoice Header

Sorry Stan for not giving more details. What I have is a Invoice much like Alpha Sports. My calcualated fields would be stored on the invoice based on a form. Maybe my thinking is going in the wrong direction but what I am trying to do is put all information about the invoice into the invoice header. This includes, Customer number, date, product sold, and invoice total. This information would then be available for reports like accounting for customer payments and aging. All I want to do is carry over all invoice information without recreating calculated fields for every report based on that invoice.

Thanks,

Jay

[rtbtat]

RE: Calculated Field in Invoice Header

Hello Jay:

When using A5 there is almost always several different ways to accomplish what you want. For example, you can place fields in the parent table called Invoice or you can put them in the Customer table. The first option would give you a summary of all charges, payments/credits for that specific invoice.

You can also place fields in the customer table that provide a running total for all charges and payments/credits for that customer reference all of their invoices.

And of course, you don�t have to choose one or the other, you can do both.

So how does one set this up? As I said above, there are many different ways, I�m going to discuss my favorite technique. I create 3 fields in the parent table [which you call the Header]. Those three fields are Debits, Credits, and Bal_Due [the names can change]

The Bal_Due field is a calculated field that calculates the difference between Credits and Debits.

There are at least two different ways to get the values from the child table into the parent table. I use an Xbasic script that fetches through all of the child records and calculates the total Debits and/or Credits every time a new child record is recorded.

If you are not into Xbasic programming, you can use Alpha�s Posting Field Rule. If it�s a Debit, post the amount from the child table to the parent Debits field. If it�s a Credit, post the amount to the parent Credit Field.

Did this help?

Robert T

[Jay Sims]

RE: Calculated Field in Invoice Header

Thanks Robert,

You are a big help. Also, I have printed out your October 2002 article on Sub-Reports. This should to lead me in the right direction.

Jay

[rtbtat]

RE: Calculated Field in Invoice Header

Hello Jay:

Once you figure out the system and how easy it is, you will love it. From that point on, you will always have an up to date Invoice Balance, or an overall Customer Balance, at your finger tips. And best of all, Alpha will do the work automatically.

If you need more help, please don't hesitate to ask additional questions.

Good Luck,
Robert T

[Jay Sims]

RE: Calculated Field in Invoice Header

Hi Robert,

Sorry to be a pest but my brain refuses to understand common logic. I still cannot get the invoice total into the invoice header. Here are the steps I am following.

1. Invoice Header (Parent) I placed three fields. Debits, Credits and Balance Due.

2. I placed an expression in the Balance Due Field Rule in the Invoice Header, Debits minus Credits

3. In my Customer Table I have the following fields, Debits, Credits and Balance Due.

4. I posted the Customer Child table Debit field to the Invoice Header (Parent) Debit field. Likewise I posted the Customer Child table Credit field to the Invoice Header Credit field.

The missing link is my invoice total (Calculated field). Am I incorrect in trying to add the invoice total to the Debit field in the invoice header?

Mayby I am trying to do this backwords.In your reply you wrote " there are at least two different ways to get the values from the child table into the parent table". What I have been trying to do is get the invoice values from the parent (Invoice Header) and place in the child(Customer)table.

I know this has to be simple to accomplish but I am trying real hard to complicate it.

Thanks again,
Jay

[rtbtat]

RE: Calculated Field in Invoice Header

Jay:

You are in now way being a pest. There is a reason why this board exists and it's one of the core factors that make Alpha Five so special.

Right now I'm working on something that is taking up all of my time but let me take a quick shot at it. The following is only for tracking individual invoices

I think you have the general idea, you post debit transactions from the child transaction table to the parent Invoice header. You also post credits [payments] from the child transaction table to the Parent Credits Field. [Or you can have a separate table for Credits/Payments. This is a personal preference choice].

Once that is done, the Balance or Bal_Due field in the Invoice Header table is the difference between Credits and Debits. [Of course I didn't mention shpping and tax because I wanted to keep this example simple. But in the real world, you would usually include shipping and/or taxes in the Bal_Due calculation].

And NO, you don't want to add the Invoice Total to the Debits field. Every time you record a transaction in the child table that amount is posted to the Debits field in the parent table. If a customer orders 3 different items such as :

4 books at $10 per book for a line item total of $40
2 DVDs at $15 per DVD for a line item total of $30
5 Calendars at $3 per calendar, line item total of $15

Child Table Transactions
------------------------

In the 1st line, $40 is posted to the parent debits field
In the 2nd line, $30 is posted to the parent debits field
In the 3rd line, $15 is posted to the parent debits field

The parent debits field for this invoice should have a current value of $85. If there are no payments or credits, then the invoice total will be $85 [plus shipping and/or tax].

If there are payments or credits issued, those amounts will post to the Invoice Header Credits field.

The Balance [or Bal_Due] field will then reflect the difference between the Credits and Debits fields, giving you the total amount due for this invoice. But remember, we left out shipping and handling which is usually added in the real world.

Jay, I'm taking a shot in the dark, but it appears as if you understand the above, but your set design may be flawed. You repreatedly mention the customer child table. In general there is a One to One relationship from the Invoice Header to the One customer ordering products.

However, there is a One to Many Relationship between the Invoice Header and the child Transaction Table [whatever name it's given] where you record multiple transactions for that Invoice. Based upon the little I can tell, it sounds as if you're trying to post data from the Customer Table to the Invoice Header, that will not work.

You want to post debits and/or credits from the child transaction table [this is the Many Part of the 1 to Many]to the parent Invoice Header.

Robert T

[rtbtat]

RE: Calculated Field in Invoice Header

Jay:

I just thought of something and I owe you an apology. I use Xbasic to calculate the total debits and credits so my Xbasic script makes those cacluations only for the child records currently linked to the parent invoice record.

I haven't looked at or used Alpha's posting rule in such a long time, that I forgot what it looks like. You almost certainly have to create a condition to make sure the child Inv_No = the parent Inv_No in your posting rule. You only want to post debits and credits for this specific invoice.

I'm sorry about that omission, I'm sure someone who uses the posting rule can give you a clearer set of instructions.

Robert T

[Al Buchholz]

RE: Calculated Field in Invoice Header

Jay

Here is a similar situation that was worked on recently. Try this and see if it works on your machine. Put it in it's own directory.

What patch level of Alpha 5 are you on?

http://msgboard.alphasoftware.com/alphaphorum/read.php3?sortby=lastreply&direction=desc&num=11&id=53464&thread=53339#MSG53462

[Jay Sims]

RE: Calculated Field in Invoice Header

Words of thanks to all for you help. Especially Robert. I, along with all in the Alpha community owe you a debt of gratitude for your efforts to help others. My problem is now solved. I looked at different methods used on this forum and sort of came up with a solution. I call it the sausage approach. It may taste good but you don�t want to see how it�s made. Because it deals with payments and credits I�ll be better off testing it for a while before sharing it with others.

One suggestion I�ll like to make is it would be nice to have a tutorial based on the Alpha Sports Invoice on receiving payments. I understand accounting is very complex and should be left to the experts. Just a simple application to enter payments and credits would be nice. Maybe take Robert�s October 2002 article on Sub-Reports and use Alpha Sports Invoice as an example.

Once again thanks.

Jay

[rtbtat]

RE: Calculated Field in Invoice Header

Hello Jay:

Thanks for the kind words. I think most of us operate in a comfort zone utilizing the same old techniques/procedures that we can implement quickly and easily. However, when we cannot solve problems with our current skill set and/or those familiar techniques, that's when you really begin to learn how Alpha Five works

As you probably noticed, when you go through a series of steps to solve a new, frustrating problem, you learn a great deal. Yes I know it wasn't voluntary, but in the end, you will almost certainly come away with a deeper and better understanding of Alpha Five.

Robert T