SO Invoice Data Entry: Button Script to get Invoice No from an SO No

I have added a button script to the SO Invoice Data Entry MAIN panel and was wanting to make looking up an invoice that has already been batched a little quicker.

Essentially, instead of the user typing the invoice # they would type the sales order # into the invoice # field and then click the button script. The script is supposed to then perform a reverse lookup from the SO_InvoiceHeader table, but I can't figure out how to create an object that points to this table and performs the lookup. 

Typing in the SO # and then clicking the button would then pull up the actual invoice # that has been assigned to the sales order. Sure, using the search button works as well but our users have to perform this search frequently for various reasons and I am just trying to make the process go a little smoother.

While I've tried many different methods that I was able to find online, here's the final one that I've attempted that (still) isn't working:

To my knowledge, SO_Invoice_Svc only contains the details for the current invoice and cannot be used to locate other invoices, so I just need a way to link to the appropriate data source and perform the lookup.

You all have been a great help before and I really do appreciate your time and effort!

Top Replies

  • - maybe I'm missing something, but would it not be easier to customize the invoice # lookup?  Add Sales Order Number to the lookup, you can then within the lookup be able to search by a…

  • 0

    What are you trying to do? Are you trying to see if the sales order already has an invoice in invoice data entry?

  • 0 in reply to BigLouie

    Yes and no - the goal is that we may need to revisit the invoice and may only immediately only know the SO#, and this is just to allow us to more quickly access the invoice without having to use the search feature. Sure, searching for it works just fine we are just wanting to streamline this to be more efficient. So in the invoice field, we will actually type the SO#, run the script, and the SO# will be replaced by the actual invoice # (since Sage will not allow us to pull up an invoice in Invoice Data Entry by the SO#).

    The use case here is to pull up unposted orders that already have an invoice # assigned to them. For example, maybe the sales team may ask accounting to remove one of their orders from the invoicing batch so they can make modifications to it (such as add a credit card for a deposit) to which all they would be able to provide is the SO#. This number could be entered by accounting instead of the invoice # to p ull the invoice up in data entry or for a variety of other reasons.

    I just figured this would be pretty simple since this data is already in the SO_InvoiceHeader table. If this was an ODBC connection this would be simple because I know exactly which table to find this in, but Sage scripting doesn't appear to use the tables directly and instead uses bus/svc object pointers that I just don't fully understand the object model yet.

  • 0 in reply to SoonerFan21

    I don't think the big problem is the lookup.  Whenever I've tried to change the key field in a script it has left an orphaned row in the data entry table.  Once you type in the SO #, that sets the key and a placeholder record may be stored in SO_InvoiceHeader, and you can't really get around that in the same script.

    I'm betting your InvokeChange would be blocked too, since your script is running after a new entry has been started and you can't just swap out such things in the UI like that (...if it was in any way functional, you'd expect a "do you want to save" type prompt...).

    For your lookup I'd try SetBrowseIndex and SetBrowseFilter (instead of SetIndex and Find)... but getting that working isn't likely going to help.  (And your object creation doesn't look right).

  • 0 in reply to Kevin M

    The object creation isn't right - that's essentially the basis to this question. I need to get a pointer to whatever object contains the SO_InvoiceHeader table equivalent.

    And I've already tested the InvokeChange using hard-coded information because I also had the same concern regarding the prompt, to which my surprise it actually worked flawlessly and the SO# was no longer showing in the SO_InvoiceHeader table once I performed this action.

  • 0 in reply to SoonerFan21

    I usually create a new object in two steps... because normally you need to test for zero (no permission) before the Set... but you shouldn't need that since you're already in that object.

    oInvoiceLookup = oSession.GetObject("SO_Invoice_Svc")

    Set oInvoiceLookup = oSession.AsObject(oInvoiceLookup)

  • 0 in reply to Kevin M

    From what I could tell, SO_Invoice_Svc only contains the data related to the currently-open invoice within the Data Entry. Or am I missing something?

  • 0 in reply to SoonerFan21

    That is the only way I know of to reliably open a new object.  What you say might be true, with how batches work... but I really don't know.

  • 0 in reply to Kevin M

    - maybe I'm missing something, but would it not be easier to customize the invoice # lookup?  Add Sales Order Number to the lookup, you can then within the lookup be able to search by a certain Sales Order Number.  This maybe easier than correcting your script, because there are some potential issues with cleaning up erroneous invoice numbers in the method you are working about above.

    (customized invoice lookup below)

  • 0 in reply to jepritch

    I was about to say