Method Community

Hi there,

I created an item type "SubTotal" to use in estimates and invoices.

The subtotal is correctly calculated taking the amounts in the column "Amount" for the items above it.

Unfortunatelly, the subtotal is displayed (on the screens) on the column "Cost" for the Estimates, and on the column "Rate" for the Invoices, instead of showing on the column "Amount" as it is a subtotal of the amounts, and not of the costs or rates...) and it is not printed on the estimates and invoices that only show the "Amount" column.

In return, the same item behaves correctly in QuickBooks, meaning that the subtotal shows on the column "Amount" and prints correctly on invoices and estimates.

Any help please? Thank you.

 Hello,

The way its functioning is by design. Grid columns are generic. They don’t have business logic that says “if it is a subtotal, don’t add it up in the column total”.  That is why it is designed the way it is, this is actually easier on the eyes as well. It is different from the specific implementation in QuickBooks, but unless/until we have a complex feature in grids that allows you to create a formula that conditionally adds some values in a column to the total and not others; this is how it has to be.

You can get around this on an Amount column on a custom report template (using the Method Report Designer) by having a formula that if the ItemType is Discount, show Rate/Cost, otherwise show Amount.

Hope this helps.

"Grid columns are generic. They don’t have business logic that says “if it is a subtotal, don’t add it up in the column total”."

Thanks amanda, for your prompt answer, but I am affraid I am not getting the logic of it - I must be doing something wrong, and I am trying to correct it from my side (and sorry if I seem a bit thick with this issue):

Do you mean that the "Subtotal" item adds up amounts in one column, and then shows the result in a different column?

Thank you,

 Hi there,

No, that's not what I meant.

By generic grid, I meant that the subtotal of a grid column is the total of each and every value in that grid column and cannot be designed to exclude any of the values. There is no way for us to specify in the grid, that if, item type is subtotal do not include it in the total amount. So for instance, if the item subtotal amount was shown under the amount column and the amount is $100, then our total amount figure would be over by $100, because we cannot specify that this should be excluded from the sum of the column.

In order to get around this, you can use the report template alternative I outlined.

Hope this helps.

Got it, thanks.

I have a final (I hope) issue... :-)

I am trying to do what you suggested on the report template, but can't build the iif() formula with the builder.

Not sure how to specify the [Field.Type] is "Subtotal".

Can you point me on the right direction, please?

Thank you,

 Hi Antonio,

It's great that you now better understand how grids work. In order to specify the [Field.Type] is 'Subtotal' here is what you do:
Add a linked field to invoiceline table, through the Item, to ItemType. Open up the report and edit the Template. Add a calculated field under InvoiceLine.Invoice (If you are not sure how to add a calculated field, take a look at webinar 30:  http://www.methodintegration.com/cs/blogs/methodblog/pages/Method-Community-Webinars.aspx). Next, you edit the calculated field, and use the IIF under Functions. Lastly, your Result formula should be Iif([ItemType]='Subtotal',[Rate],[Amount]). Drag the calculated field on your report - place it in the Detail1 section under DetailReport.

Let me know if it works.

 Hi Antonio,

In case you haven't worked with linked fields before, you can take a look at this post for some guidance: http://www.methodintegration.com/cs/forums/p/887/2977.aspx#2977
A tip -  you're adding the linked field to the InvoiceLine table, via the Item link to the field 'ItemType'.

Hi Amanda, it worked good. Thank you for your patience

Just a last question on this:

Could I use the same solution ( a calculated field ) to prevent the 0.00 value from printing?

Details: I have an Item called 'Comment' to use whenever I need to insert comments on the Description Field within an Estimate or Invoice. But can't avoid this item to print a zero value on the total column.

Added a linked field to EstimateLine Table via Item to FullName and named it ItemFullName.

Also created a field in EstimateLine called 'BlankField' of type Text (which is empty)

I am trying to build the following statement

Iif([ItemFullName] == 'Comment' , [BlankField] , [Amount])

which keeps showing zero value

I must be doing something wrong on the logic of the formula(?)

Or is it that I am trying to put a string on a numeric field(?)

It looked like I could use calculayted fields in this case also... but seems it is not the right path...

Do you think you could help with this, please?

Thank you,

 Hello,

I'm trying this out on my end. I'll let you know what I find.

Hi Antonio,

I got some good news for you - I got it to work!

Your formula should look like Iif([Item]='Comment',[BlankField],[Amount])

I'm not sure where you're pulling 'ItemFullName' from. You should be able to see 'Item' in the list of available fields to select from when creating your formula.

Let me know if that works.

Hummm, not yet working properly, although the formula above seems to be calculating properly.

The issue seems to be with the formating. [BlankField] was created as Text length 255

Tried formatting the calculated field in several different ways, on the report designer:

{0:n2} prints as 0.00 for [BlankField] <not suitable> and amount formated with 2 decimals for [Amount] <good!>

{0:#.00} prints as .00 for [BlankField] <not suitable> and amount formated with 2 decimals for [Amount] <good!>

{0:#} prints blank for [BlankField] <good!> but amount formated with NO decimals for [Amount] <not suitable>

{0:#.##} doesn't work either....

If it has to do with formatting, could you please tell me how you formatted in your side? Otherwise, help please.

Thank you for your patience.

Hi Amanda, it is working now.

I was seeing the results on the preview of the report designer, but when printed preview from within Method, the form prints the estimate lines as intended - rate for SubTotals, blank (NoPrint) for comment lines and amount with two decimals.

The customer is happy - I am happy.

Thank you again for your pateince.

Have a great day.

 Hi Antonio,

That's awesome that it's working for you now, well done!