Printing balance remaining or current balance on SOP historical Blank Invoice

(0) Share

Report

Posted on by Community Member

Is there a way to print the Balance remaining on a SOP Historical Blank Invoice. I know how to do it on the SOP Blank Invoice, but there are different tables on the historical invoice. This is after the invoice has been posted.

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (0)

Suggested answer

L Vail 65,271 on at

Like (0)

Report

Use the same method you used for the WORK document. You will just change the table names in your package file from SOP_LINE_WORK to SOP_LINE_HIST and from SOP_HDR_WORK to SOP_HDR_HIST

Kind regards,

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Leslie,

Thank you for your prompt reply.

I don’t think I can do it in the package file because they use a Word template. I have heard that will cause issues with the Word Template. However I will look at doing it in Report Writer. I thought I already did try it and it did not work for me. I will revisit it.

Thank you again,

Was this reply helpful? Yes No
Suggested answer

L Vail 65,271 on at

Like (0)

Report

Hi,

The package file edit will help you get the fields on the report writer report. You need to get them to render in Report Writer in order to add them to your Word template. Editing the package file is the easiest way to copy the format of the Work document over to the History document. You do Report Writer first, print the RW report to an .XML file and then replace the data source of your Word template to the new .XML file that will include your new field.

Kind regards,

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Leslie,

Sorry for the delay.

I finally went back and looked at my testing. I had added the fields from the Sales Transaction History. These fields only bring in the Deposits and or payments made on the invoice before it is posted. As far as I can tell SOP does not track the amounts applied to invoices after the invoice is posted. This is done in RM. in RM you have apply records and most important the Current transaction amount in the RM20101 table. I think this is the table I need to link to the report. This field is updated to reflect the remaining balance if someone pays a portion of the invoice after it is posted.

What the client wants is if the Current transaction amount is $0 the want it to print PAID on the invoice. I can see some potential problems with this. After the invoice is paid off they will eventually run Paid transaction removal and remove them to History, so I might need to link the History table also RM30101.

I wonder if there is a Function script to bring this in or if I can link the tables in report writer.

Thank you for your help.

Was this reply helpful? Yes No
KirkLivermont 5,985 on at

Like (0)

Report

Cowboy,

I have the code to create a conditional in report writer to say paid when the transaction balance is $0. I can send it to you tomorrow if you would like. We use it to determine if the customer should be emailed a "receipt" or mailed a copy of their invoice.

Kirk

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Kirk,

Yes Please!

That would be incredible.

Thank you so much,

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

I'm not sure I understand. Do they care about the 'reprinting' of invoices after they are in history? Once they start paying things with cash receipts, the SOP document is in history. To get the history information to be part of your query, you would need to union the open and history tables together. RW will not let you do this. You can do it with VBA, or with RW functions that you can set in GP Powertools by Winthrop.

Can you bring more clarity for what it is they are wanting to do?

Kind regards,

Leslie

Was this reply helpful? Yes No
KirkLivermont 5,985 on at

Like (0)

Report

Cowboy,

I apologize but I misread your post. I can easily provide you the conditional to work with open invoices however I am not sure about historical invoices.

Kirk

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Leslie,

Sorry for not being clear. I think you do understand my issue now. They do want to reprint invoices when they are historical but they want the balance to be updated by Cash receipts received. As far as Union of the 2 tables from RM, I am not sure but I was thinking if I could bring in the RM20101 table I might be able write a conditional field so that if it did not find the invoice in the RM open table it would be Paid. My logic is if it is in the RM Historical table it would have to be paid.

I do not know if this is possible. I did see the recent post from Dave Musgrave (another one of my heroes, like you) about putting anything from SQL on the SOP Documents. They do not have GP Power Tools but I may suggest they purchase it.

I have not done much VBA programing and the last time was about 6 years ago.

I did not know if it is possible without custom coding.

I reread my original post and I can see where I was not clear. Sorry

Thanks,

GP Cowboy

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Thank you for your kind words, you made my day. Any time I am in the same sentence with David is a good day for me :)

Just thinking out loud here, but perhaps the inclusion of the RM Key file would help. The key file will tell you whether the transaction is in work/open/history. As you said, if it's in history, for sure it's paid. I think you're on to something here. If it's still in open you can read the Current transaction amount, and if it's in history it's 100% applied. I'm starting to think you are going to be able to do this purely with calculated fields. Regardless, the GPPT is something you should have to help you do the impossible with Dynamics. There are some incredible tools you can use to insure an upgrade goes smoothly.

What do you think?

Kind regards,

Leslie

Was this reply helpful? Yes No