Sage 100

I am sure it is answered in the socialcast but I can't find it so... I want a UDF with a lookup to another table (in this case vendor masterfile) and return the vendor number. I know I can setup a UDT and populate the vendor list but that becomes stagnant without setting up some time of update process.

It's more work than it should be, but I've created an APVendorShadow table, and set up scripts to update it with each pre-write or pre-delete to AP_Vendor

the short answer is that it can't ""normally"" be done without a shadow table and scripts HOWEVER, i just recently worked out a way to do this via a UI post load script. You do however need to know the appropriate ""TAG"" to hardcode into the script or copy it from another control that has the lookup info in the ""TAG"". Below is a script i played around with on the sales order header tab's post load event. The UDF i was testing the lookup for is called ""UDF_MY_UDF"" so you will need to replace that string with your UDF name. If you are getting the lookup parameters from another field, in this case, the SALESPERSONNO field. You can also use debug mode to figure out what the correct TAG should be for any given control by typing ""msgbox controlname.TAG$"" as seen in the image i attached. In your case, since you are after the vendor number, the other image shows the tag used for the vendor number lookup in item inquiry. You can try to hard code the script to use that. In short, what the 3 lines below are doing is calling the oScript.Execute method which allows ProvideX code to be executed. The first line appends the lookup tag needed from the SalespersonNo field. The second line then creates a button beside your UDF, this is what NOMADS normally does when drawing a panel for all MULTI LINE controls whose height is equal to 1. This line attempts to figure out the position of your UDF and then slightly changes it to account for the folder. I have found the need to change the value ""1"" in (UDF_MY_UDF.CTL'LINE-FLDR'GETLINE()-FLDR'GETTABHEIGHT()-1) to a number slightly larger or smaller on different installs. The third line than copies the the code to execute when a lookup is invoked for your control from the control's whose tag was copied. I believe in most cases, it should be ""*SY_Lookup.M4P"" but the reason i copy it instead of hardcoding it is in the event sage changes the program name, if it is being copied, from a standard control, it should not break. --------------------------------------------------------- oScript.Execute ""UDF_MY_UDF.TAG$ = UDF_MY_UDF.TAG$ + SALESPERSONNO.TAG$"" oScript.Execute ""BUTTON UDF_MY_UDF.CTL + 3000,@(UDF_MY_UDF.CTL'COL + UDF_MY_UDF.CTL'COLS - 1, (UDF_MY_UDF.CTL'LINE-FLDR'GETLINE()-FLDR'GETTABHEIGHT()-1), 3, 1.5)=%NOMAD_QRY_BTN$,FNT=%NOMAD_PNL_DEF_FONT$,OPT=%NOMAD_QRY_OPT$,TIP=%NOMAD_QRY_TIP$"" oScript.Execute ""_QRY_TBL$[UDF_MY_UDF.CTL - 10000, _WDW_INDX] = _QRY_TBL$[SALESPERSONNO.CTL - 10000, _WDW_INDX]"" ---------------------------------------------------------

Larry, I come from a non programming point of view and offer this suggestion to non programmers/scripters. If your client is on V2016 or later, you can set up VI export that will run on a schedule via Task Scheduler. Have a VI import run via the Task Scheduler 5 or 10 minutes after that. The names of the data file will be the same. Set up the tasks to run as often as you need it to keep your table up to date.

I've cleaned up the code and added comments to the version of the script attached to this post. It is also a little more robust and easier to use as well. Also, the code in the snippet in the above images for the ml_vendor.tag$ does not work. I have included two lines as samples for vendors or salespersons. Comment/uncomment as needed.

if their vendor table isn't huge, a shadow table should work perfectly for this!

Frequently updates.