Sage 100

 View Only
Back to discussions
Expand all | Collapse all

I'm trying to figure out the easiest way to lookup

Wayne Schulz

Wayne Schulz12-10-2014 15:08

@JeffSchwenk I'll add it to Evernote
Mary Jo Krueger

Mary Jo Krueger12-10-2014 15:52

Great tip, David. Thank you for sharing!

  • 1.  I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 13:40
    I'm trying to figure out the easiest way to lookup the user who created a sales order invoice in Sage 100 v2013. I see that you can add the userkey to accounts receivable invoice history lookup but that's the key rather than the user code. Is there another way to do this? TIA


  • 2.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 13:49
    I THINK that the user key in Invoice history is who UPDATED the invoice, not who CREATED it, if that makes a diff.


  • 3.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 13:58
    Thanks Beth for the reply. There are two keys, a userkey, and a userupdated key. If I use these I'm going to have to give the client a list of user keys.


  • 4.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 14:20
      |   view attached
    Ron, have you ever added a link table in Visual Integrator > export Job Maintenance? if the customer owns VI the below should work. Start a new export job with your main table being AR_InvoiceHistoryHeader click on the LINK button Link Table Name =SY_User Sequence No = 00 Key expression is USERKEY hit accept (I've attached a screen shot of the settings I used ) you don't even have to complete the VI job - at this point the link is created. Your lookup on the AR_InvoiceHistoryHeader table will have the SY_USER table and will be linked on the UserKey. I guess the problem is, if you wanted both UserKey and UserUpdated, I'm not sure how you could get both. but hopefully this helps a bit. Also thanks for asking the question, I think I've pondered doing this before and wondered if it would work. once I went through the steps I was pleasantly surprised that it does work.


  • 5.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 14:22
    I tried right clicking on tab one of an invoice in history, to look at the system information and the user created key is blank. So I DFDM'd it. There is a user key, NOT a CREATED user key. I wonder if this is why the information does not display..... Could this be sloppy programming?


  • 6.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 14:27
    interesting Jeff, I wonder if that since this is history table it only stores the user that updated the register in both of those fields. rather than the user that manually entered the invoice in one field and the person who updated it in the updated field? I've never explored it far enough to know one way or the other, has anyone else?


  • 7.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 14:31
      |   view attached
    @DavidOverholt - Your post is amazing. I have long grumbled that the ALE is missing table links. This is fantastic. Below is the ALE after doing your suggestion. I wonder if this will survive an upgrade migration??


  • 8.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 14:46
    @WayneSchulz - Can you add David's post to the Evernote DB??


  • 9.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 15:04
    I created a sales order invoice with user rsg key 3 and went back and added a line to it with user tes key 4. I've attached the system information and invoice history header information. Based on the date of the created by information its the user that created the customer. In the AR invoice history header both userkey and userupdated key are 4. Seems that if a different user updates an invoice the original user who created the invoice is not kept. Now I'm going to try David's solution. Thanks David and Jeff.


  • 10.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 15:08
    @JeffSchwenk I'll add it to Evernote


  • 11.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 15:12
    I suppose it knowing WHO created it is important, you can add a UDF to SO and flow it all the way to history...


  • 12.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 15:14
    I'm now thinking that the userupdatedkey is the user that posted the invoice and userkey seems to be the user that last touched the invoice prior to posting. Yup, looks like a UDF. thanks


  • 13.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 15:19
    Thanks @DavidOverholt your solution works like a charm. I had no idea you could do this.


  • 14.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 15:52
    Great tip, David. Thank you for sharing!


  • 15.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-10-2014 18:15
    You could create a button script that looked up the User name based on the User Key and popped it up in a message box.


  • 16.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-11-2014 05:31
    @MaryJoKrueger and @JeffSchwenk thanks for the compliment, I guess its true that even a blind squirrel finds an acorn once in a while


  • 17.  RE: I'm trying to figure out the easiest way to lookup

    Posted 12-11-2014 05:35
    Just don't mention that to your client........


  • 18.  RE: I'm trying to figure out the easiest way to lookup

    Posted 10-19-2017 09:42
    Here is an oldie. Still working when I remember to use it. Want to move this concept a bit further. Is it possible to do something like this for the WRK tables and UDF's? Looking to eliminate a sub report in Crystal. Situation is BOM indented WRK table to link with CI item (primary vendor) but would like to pull in Alias information for that vendor and item. Thoughts. Just trying to think outside the box three years later...


  • 19.  RE: I'm trying to figure out the easiest way to lookup

    Posted 05-11-2023 21:56

    Bringing this back after six years since its last appearance.  Still solid.  Each month end, I have 20 - 30 SO's that need to be invoiced.  I have not paperwork to work from so I invoice by picking a SO.  But after five or six invoices, I loose track of which SO's have been processed.  The SO header table does not store the invoice # before invoice.  Invoicing becomes laborious.  By following steps outlined by @David Overholt above, I was able to add the invoice # to the SO ALE.



    ------------------------------
    Jeff Schwenk
    Bottomline Software, Inc.
    (540) 221-4444
    ------------------------------

    Original Message


  • 20.  RE: I'm trying to figure out the easiest way to lookup

    Posted 05-12-2023 11:15

    "The SO header table does not store the invoice # before invoice. "

    Yes it does, if you mean unposted invoices against an SO.




    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 21.  RE: I'm trying to figure out the easiest way to lookup

    Posted 05-12-2023 12:00

    Let me clarify my misspeak.  You are correct that it is stored in the SO Header.  However, in v2022 and prior, that field is not an available option in the SO ALE on the SO invoice entry panel.  I just spent 20 minutes looking again in the customer ALE options.  And please don't tell me that it is and I need cataract surgery.  It just might push me into retirement today.

    On the v2023 good news front, this field IS available in the ALE lookup.  Who knew that this was one of the 40 new features in this release?  Sage is INDEED listening to the channel!!!!!



    ------------------------------
    Jeff Schwenk
    Bottomline Software, Inc.
    (540) 221-4444
    ------------------------------

    Original Message


  • 22.  RE: I'm trying to figure out the easiest way to lookup

    Posted 05-12-2023 12:16

    You're right... it's not there in my 2022 either.  Odd.



    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message