Hmmmm, where is Kevin when you need him. I would think creating a UDT to mirror the Customer master file and then basing the UDF on the UDT but just how many customers do you have?

BigLouie is correct.  You can't validate a UDF against a Sage table, so set up a mirror UDT (maintained by script or manual import) and use that instead.

To continue this....playing with the idea

I've setup an export to an external PG db. We have 5 companies in our Sage application. Would like to keep the

customer list exports tied to each one of our Sage companies.

Example export from AR_Customer to postgres table using odbc dsn defined on server

Data Tab in export template includes a defined Temp001 field:

Temp001 "SNJ"   = company Sage company name must be typed in

CustomerNo

CustomerName

With an example table def in postgresql:

company_code (3 digit sage company)

customer_id

customer_name

Export works great...but

Ok, the question. Rather than changing the Temp001 string to the company name each time,

maybe I could tweak the vi job itself, or assign a parameter to the vi job and call it at the command line.

My other option is to run an external app to odbc the data from Sage by company, which I have done

multiple time.

Just want to make sure there isn't some yoda trick I may not know about.

Pulling data out of Sage, then inserting into pg probably offers the most flexibility, but bouncing of the

pros.

I can't imagine you would want to use a drop box when you have more than a reasonable number of options, like 25 or so.  Using a lookup is better for the end user when you have a larger number of records to validate against.  So setting up the UDF as a multi-line string field will give the best results.  Then use a pre-validate event script to check the value entered against AR_Customer.  This will work across companies without having to do any maintenance with mirrored tables across multiple companies.  If you want to have a lookup added to the UDF, i think i have a code example of how to do this floating somewhere and it allows pointing the lookup to standard sage 100 tables.

Here is code that can add a lookup to a UDF field.  Your UDF should be added to the panel with a height of 1.0 and enough space on the right to accommodate the lookup button.  This code is a template that i use with comments to help you understand what each line is for/doing.  For most lookups assigned to the actual field's control, like the salesperson number, you can just copy everything from the field specified in the sFieldToCopyLookupFrom variable.  However, in customer maintenance, the customer number field is actually called ML_Customer and is missing the ALELOOKUP property so i had to hardcode it in this example.  It is still copying the other relevant information, such as the formatter and query program.  This sample script also has other lines for vendor and salesperson lookups as an example.

sFieldToAddLookupTo = "UDF_Referring_Customer" ' Must be a valid field. Don't include $ for string fields.

sFieldToCopyLookupFrom = "ML_Customer" ' Must be a valid field or blank. Don't include $ for string fields. Comment out if not needed.

sLookupTag = "" ' Leave this blank if you want to copy this value from the "field to copy from".
' sLookupTag = "ALESTART==ARDivisionNo$;ALELOOKUP=AR_SalespersonDiv;" ' Hardcoded for Salesperson. Comment out if not needed.
' sLookupTag = "ALEMASK==cVendMask$;ALESTART==cDisplayInactiveVendorsInALE$;ALELOOKUP=AP_Vendor;" ' Hardcoded for Vendor. Comment out if not needed.
sLookupTag = "ALEMASK==cCustMask$;ALESTART==cDisplayInactiveCustomersInALE$;ALELOOKUP=AR_Customer;module=A/R" ' Hardcoded for Customer. Comment out if not needed.

sFormatter = "" ' Leave this blank if you want to copy this value from the "field to copy from".
' sFormatter = "=_obj'ClassFormatterSALESPERSON$(id,%1,""""%2"""")" ' Hardcoded for Salesperson numbers. Comment out if not needed. This is useful for fields like the CustomerNo, VendorNo, or any other multi part keys.
' sFormatter = "=_obj'ClassFormatterVENDOR$(id,%1,""""%2"""")" ' Hardcoded for Vendor numbers. Comment out if not needed. This is useful for fields like the CustomerNo, VendorNo, or any other multi part keys.

sLookupProgram = "" ' Leave this blank if you want to copy this value from the "field to copy from".
' sLookupProgram = "*SY_Lookup.M4P" ' Hardcoded Lookup program. Comment out if not needed.

nLookupButtonLineAdjuster = .75 ' This should be set to 1 as default but can be overriden as needed.  Increment or decrement this value in .25 intervals until the lookup button appears on the same line as the control specified in sFieldToAddLookupTo.

bIsFieldToAddLookupToValid = False ' This is a boolean flag to indicate the control specified in sFieldToAddLookupTo exists on the panel.
bIsFieldToCopyLookupFromValid = False ' This is a boolean flag to indicate the control specified in sFieldToCopyLookupFrom exists on the panel.

' The below two "If" statements check and flag whether the control specified in sFieldToAddLookupTo exists on the panel. It uses oScript.Evaluate to determine if the control you specified actually exists on the panel to avoid errors.
If sFieldToAddLookupTo <> "" Then 
	If oScript.Evaluate(sFieldToAddLookupTo & ".CTL") <> "0" Then bIsFieldToAddLookupToValid = True
End If

' The below two "If" statements check and flag whether the control specified in sFieldToCopyLookupFrom exists on the panel. It uses oScript.Evaluate to determine if the control you specified actually exists on the panel to avoid errors.
If sFieldToCopyLookupFrom <> "" Then 
	If oScript.Evaluate(sFieldToCopyLookupFrom & ".CTL") <> "0" Then bIsFieldToCopyLookupFromValid = True
End If

' The below line is checking that the appropriate variables have a value. 
If ((bIsFieldToAddLookupToValid) And ((bIsFieldToCopyLookupFromValid) Or (sLookupTag <> "" And sLookupProgram <> ""))) Then 

	' The below line defines/creates a new button beside the control specified in sFieldToAddLookupTo. The 4 parameters specified in @() are Column position, Line position, Width, and Height, override if needed.
	oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL = " & sFieldToAddLookupTo & ".CTL + 3000; BUTTON " & sFieldToAddLookupTo & "_Lookup.CTL, @(" & sFieldToAddLookupTo & ".CTL'COL + " & sFieldToAddLookupTo & ".CTL'COLS - 1, (" & sFieldToAddLookupTo & ".CTL'LINE-FLDR'GETLINE() - FLDR'GETTABHEIGHT() - " & nLookupButtonLineAdjuster & "), 3, 1.5) = %NOMAD_QRY_BTN$, FNT = %NOMAD_PNL_DEF_FONT$, OPT = %NOMAD_QRY_OPT$ + ""T"", TIP = %NOMAD_QRY_TIP$; " & sFieldToAddLookupTo & "_Lookup.CTL'Enabled = 0; IF coBusiness <> 0 THEN IF coBusiness'GetKey$() <> """" THEN " & sFieldToAddLookupTo & "_Lookup.CTL'Enabled = 1 END_IF END_IF"
	
	If bIsFieldToCopyLookupFromValid Then
		oScript.Execute sFieldToCopyLookupFrom & "_Lookup.CTL = " & sFieldToCopyLookupFrom & ".CTL + 3000"
		oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'BackColor$ = " & sFieldToCopyLookupFrom & "_Lookup.CTL'BackColor$"
		oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'DisabledTextColor$ = " & sFieldToCopyLookupFrom & "_Lookup.CTL'DisabledTextColor$"
		oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'Font$ = " & sFieldToCopyLookupFrom & "_Lookup.CTL'Font$"
		oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'HoverColor$ = " & sFieldToCopyLookupFrom & "_Lookup.CTL'HoverColor$"
		oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'TextColor$ = " & sFieldToCopyLookupFrom & "_Lookup.CTL'TextColor$"
	End If
	
	If sLookupTag = "" And bIsFieldToCopyLookupFromValid Then
		' The below line will append the tag from the field you specified in sFieldToCopyLookupFrom to the tag in the field specified in sFieldToAddLookupTo.
		' oScript.Execute sFieldToAddLookupTo & ".TAG$ = " & sFieldToAddLookupTo & ".TAG$ + " & sFieldToCopyLookupFrom & ".TAG$"
		oScript.Execute sFieldToAddLookupTo & ".TAG$ = " & sFieldToCopyLookupFrom & ".TAG$ + " & sFieldToAddLookupTo & ".TAG$"
	ElseIf sLookupTag <> "" Then
		' The below line will append the value specified in sLookupTag to the tag in the field specified in sFieldToAddLookupTo.
		oScript.Execute sFieldToAddLookupTo & ".TAG$ = " & sFieldToAddLookupTo & ".TAG$ + " & """" & sLookupTag & """"
	End If
	
	If sFormatter = "" And bIsFieldToCopyLookupFromValid Then
		' The below line will copy the fomatter string for the control specified in sFieldToCopyLookupFrom. This is useful for fields like the CustomerNo, VendorNo, or any other multi part keys.
		oScript.Execute "_OUT_TBL$[" & sFieldToAddLookupTo & ".CTL - 10000, _WDW_INDX] = _OUT_TBL$[" & sFieldToCopyLookupFrom & ".CTL - 10000, _WDW_INDX]"
	ElseIf sFormatter <> "" Then
		' The below line will add the fomatter string specified in sFormatter. This is useful for fields like the CustomerNo, VendorNo, or any other multi part keys.
		oScript.Execute "_OUT_TBL$[" & sFieldToAddLookupTo & ".CTL - 10000, _WDW_INDX] = " & """" & sFormatter & """"
	End If
	
	If sLookupProgram = "" And bIsFieldToCopyLookupFromValid Then
		' The below line will copy the Lookup program for the control specified in sFieldToCopyLookupFrom.
		oScript.Execute "_QRY_TBL$[" & sFieldToAddLookupTo & ".CTL - 10000, _WDW_INDX] = _QRY_TBL$[" & sFieldToCopyLookupFrom & ".CTL - 10000, _WDW_INDX]"
	ElseIf sLookupProgram <> "" Then
		' The below line will add the fomatter string specified in sLookupProgram. If this isn't copied from a control specified in sFieldToCopyLookupFrom, it must be set to something for the lookup to work. As of 03/14/2018, i believe the value "*SY_Lookup.M4P" should work for majority of cases.
		oScript.Execute "_QRY_TBL$[" & sFieldToAddLookupTo & ".CTL - 10000, _WDW_INDX] = " & """" & sLookupProgram & """"
	End If
	
End If

Here is what it looks like on the panel where i added it (pMain).

Also, to avoid the need for a validation script, when i added the UDF to the panel, i checked the Locked option.  This leaves the Lookup enabled for returning a value selected from the lookup into the UDF.

The script is added to the post load UI event of the panel where the UDF is added, in my example, this is pMain.

By copying the formatter information, this allows the UDF to display the value the same way the customer number field does while storing the values in the table without the formatting.

So if divisions are disabled, the UDF will display it like this but the field will contain the division, which would be "00" if divisions are disabled,

The sample code above looks like a lot is going on but that is merely because it serves as a template to accommodate as many scenarios as possible.  For your specific request, it can be simplified to the following.

sFieldToAddLookupTo = "UDF_Referring_Customer" 
sFieldToCopyLookupFrom = "ML_Customer" 
nLookupButtonLineAdjuster = .75
oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL = " & sFieldToAddLookupTo & ".CTL + 3000; " & sFieldToCopyLookupFrom & "_Lookup.CTL = " & sFieldToCopyLookupFrom & ".CTL + 3000; BUTTON " & sFieldToAddLookupTo & "_Lookup.CTL, @(" & sFieldToAddLookupTo & ".CTL'COL + " & sFieldToAddLookupTo & ".CTL'COLS - 1, (" & sFieldToAddLookupTo & ".CTL'LINE-FLDR'GETLINE() - FLDR'GETTABHEIGHT() - " & nLookupButtonLineAdjuster & "), 3, 1.5) = %NOMAD_QRY_BTN$, FNT = %NOMAD_PNL_DEF_FONT$, OPT = %NOMAD_QRY_OPT$, TIP = %NOMAD_QRY_TIP$"
oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'Enabled = 0; IF coBusiness <> 0 THEN IF coBusiness'GetKey$() <> """" THEN " & sFieldToAddLookupTo & "_Lookup.CTL'Enabled = 1 END_IF END_IF"
oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'BackColor$ = " & sFieldToCopyLookupFrom & "_Lookup.CTL'BackColor$"
oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'DisabledTextColor$ = " & sFieldToCopyLookupFrom & "_Lookup.CTL'DisabledTextColor$"
oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'Font$ = " & sFieldToCopyLookupFrom & "_Lookup.CTL'Font$"
oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'HoverColor$ = " & sFieldToCopyLookupFrom & "_Lookup.CTL'HoverColor$"
oScript.Execute sFieldToAddLookupTo & "_Lookup.CTL'TextColor$ = " & sFieldToCopyLookupFrom & "_Lookup.CTL'TextColor$"
oScript.Execute sFieldToAddLookupTo & ".TAG$ = " & sFieldToAddLookupTo & ".TAG$ + " & """" & "ALEMASK==cCustMask$;ALESTART==cDisplayInactiveCustomersInALE$;ALELOOKUP=AR_Customer;module=A/R" & """"
oScript.Execute "_OUT_TBL$[" & sFieldToAddLookupTo & ".CTL - 10000, _WDW_INDX] = _OUT_TBL$[" & sFieldToCopyLookupFrom & ".CTL - 10000, _WDW_INDX]"
oScript.Execute "_QRY_TBL$[" & sFieldToAddLookupTo & ".CTL - 10000, _WDW_INDX] = _QRY_TBL$[" & sFieldToCopyLookupFrom & ".CTL - 10000, _WDW_INDX]"

Damn. Can't wait to try this. David thanks for the effort and insight.

David,

Is this possible under Sage 100 2014.

We created a new panel for this purpose. Can't seem to find the post load UI event.for the panel.

Am I dead in the water?

No, UI scripts were introduced in 2015.  There might be a way to accomplish this using fonted text added to the panel that uses the XEQ function to call a text file containing the provideX code needed to do this but it would be tricky.

Our 2016 update is 4-6 weeks out. I'll contemplate. thanks David

Possible with a bit of a work around.  I couldn't get it to PERFORM against it self so i had to have a helper button.

So add a button to the panel so it appears to the left and either on the same row on above the field.  Take note of the name in the lower left.  You can set it to be hidden but DO NOT set it to be disabled.

Give it a name that makes sense.

Add the following to the script.

MAS_SCR_PFM = "..\CM\Script\AR_CustMaint_AddCustLookup.pl;ADD_LOOKUP"

Create a text file in the CM\Script folder that matches the file called in the script above.  I'm using the name "AR_CustMaint_AddCustLookup.pl" with "pl" for the extension for "Perform Logic".  They are named the same so i know they are related.

In the "AR_CustMaint_AddCustLookup.pl" file, add he following.

INVOKE_BUTTON:
ENTER ButtonControl
If ButtonControl <> 0 Then {
	ButtonControl'Enabled = 1
	ButtonControl'Visible = 0
	PreInput ButtonControl
}
EXIT 
ADD_LOOKUP:
FieldToAddLookupTo$ = "UDF_Referring_Customer" 
FieldToCopyLookupFrom$ = "ML_Customer" 
Offset = .75
FieldToAddLookupTo = 0; FieldToAddLookupTo = EVN(FieldToAddLookupTo$ + ".CTL", ERR = *PROCEED)
FieldToCopyLookupFrom = 0; FieldToCopyLookupFrom = EVN(FieldToCopyLookupFrom$ + ".CTL", ERR = *PROCEED)
If FieldToAddLookupTo <> 0 And FieldToCopyLookupFrom <> 0 Then {
	FieldToAddLookupTo_Lookup = FieldToAddLookupTo + 3000
	FieldToCopyLookupFrom_Lookup = FieldToCopyLookupFrom + 3000
	BUTTON FieldToAddLookupTo_Lookup, @(FieldToAddLookupTo'Col + FieldToAddLookupTo'Cols - 1, (FieldToAddLookupTo'Line - FLDR'GetLine() - FLDR'GetTabHeight() - Offset), 3, 1.5) = %NOMAD_QRY_BTN$, FNT = %NOMAD_PNL_DEF_FONT$, OPT = %NOMAD_QRY_OPT$, TIP = %NOMAD_QRY_TIP$, ERR = *RETURN
	FieldToAddLookupTo_Lookup'BackColor$ = FieldToCopyLookupFrom_Lookup'BackColor$
	FieldToAddLookupTo_Lookup'DisabledTextColour$ = FieldToCopyLookupFrom_Lookup'DisabledTextColour$
	FieldToAddLookupTo_Lookup'Font$ = FieldToCopyLookupFrom_Lookup'Font$
	FieldToAddLookupTo_Lookup'HoverColor$ = FieldToCopyLookupFrom_Lookup'HoverColor$
	FieldToAddLookupTo_Lookup'TextColor$ = FieldToCopyLookupFrom_Lookup'TextColor$
	FieldToAddLookupTo_Lookup'Enabled = 0
	If coBusiness <> 0 Then {
		If coBusiness'GetKey$() <> "" Then {
			FieldToAddLookupTo_Lookup'Enabled = 1
		}
	}
	Execute FieldToAddLookupTo$ + ".TAG$ = " + FieldToAddLookupTo$ + ".TAG$ + ""ALEMASK==cCustMask$;ALESTART==cDisplayInactiveCustomersInALE$;ALELOOKUP=AR_Customer;module=A/R"""
	_OUT_TBL$[FieldToAddLookupTo - 10000, _WDW_INDX] = _OUT_TBL$[FieldToCopyLookupFrom - 10000, _WDW_INDX]
	_QRY_TBL$[FieldToAddLookupTo - 10000, _WDW_INDX] = _QRY_TBL$[FieldToCopyLookupFrom - 10000, _WDW_INDX]
}
RETURN

Back in Customizer, use the text tool to add fonted text to the panel beneath or to the right of the UDF the lookup needs to be added to.

Use the following in the Text field.

=XEQ("..\CM\Script\AR_CustMaint_AddCustLookup.pl;SET_CMD_STR", "", BT_Link_1.CTL)

None of the settings for the text really matters since it returns an empty string anyway but if it makes you feel better you can set the option to make it hidden.