Check if Vendor code from Database exists in a Lookup using Optional field & IDGRP concatenation (3 replies)
Hi Llew,
As discussed, did you get the Lookup working in SQL Management studio first?
Could I get a look at the query?
Nick
Hi Nick yes, I did. See below: This returns the correct values as expected, and then I would look up the IDGR&XIDPARTY (INVCHLOR01IAH) and return the vendor number, and then use the vendor number for any new records where IDGRP = INV and XPARTYID = CHLOR01IAH
Query:
SELECT dbo.APVEN.VENDORID, dbo.APVEN.IDGRP, dbo.APVENO.OPTFIELD, dbo.APVENO.VALUE
FROM dbo.APVEN INNER JOIN
dbo.APVENO ON dbo.APVEN.VENDORID = dbo.APVENO.VENDORID
WHERE (dbo.APVENO.OPTFIELD = 'XIDPARTY')
Llew, I think you could alter the SQL/Lookup to:
SELECT dbo.APVEN.VENDORID, dbo.APVEN.IDGRP, dbo.APVENO.OPTFIELD, dbo.APVENO.VALUE<br>FROM dbo.APVEN INNER JOIN dbo.APVENO ON dbo.APVEN.VENDORID = dbo.APVENO.VENDORID and dbo.APVENO.OPTFIELD = 'XIDPARTY'<br>WHERE ON.VALUE = '%1' and APVEN,IDGRP = '%2'<br>
SELECT dbo.APVEN.VENDORID, dbo.APVEN.IDGRP, dbo.APVENO.OPTFIELD, dbo.APVENO.VALUE<br>FROM dbo.APVEN INNER JOIN dbo.APVENO ON dbo.APVEN.VENDORID = dbo.APVENO.VENDORID and dbo.APVENO.OPTFIELD = 'XIDPARTY'<br>WHERE ON.VALUE = '%1' and APVEN,IDGRP = '%2'<br>
Note here the two parameterised values %1 and %2...this is how to parameterise the query.
Then your lookup function would look like:
Lookup("LOOKUPID", "VENDORID", Array(%XIDPARTY, %IDGRP), False)
Lookup("LOOKUPID", "VENDORID", Array(%XIDPARTY, %IDGRP), False)Note that we're passing in two values as parameters into the Lookup...these are two fields enclosed in the Array.
In the source dataset the vendors are called Parties, and each branch gets the same PartyCode (XIDPARTY), but they are set up at different times, so when a branch sets up the party it might already be in accpac, so instead of me assigning the next number from the counter I want to check if the account might already exist based on a concatenation of the XIDPARTY field and the IDGRP, because the party might be in different currencies in which case it gets a different number.
So if the DB XIDPARTY&IDGRP = The XIDPARTY&IDGRP Lookup, then I want to use that vendor or if it does not exist then assign a number from the counter.