• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Financial Management Lawson S3 Financials

AP vendors report

 4 Replies
 1 Subscribed to this topic
 43 Subscribed to this forum
Sort:
Author
Messages
Chesca
Veteran Member
Posts: 490
Veteran Member
6/30/2015 6:52 PM
I am trying to create a list of vendors including name, tax id, diverse code, phone number, class, and address. I joined the APVENMAST AND APVENADDR but I am getting multiple address per vendor. I think I'd like to get the maximum date or most current vendor's address from the file and I don't know how to. Any help would be greatly appreciated.
Example:
Vendor 1 MED PO box 1235 1/18/11
Vendor 1 MED PO box 3953 3/26/12
TracyO
Veteran Member
Posts: 97
Veteran Member
6/30/2015 7:26 PM
I have a similar report but also included the APAUDIT table. I then added a group on APVENMAST.VENDOR and did a secondary sort on APAUDIT.TRANS_DATE. Then suppress the detail section and print the Group header or footer whichever on you put your fields on.
Chesca
Veteran Member
Posts: 490
Veteran Member
6/30/2015 8:25 PM
Would you be willing to share your SQL or report via email? We have to provide the state with this report by end of day tomorrow and my users did not let me know they needed this until this morning.
TracyO
Veteran Member
Posts: 97
Veteran Member
7/1/2015 11:23 AM

Here is the sql

SELECT "apvenmast"."VENDOR", "apvenmast"."VEN_CLASS", "apvenmast"."VENDOR_VNAME", "apvenaddr"."ADDR1", "apvenaddr"."ADDR2", "apvenaddr"."CITY_ADDR5", "apvenaddr"."STATE_PROV", "apvenaddr"."POSTAL_CODE", "apvenmast"."TAX_ID", "APAUDIT"."FIELD_ID", "APAUDIT"."TRANS_DATE", "apvenmast"."VENDOR_STATUS"
FROM ("LSFPROD_PROD9"."APVENMAST" "apvenmast" INNER JOIN "LSFPROD_PROD9"."APVENADDR" "apvenaddr" ON ("apvenmast"."VENDOR_GROUP"="apvenaddr"."VENDOR_GROUP") AND ("apvenmast"."VENDOR"="apvenaddr"."VENDOR")) LEFT OUTER JOIN "LSFPROD_PROD9"."APAUDIT" "APAUDIT" ON ("apvenmast"."VENDOR_GROUP"="APAUDIT"."VENDOR_GROUP") AND ("apvenmast"."VENDOR"="APAUDIT"."VENDOR")
WHERE "apvenmast"."VENDOR_STATUS"='A'
ORDER BY "apvenmast"."VENDOR", "APAUDIT"."TRANS_DATE"


Chesca
Veteran Member
Posts: 490
Veteran Member
7/3/2015 5:45 PM
Thank you soooo much TracyO!!