Adding Calculated Fields to Order Forms
If you have a current copy of CCRQInvoice you can add calculated fields to the form. Today I’ll give a brief demonstration on how to create fields that involve simple calculations based on other fields. In my example, I want to add a “deposit” statement that tells my client that they need to provide a 50% deposit on the order.
I’ll start by editing the standard form template in CCRQInvoice – selecting Forms and then Edit Order Forms. I select the “standard” form and click Edit.
What I want to do is to add a statement “A 50% deposit (deposit) of the total (total) is required”. I’m going to place this in the footer, removing the customer message block that already exists. Clicking on the CustomerMsg field in the form, press the delete key to remove it.
Drag a Label control from the Standard Control window on to the form. Stretch it out to the right size, double click on it and type “A 50% deposit ( ) of the total ( ) is required”. You will have to leave enough room in the parentheses for the largest number you anticipate.
Click on the Field List tab, which is usually in the upper right corner. Right click on InvoiceData to get a popup menu.
Select Add Calculated Field, which will add a field named calculatedField1 to the list of fields. The property grid will show the field. I recommend changing the name to something that makes sense, like CalculatedDeposit. Note that the name must be unique, and it must not have any spaces or special characters in it.
Change the Field Type to “Double”. Click on the Expression field, and then click on the ellipsis (…) that appears in the field. This opens the Expression Editor.
In the large box at the top, type in .5 * and then click on the Fields item in the box below. Select [AmountTotal] from the list of fields in the center box. You should see the “formula” appear in the top box, as shown below. Click OK to save the formula
Drag two more label controls to the form, placing them over the blank spots in the parentheses that we left in the discount statement. Select the first label, and click on the pip in the upper right corner. In the Data Binding field, locate the calculatedDeposit field.
Click on the other label and select the AmountTotal field for the data binding value.
Save the form and exit the editor, and exit the Edit Reports window.
Select Order Processing from the main menu, load an invoice, select the Printing tab and located the form that you just created.
Select the invoice to print (try just one for a test) and click Process Selected. As you can see, the form has the calculated values that we want. We can improve this by adding some formatting (displaying values as currency, for example)
Let me know if this explanation is clear, or if you have any questions.
Category: CCRQInvoice
About the Author (Author Profile)
Charlie Russell is the founder of CCRSoftware. He’s been involved with the small business software industry since the mid 70′s, focusing on inventory and accounting software for small businesses. He is a Certified Advanced QuickBooks ProAdvisor and participate extensively in the QuickBooks Community user forums under the ID of CCRussell.
Subscribe
If you enjoyed this article, subscribe to receive more just like it.
Hello, I think there’s a bug in the CCRQInvoice, I tried to make an invoice with toy car, 5 quantity * 100, in the amount it always shows a $ (dollar sign).
How can I turn it off, I tried to hit the ‘pip’ and format it to general, number only, but still there’s no luck.
Thanks.
Are you referring to the “amount” column as it is set up in the “standard” form? Rather than a “calculated” field that you created, as this article discusses? And I assume you are using verison 3.3.1.1 of the program as well.
If so, it isn’t a bug, it has to do with how the standard form is set up. If you select the “field list” on the right, then click on that amount field, you will see that the field is “TextAmount”. It is a character field, not a numeric field, and the $ symbol is a part of the data. So you can’t get rid of it easily. Instead of that field, use “Amount”, which is a numeric field. Then you can format it.
Note that if you have any “comment” or description-only lines, using this template, you will then get a 0.00 value in that column instead of a blank value. That is why this release of the program uses a pre-formatted “TextAmount” field in the standard template.
If this is a problem for you then contact our support department and we can get you a pre-release of the next version of CCRQInvoice which has a better workaround for this issue, as it uses a more advanced version of the text editor.