Dynacom Technologies

Hi! I want to know how will I add other information from invoice to the statement?

Like, i use 2 types of terms for each customer, and i want to show it on statement

for each invoice.There is no field containing the information for invoice in template designer.

how can i add other database to it? please help.. for v11 plantinum version.

Hi,

     you can add your field on your statement template with the advanced template designer. You can get your second term by query and show it on your template. If you want to open the advanced template designer, you have to click CTRL-SHIFT and click on "Edit" button in the screen "Select a Design".

 Jean-François Lefebvre

Hi.

Im in advanced template designer, but where can i built query? and how?

please help.

Hi,

    You have to click on the "View script" icon(icon image is white sheet with small yellow circle) in the toolbar. In the editor, you will be able to add script and create new query. Follow this step :

1) Add a new label in the Detail Section et change the property "Name" for "NewLabel"

2) Click on the "View script" icons

3) Add this code in the "OnFormat" event of the detail section.

Hi!

Thanks Very Much. i was able to connect the field.

But, I have another problem on this script, when i query the ExTermId on InvoiHdr, it showed the ID of Term, the # only, not the code of term,

how can i solve this? & another problem is how can i calculate the total for the invoice showed on statement? because i choose selected

periods only, i didnot want to show the balance forwarded anymore. just the total on the invoices printed on the invoice.

Please help.

Hi,

    for your problem about the term, you can add a textbox in the detail section and you can bind this textbox to the field Description of the table "Terms". When you add a textbox on your statement template, a field selector screen appear. In this screen, select the object "Terms" and select the field "TermId". With this field, you will see the term code on your statement template.

To calculate the invoice total showed on your statement, you have to add some script in your template and add a new label in the "CustomerFooter" section.

In the "customerHeader" section on the "OnFormat" event, you have to add this script :

'This script reset the Invoice total to 0 after each customer 

Set clsApplication = CreateObject("DynaFoundation.CAppObject")

Hi!

I'm able to do what you have mentioned. but, it does not properly calculate the total of the invoice printed on statement. For example, 1 customer purchased during Aug. 1 to Aug 30, and i print the statement for the week of aug. 11 to 15 ( i select dates from customer statement screen), your solution to the total calculation is still included the aug. 1 to 11 invoices, even it doesn't show on statement, and not the total of the invoice of aug 11 to 15 only. How can i Solve This? ( total for selected dates only)

The problem on term is that when i connect to the InvoiHdr table the ExTermID showed ID (the identity no.) of term, example the record on Term table is InTermID = 1 TermID =  Net 30, InTermID = 2, TermID = Net 60, when  i connect to the ExTermID field on InvoiHdr it shows only 1 or 2, not Net 30/Net 60. and if i select the field that you mentioned, it show only the Net 30 (which is default in customer screen). But i used this 2 terms for 1 customer that purchased different product. Please Help. ( the indentity no. displayed for each invoice is right but how can i convert it to show Net30 or Net60?)

Thank you and please help me on this.

Hi,

    for your invoice total, you have to change the event's script for detail section. Move the script from 'OnFormat' event to 'AfterPrint' event in the detail section and you will have only the total of the invoice of the statement.

For your term problem, you can bind a textbox on your statement like I explained in my last message or if you get your term by query, you can get your 'TermId' with this query :

Select TermId From Term INNER JOIN InvoiHdr on InvoiHdr.ExTermId = Term.InTermId where InvoiId = 'Invoice No'

Jean-François Lefebvre

Hi!

Very Thanks for your great help! I was able to built the two field.

 I have another question, the total in the customer footer is in the format 87488.00( no comma in thousands), how can i setup the format of it (comma to separate thousand) ? i can't found it in properties because it's a label.

How can i built a record count for the detail in customer footer section? ex., the customer footer will show total invoices is in statement.

Hi,

     you have to do a 'FormatNumber' on your invoice's total and the total will be formatted like the regional settings.

Exemple : FormatNumber(clsApplication.Application.Properties.GetProperty("InvoiceTotal"), 2)

You have to do the same thing for your record count than for invoice total. Here an exemple :

In the 'CustomerHeader' section, you have to add this script in the 'OnFormat' event : 

clsApplication.Application.Properties.SetProperty "InvoiceCount", 0

In the 'Detail section, you have to add this script in the 'AfterPrint' event :

Hi!

I'm able to do the counting.. but the count showed is incorrect. ex., if 2 invoices on statement, counting showed 3, always additional 1, was is the error on script? i just copied the script of yours..

please help

Hi,

      the error is due from the balance amount(first line of the statement). To have the correct counting of invoices, you have to add an "If" in the script of the detail section. Exemple :

If rpt.Sections("Detail").Controls("txtDocType").Text = "INV" Then

End If

Here is the others possibilities that can appear in the statement. If you want to count it, you have to add a condition for each type to the statement "If" like this :

If rpt.Sections("Detail").Controls("txtDocType").Text = "INV" and rpt.Sections("Detail").Controls("txtDocType").Text = "CRE"  Then

INV = Invoice

CRE = Credit Memo

BAL = Balance

PAY = Invoice Payment

Jean-François Lefebvre

Hi!

Thanks im able to built it..

Another question.. how to hide credit memo & Payment from statement printing?

please help

Hi,

    you can hide credit memo and payment transactions from statement but the transactions will be included in the different total in the bottom of the statement. If you want to exclude the credit memo and payment transactions from statement, you will have to calculate manually the total.

Here is the script, to hide the transaction, to add in the detail section on the "OnFormat" event :

If rpt.Sections("Detail").Controls("txtDocType").Text = "CRE" or rpt.Sections("Detail").Controls("txtDocType").Text = "PAY" Then

    rpt.Sections("Detail").visible = False

Else

     rpt.Sections("Detail").visible = True

End If

 Jean-François Lefebvre

Hi!

I found out that printing statement, the credit memo is putting together with charge side, is there anyway to put it on the credit side? or put a parenthesis on it's amount?