I forgot to mention, if you need a numeric field returned with GetResultSets, you have to wrap it in the STR function in the first or second argument.
If you need to return multiple columns, pick a delimiter that makes sense and won't occur in the data normally, my go to is ASCII # 31 (Unit Separator). ASCII # 30 is Row/Record Separator. You can then parse the results on each delimiter to get the piece of data you are after. So lets say you wanted the vendor key in the one variable and the vendor name and address fields in the other, you could do it using the following.
retExists=nAP_Vendor_Svc'GetResultSets("APDivisionNo$+CHR(31)+VendorNo$","VendorName$+CHR(31)+AddressLine1$+CHR(31)+AddressLine2$+CHR(31)+AddressLine3$+CHR(31)+City$+CHR(31)+State$+CHR(31)+ZipCode$+CHR(31)+CountryCode$",sKeyValues,sFieldValues$,"UCS(VendorStatus$)=UCS(""A"")",CHR(0),CHR(254))
Another interesting fact about what you can pass in the first and second arguments is that it can be any valid evaluable ProvideX code, so using EVS, XEQ, or an object method or property, like _obj'GetKey$() or _obj'GetKeyPadded$(). All ProvideX classes should have a variable called _obj that is the local handle for the object. So when GetResultSets evaluates _obj'GetKey$(), it does so using whatever object GetResultSets is being used on. Just keep in mind that was you pass to it has to be able to be evaluated and return a value, you can not do variable assignments. The exception is if you use the XEQ function to CALL ProvideX code in a file, in which case you can have whatever logic you want in the file and choose what to return to the XEQ function.
------------------------------
David Speck II
Tennessee Software Solutions
------------------------------
Original Message:
Sent: 05-05-2022 18:17
From: David Speck II
Subject: VI import (AP invoices) without VendorNo in the file
GetResultSets will return a 1 IF at least one record was found to match your criteria and a 0 if no records were found.
The variables passed to GetResultSets in the third and fourth arguments will return a delimited list of values from each record that matched the criteria. The delimiter is the SEP character in ProvideX, or ASCII # 138 so you can use CHR(138) in ProvideX and VBScript but in ProvideX, it is easier to just use the SEP constant.
The variables will be prefixed with a single space followed by the SEP character and then the values from each record will follow. It will also have a trailing SEP character.
So if only one record is returned, you can just grab the first value, by using the MID function combined with the POS function to locate the SEP characters. This is done in two steps, first to locate the first one and then to locate the second one. I suppose you could always just start by going after the third character onward but since you never know if Sage will change something it what gets returned, I just made it a habit to look for each occurrence of the SEP character.
If you want to know how many records matched, you can count how many times the SEP character occurs in the variable and subtract 1 from it. in ProvideX, you can use the POS function to scan for the all occurrences of the SEP character by passing a 1 in the second argument and a 0 in the third argument. In VBScript, I find it easier to just use the Split function on the variable using the SEP character as the delimiter and then you use UBound against the resulting array.
------------------------------
David Speck II
Tennessee Software Solutions
------------------------------
Original Message:
Sent: 05-05-2022 17:47
From: Kevin Moyes
Subject: VI import (AP invoices) without VendorNo in the file
Success #2, without the UDT (querying APDivisionNo too, just for fun):
"";IF NOT(coAP_Vendor_Svc) THEN { coAP_Vendor_Svc = NEW("AP_Vendor_Svc",%SYS_SS) } END_IF; IF coAP_Vendor_Svc>0 and IMP$[1]<>"" THEN { sVendor$=""; sDiv$=""; retExists=coAP_Vendor_Svc'GetResultSets("APDivisionNo$","VendorNo$",sDiv$,sVendor$,"UCS(UDF_Long_Vendor_Key$)=UCS("+QUO+SUB(IMP$[1],QUO,QUO+QUO)+QUO+")",CHR(0), CHR(254)); IF retExists THEN { sVendor$=MID(sVendor$,POS(SEP=sVendor$)+1); sVendor$=MID(sVendor$,1,POS(SEP=sVendor$)-1); IMP$[99]=sVendor$; sDiv$=MID(sDiv$,POS(SEP=sDiv$)+1); sDiv$=MID(sDiv$,1,POS(SEP=sDiv$)-1); IMP$[98]=sDiv$; } END_IF; } END_IF;IF sVendor$<>"" THEN VAR$=sDiv$+"-"+sVendor$ END_IFIndented for easy reading:
I'm not sure I understand how to clean the returned values fully (especially if I had included more columns), but I can figure that out later.
A great big "Thank You" to all who replied. :-)
I'd have struggled on this for a while without help.
------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Original Message:
Sent: 05-05-2022 12:21
From: David Speck II
Subject: VI import (AP invoices) without VendorNo in the file
File Layouts and Program Information - SY_Service (sage.com)
When looking on the FLOR, look for system classes under the BaseSystem group before Library Master. For whatever reason, the ones under Library Master are the plain versions and don't have much detail on the parameters.
Something like this should work.
IF NOT(nAP_Vendor_Svc) THEN { nAP_Vendor_Svc = NEW("AP_Vendor_Svc",%SYS_SS) }; IF nAP_Vendor_Svc THEN { IF IMP$[1]<>"" THEN { retExists=nAP_Vendor_Svc'GetResultSets("VendorNo$","QUO+QUO",sVendorNo$,sBlank$,"UCS(UDF_Vendor_Key$)=UCS("+QUO+SUB(sVendorKey$,QUO,QUO+QUO)+QUO+")",CHR(0), CHR(254)); IF retExists THEN { sVendorNo$=MID(sVendorNo$,POS(SEP=sVendorNo$)+1); sVendorNo$=MID(sVendorNo$,1,POS(SEP=sVendorNo$)-1); IMP$[99]=sVendorNo$ } } }
EDIT: Corrected the filter in the GetResultSets method to use the correct variable value enclosed in double quotes and to escape double quotes in the value if they exist.
------------------------------
David Speck II
Tennessee Software Solutions
Original Message:
Sent: 05-05-2022 11:59
From: Kevin Moyes
Subject: VI import (AP invoices) without VendorNo in the file
I've never used GetResultSets...but should learn. Bret's post on SageCity looks to have helpful examples. Do you know of a reference that explains what each of the parameters are used for?
------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Original Message:
Sent: 05-05-2022 11:18
From: David Speck II
Subject: VI import (AP invoices) without VendorNo in the file
I was going to suggest something similar, perform logic on either after read or before assign of the vendor no. The perform logic would just have to check if you have an object handle for AP_Vendor_Svc, if not, get it and store it in a variable that will be checked on each record, if the variable is not zero, then do a GetResultSets with a where clause on your UDF. Then parse the results and either put the value in a temp field, another variable, or overwrite an array index in the IMP$[] array, could always use 99 to be safe, then point your VendorNo field to this column with the Replace operation.
------------------------------
David Speck II
Tennessee Software Solutions
Original Message:
Sent: 05-05-2022 08:08
From: Randy Marion
Subject: VI import (AP invoices) without VendorNo in the file
@Kevin Moyes - I have done this on the AR side - Customers and Sales invoices, but the same concept. I have a customer UDF that contains the key value the import file will contain and I look up the customer number to actually use for the AR invoice. In my case, we use a delimited text file and they do not use AR Divisions, so the division field is simply assigned '00' and I do so After Assign perform logic that looks up the UDF, finds the Cust No and then simply put the found Cust No data in the imp$[] array, at the end of course. Then use that array position for the AR CustomerNo field.
In my case, if the PL cannot find the customer in AR, I have additional logic to go ahead and add them. Would this approach work for you?
------------------------------
Randy Marion