Vendors

Returns the available vendors, including contact details, address information, and account data.

Table Specific Information

Select

The connector uses the Square API to filter the results by the following columns and operators while the rest of the filter is executed client-side within the connector.

Id supports the following operators: =, IN.
Name supports the following operators: =, IN.
Status supports the following operators: =, IN.

List all vendors, including both active and inactive records:

SELECT * FROM Vendors;

Retrieve a vendor by specifying its Id:

SELECT * FROM Vendors WHERE Id = 'F5MMNGPNW2IZVRB2'

Retrieve several vendors at once by supplying their Ids with IN. The connector sends these to the Square bulk retrieve endpoint, so the list is served by a single request rather than one request per Id.

SELECT * FROM Vendors WHERE Id IN ('F5MMNGPNW2IZVRB2', 'SUKM5RFPH6T4CWTW')

Retrieve a vendor by specifying its Name:

SELECT * FROM Vendors WHERE Name = 'new-vendor'

List only active vendors:

SELECT * FROM Vendors WHERE Status = 'ACTIVE'

List only inactive vendors:

SELECT * FROM Vendors WHERE Status = 'INACTIVE'

Insert

To create a vendor, the Name field is required.

INSERT INTO Vendors (Name, AddressAddressLine1, AddressAddressLine2, AddressAddressLine3, AddressLocality, AddressSublocality, AddressSublocality2, AddressSublocality3, AddressAdministrativeDistrictLevel1, AddressAdministrativeDistrictLevel2, AddressAdministrativeDistrictLevel3, AddressPostalCode, AddressCountry, AddressFirstName, AddressLastName, AccountNumber, Note, Status) VALUES ('insert_4', 'a', 'b', 'c', 'locality', 'sub', 'sub2', 'sub3', 'AddressAdministrativeDistrictLevel1', 'AddressAdministrativeDistrictLevel2', 'AddressAdministrativeDistrictLevel3', '134003', 'IN', 'AddressFirstName', 'AddressLastName', '1234567890', 'this is a note.', 'ACTIVE')

INSERT INTO Vendors (Name, AccountNumber, Note, Status, Contacts) VALUES ('insert_8', '1234567890', 'this is a note.', 'ACTIVE', '[{"ordinal": 0, "name": "first_contact", "phone_number": "1234567890", "removed": false}, {"ordinal": 1, "name": "first_contact2", "phone_number": "1234567891", "removed": false}]')

Note: The Version field cannot be set during INSERT operations.

Update

To update a Vendor record, the Version field is required. The supplied value must match the record's current value for this field.

UPDATE Vendors SET Name = 'updated', AddressAddressLine1 = 'updated', AddressAddressLine2 = 'updated', AddressAddressLine3 = 'updated', AddressLocality = 'updated', AddressSublocality = 'updated', AddressSublocality2 = 'updated', AddressSublocality3 = 'updated', AddressAdministrativeDistrictLevel1 = 'updated', AddressAdministrativeDistrictLevel2 = 'updated', AddressAdministrativeDistrictLevel3 = 'updated', AddressPostalCode = 'updated', AddressCountry = 'AU', AddressFirstName = 'updated', AddressLastName = 'updated', Contacts = '[{"ordinal": 2,"email_address": "[email protected]","name": "updated1","phone_number": "5645645645","removed": false}, {"ordinal": 3,"email_address": "[email protected]","name": "updated2","phone_number": "1233211233","removed": false}]', AccountNumber = '9346', Note = 'updated', Version = 3, Status = 'INACTIVE' WHERE Id = 'YEA4LD6WXX6BJUZT'

Bulk Insert

The maximum BatchSize supported by Vendors is 100.

You can create vendors in bulk using TEMP tables.

INSERT INTO Vendors#TEMP (Name, AddressCountry) VALUES ('s1', 'IN')

INSERT INTO Vendors#TEMP (Name, AddressCountry) VALUES ('s2', 'IN')

INSERT INTO Vendors (Name, AddressCountry) SELECT Name, AddressCountry FROM Vendors#TEMP

Bulk Update