TotalTaxAmount SetTaxAmountByRow not working

SOLVED

Hi,

I am trying to update invoice's total tax  using below code

TaxSummaryInfo taxSummaryInfo = journal.GetTotalTaxAmountInfo();
if (taxSummaryInfo.GetCount() > 0)
{
double totalTax = 10;
taxSummaryInfo.SetTaxAmountByRow(1, totalTax);
taxSummaryInfo.Save();
}

But it's throwing error, field you are trying to update is not accessible.

Is there anything wrong or any suggestion to make it working?

Thanks in advance.

  • 0
    SUGGESTED

    I may not be able to help because I don't use the  SDK and I don't read C# very well.

    However, if you are getting the Total Tax Amount, is this the total tax for an invoice found in table titlutot?  Is this the amount you see in the Ctrl-Shift-Y dialog in Sage 50?

    If so, then this is not the same as the line item rows of each invoice.  It looks like SetTAxAmountByRow is attempting to set the tax you see on each record of an invoice that you see in the Ctrl-Y dialog in Sage 50. eg. 

    taxSummaryInfo.SetTaxAmountByRow(1, totalTax);

    is the first row of the invoice, not the total tax of the invoice.

    If so, is it possible the SDK allows for updating the rows and will calculate the Total Tax for the invoice for you?  In this case GetTotalTaxAmountInfo fields might be read-only or you need a SetTotalTaxAmount method?

    Just a guess!

  • 0

    Hi Richard,

    Thanks for your inputs.

    Yes its same as SAGE 50 CTRL + Shift + Y and I would like to update the same value using SDK.

    There is no direct method  for SetTotalTaxAmount in .NET SDK and I need to update only total tax not the individual Item's tax (CTRL + Y).

    Please let me know if there is any alternative in .NET SDK to update tax total.

    Thanks.

  • +1 in reply to Nil
    verified answer

    As i said, I don't use the SDK so I don't know all the methods.  I'd have to read the help files just like you do.

    So I am going back to the logic of why you need to update only the total of the invoice without updating the tax on the line items.

    eg. 

    Line 1 Amount $100 HST @ 13% = $13

    Line 2 Amount $150 HST @ 13% = $19.50

    Total tax should be $32.50

    But if for some reason, the original invoice has a total tax of $32.51, is this your situation?

    If so, then you may have a line item like the following

    Line 1 Amount $81.75 HST @ 13% = $10.63

    Line 2 Amount $149.66 HST @ 13% = $19.46

    Total tax should be $30.09

    But if you calculate the total tax on the total of each line items then

    $81.75 + $149.66 = $231.41 @ 13% = $30.08

    If this is your scenario, I think you should be updating the tax on the line and I would hope the total tax would be updated as well.  A couple of quick tests should be able to show you what happens.

    If not, then you will have to use SQL commands to do the update of the total tax once you validate the total tax based on each line.

    And yes I know the total tax in the program can be updated with Ctrl + Shift + Y but in sales invoices, this also results in a dumb line at the bottom of the customer invoice printout indicating the tax not included in the lines.  So why would there be tax not included in the lines when the tax is supposed to be calculated on the amounts on the lines?

    You don't see this on the purchase invoice side because you can't print a purchase invoice.

  • 0 in reply to Richard S. Ridings

    Hi Richard,

    Yes, you understood my situation why I need to update tax total.

    For each line tax calculation based on total tax then it may lead wrong total and it won't match total with my application's modified total.

    After checking into details, It's seem hard to do with SDK directly.

    Yes, I was thinking to do directly in SQL but need to update couple of tables and identify columns and it's calculation to be accurate.

    Yes, print showing "'Tax code name' not included " and looking into tables they are modifying I can see they are inserting new records for into SQL table " ttaxdtl ".

    Let me try to modify using SQL commands and update required fields and calculation.

    Thanks a lot for your help and guidelines.

  • 0 in reply to Nil
    For each line tax calculation based on total tax then it may lead wrong total and it won't match total with my application's modified total.

    I'm not sure I fully understand this line but I assume it is more related to your application than Sage 50 so it makes sense I don't know what you are doing.

    ttaxdtl is only required to be updated when Report on Taxes is turned on for the tax (ttaxauth.wTaxRpt I believe).

    The main table that tracks total tax on purchase and sales invoices is titlulit.  This is the total I was referring to above.

    If your app is changing the amounts on invoice already posted, and the changes would affect journal entries then yes, there are a lot of table to update (Accounts, Journal Entries, Invoice lookup, currency info if applicable, etc.).  Please do not invalidate the audit trail of the program.  Also make sure the fiscal year(s) you are posting to are allowed by the system.  I would think the SDK tracks that when you use it but I would test the RunNonQuery method carefully.

    I would still try to adjust the line item tax amounts and see if that flows through properly.

  • 0 in reply to Richard S. Ridings

    Hi Richard,

    For each line total, I mean for this, if we calculate on each line then it may mismatch to total of my application. In my application like in below calculation total come 30.08 and In my application I change it to 30.09 for rounding for taxes.

    But if you calculate the total tax on the total of each line items then

    $81.75 + $149.66 = $231.41 @ 13% = $30.08

    If your app is changing the amounts on invoice already posted, and the changes would affect journal entries then yes, there are a lot of table to update (Accounts, Journal Entries, Invoice lookup, currency info if applicable, etc.)

    I am posting Sales invoice using SDK and in that we can't provide modified rounded tax total so I need to update posted invoice and SDK will auto calculate taxes based on tax code I am supplying while posting Sales invoice using SDK.

    Now to override sales taxes, I have to RunNonQuery and modified all tables and columns which simply accounting updating while update total in SAGE Desktop application.

    For audit, yes need to change all tables with simply mysql database tables update otherwise it may lead audit fail or may crash company file.

    Hope this will help you understand my problem into more details.

    Thanks for your help

  • 0 in reply to Nil
    SUGGESTED

    That is exactly what I thought you were doing.  But I thought you would be changing the amounts in Sage 50 to match your own application.

    The problem looks like you have programmed the other application to calculate the tax based on the total of all items that are applicable for that tax, while Sage 50 has always calculated the sales tax by line, rounded to two decimal places and then add up the amounts.  This causes the confusion between programs.  Both methods are acceptable to the CRA as long as the one used, is used consistently.

    Before you post your invoice to Sage 50, I thought you could just run a quick calculation of the invoice sales tax the way Sage 50 will calculate it, compare it to your's and then just add/subtract the difference if applicable, from one of the line items that has that tax code on it (yes I know that I just made it sound simple but that programming is likely a few hours work plus testing).

    I just took a look at the 2020.2 SDK help file.  It looks like the the TaxSummaryInfo Class might be used on entries that are already posted.  Are you trying to use it before it is posted?

  • 0 in reply to Richard S. Ridings

    Hi Richards,

    But I thought you would be changing the amounts in Sage 50 to match your own application.

    Yes, I need to match my application's tax amount to SAGE 50 as I have invoice section in my app and do rounding for tax amount as needed and then post invoice to SAGE using SDK from my app to SAGE.

    I just took a look at the 2020.2 SDK help file.  It looks like the the TaxSummaryInfo Class might be used on entries that are already posted. 

    Yes, I am already using 2020.2 SDK and TaxSummaryInfo class is there and its returning Total Tax info but there is method to SetTaxAmountByRow but its not working.

    As of now, only one options I can see, manually update tax values as needed using SQL RunNonQuery.

    Thanks.

  • 0 in reply to Nil
    SUGGESTED
    Yes, I am already using 2020.2 SDK and TaxSummaryInfo class is there and its returning Total Tax info but there is method to SetTaxAmountByRow but its not working.

    Are you posting the entry first, then asking for the Total tax?  If so, are you getting the total tax from the correct entry?  Or is it the total tax from a vendor invoice or a different customer invoice?

    If it is the correct amount, then I am guessing the error is on the SetTaxAmountByRow.  So you would need to check to see what the full error indicates (maybe there is something in the InnerException message).  I would suspect you are not referencing the proper invoice when setting the row tax.  Based on your sample as well, you are trying to set the total tax to be the same as the row tax and I don't think that is what you really want.

  • 0 in reply to Richard S. Ridings

    Yes, I am posting invoice first and while posting entry, it's not allowing to set any parameter or field to set updated total tax as SAGE calculating auto.

    It's for same customer and same invoice.. just want to update total tax to make it rounded as I did in my application.

    Yes, its throwing error at SetTaxAmountByRow "The field you are trying to change or function you are calling is not accessible under current circumstance.".

    No, I want to set total tax same as my application.

    Thanks.

  • 0 in reply to Nil

    Using nice round numbers, you have an invoice with two rows.

    $100

    $200

    and for some reason, your program is calculating the total HST @ 13% to be $39.01.

    Sage 50 will calculate $39 in total HST using $13 on the first row and $26 on the second row.

    What are you posting on the original invoice before you attempt to update the amounts?

    Based on my recommendation above I would be posting $13.01 and $26.00 on each of the rows so the total tax is $39.01.

    However, based on this line

    Yes, I am posting invoice first and while posting entry, it's not allowing to set any parameter or field to set updated total tax as SAGE calculating auto.

    You are not completing the transaction before you start to edit the already posted transaction.  If the transaction is not posted, how do you update it?

    In your original transaction, I am assuming you have tried it without editing the total tax, do you see the exact invoice you posted in the Sales Invoice data entry screen and the tax you posted under Ctrl-Y and Ctrl-Shift-Y?

    I still think you should be updating the row tax amounts and see what happens to the total but you have been resistant to this option.  I don't know if I am correct because as I mentioned, I don't use the SDK but based on what I have read, this sounds like a suitable test.

    What happens to the total tax when you post the $13.01 and $26 in line item tax as I mention above?  Do you get $39.01 or does the Sage 50 program recalculate?  You should be able to set a break point in your code to allow you to stop and adjust the dollar amounts as you step through the code, before you do the final post without changing the code to pull the original invoice from your program.

    I am not sure I can help you much more but I would be interested in hearing what happens in this kind of test.  It will prove whether you only need to adjust the line item amounts instead of the total as I mentioned above and will prevent you from having to do at least 6 SQL RunNonQuery Update commands to update a penny in the two account totals (in the proper fiscal year), HST reports, invoice line items tax, customer, invoice total tax and bank rec tables if applicable.

  • 0 in reply to Richard S. Ridings

    Hi Richard,

    Finally I have achieved this.

    I have updated all required tables with right values using SQL and its working perfectly.

    Thanks for your help.

  • 0 in reply to Nil

    You are welcome.

    You never did mention if adding the difference to the tax in one of the rows worked or not?  And if not what kind of error message you received?

  • 0 in reply to Richard S. Ridings

    I have mentioned in my earlier reply but let me mention that again as below quote.

    Yes, its throwing error at SetTaxAmountByRow "The field you are trying to change or function you are calling is not accessible under current circumstance.".


    Now instead of SDK, I have done with SQL query as SDK is not allowing to update using row method as above.

    Please let me know if you want to know details about solution I have applied to override tax total.

    Thanks.

  • 0 in reply to Nil

    Sorry my confusion.  I thought that command was causing a problem because the it is normally used after the entry is posted and it appeared you were trying to issue it as you were posting the original invoice.

    Please let me know if you want to know details about solution I have applied to override tax total.

    Thank you but I don't think that is necessary.  I know the tests and tables that need to be updated or not.