Customers
Create, update, delete, and query the available customers in Square.
Table Specific Information
Select
The provider 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 provider.
- Id supports the following operators: =, IN.
- Email supports the following operator: =.
- CreatedAt supports the following operators: =, >=, <=, >, <.
- UpdatedAt supports the following operators: =, >=, <=, >, <.
- ReferenceId supports the following operator: =.
- Segments supports the following operators: =, IN.
- CreationSource supports the following operators: =.
- GroupIds supports the following operators: =, IN.
Retrieve all customers:
SELECT * FROM Customers
Retrieve a specific customer:
SELECT * FROM Customers WHERE Id = '0SY145EEWD0YXCW489VV386KH8'
Retrieve several customers at once by supplying their Ids with IN. The provider sends these to the Square bulk retrieve endpoint, so the list is served by a single request rather than one request per Id. A list longer than the maximum BatchSize is split across as many requests as needed. Note that = does not use this endpoint; a single-Id filter is answered by the per-customer request instead.
SELECT * FROM Customers WHERE Id IN ('W4JF00NKDQ857310HB7NNN74T4', 'Z3G4NNA8WCFKM2R8T32XAHWT1M')
An Id that does not match a customer is omitted from the results rather than reported as an error, so the row count can be lower than the number of Ids supplied. Any other predicate in the WHERE clause is applied client-side, and LIMIT does not restrict a bulk retrieve, which always returns the customers named in the Id list.
Insert
To create a customer, you must specify at least one of the following values: GivenName, FamilyName, CompanyName, EmailAddress, or PhoneNumber.
INSERT INTO Customers (GivenName, FamilyName, Email, PhoneNumber, AddressLine1, AddressLine2, AddressLocality, AddressAdministrativeDistrictLevel1, AddressPostalCode, AddressCountry) VALUES ('John', 'Doe', '[email protected]', '1-212-555-4240', '500 Electric Ave', 'Suite 600', 'New York', 'NY', '10003', 'US')
Update
Modify the details of an existing customer by providing the Id of the customer:
UPDATE Customers SET GivenName = 'Amelia', FamilyName = 'Earhart' WHERE Id = '32123'
Delete
Delete a customer from a business, along with any linked cards on file, by providing the Id of the customer:
DELETE FROM Customers WHERE Id = '32123'
Bulk Insert
The maximum BatchSize supported by Customers is 100.
You can create customers in bulk using TEMP tables.
INSERT INTO Customers#TEMP (GivenName, FamilyName, Note, ReferenceId) VALUES ('BulkA', 'TestOne', 'bulk probe 1', 'ref-bulk-a')
INSERT INTO Customers#TEMP (GivenName, FamilyName, Note, ReferenceId) VALUES ('BulkB', 'TestTwo', 'bulk probe 2', 'ref-bulk-b')
INSERT INTO Customers (GivenName, FamilyName, Note, ReferenceId) SELECT GivenName, FamilyName, Note, ReferenceId FROM Customers#TEMP
Bulk Update
The maximum BatchSize supported by Customers is 100.
Customers can be updated in bulk using their Ids.
INSERT INTO Customers#TEMP (Id, GivenName, ReferenceId, Note) VALUES ('W4JF00NKDQ857310HB7NNN74T4', 'BulkAUpd', 'ref-upd-a', 'updated one')
INSERT INTO Customers#TEMP (Id, GivenName, ReferenceId, Note) VALUES ('Z3G4NNA8WCFKM2R8T32XAHWT1M', 'BulkBUpd', 'ref-upd-b', 'updated two')
UPDATE Customers (Id, GivenName, ReferenceId, Note) SELECT Id, GivenName, ReferenceId, Note FROM Customers#TEMP
Square reports the outcome of each row in the batch separately. Query LastResultInfo#TEMP after the update to read the per-row Success, AffectedRows, and Id values, along with ErrorStatusCode, ErrorMessage, and ErrorDescription for any row that failed.
SELECT * FROM LastResultInfo#TEMP
Bulk Delete
The maximum BatchSize supported by Customers is 100.
Stage the Ids of the customers to remove in a TEMP table, then delete them in a single request. Only the Id is needed on each staged row.
INSERT INTO Customers#TEMP (Id) VALUES ('Z3G4NNA8WCFKM2R8T32XAHWT1M')
INSERT INTO Customers#TEMP (Id) VALUES ('W4JF00NKDQ857310HB7NNN74T4')
DELETE FROM Customers WHERE EXISTS SELECT Id FROM Customers#TEMP
Note: The staged form shown above is required to reach the bulk delete endpoint. A DELETE FROM Customers WHERE Id IN (...) statement removes the same customers but issues one request per Id. The same applies to bulk UPDATE, which must also read its rows from the TEMP table.
Columns
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
The Id of the customer. |
| GivenName | String | False |
The customer's given (i.e., first) name. |
| FamilyName | String | False |
The customer's family (i.e., last) name. |
| Nickname | String | False |
The customer's nickname. |
| String | False |
The customer's email address. | |
| CompanyName | String | False |
The name of the customer's company. |
| PhoneNumber | String | False |
The customer's phone number. |
| AddressLine1 | String | False |
The first line of the address. |
| AddressLine2 | String | False |
The second line of the address. |
| AddressLine3 | String | False |
The third line of the address. |
| AddressLocality | String | False |
The city or town of the address. |
| AddressSubLocality | String | False |
A civil region within the address's locality. |
| AddressSubLocality2 | String | False |
A civil region within the address's sublocality. |
| AddressSubLocality3 | String | False |
A civil region within the address's sublocality_2. |
| AddressAdministrativeDistrictLevel1 | String | False |
A civil entity within the address's country. In the US, this is the state. |
| AddressAdministrativeDistrictLevel2 | String | False |
A civil entity within the address's administrative_district_level_1. In the US, this is the county. |
| AddressAdministrativeDistrictLevel3 | String | False |
A civil entity within the address's administrative_district_level_2. |
| AddressPostalCode | String | False |
The address's postal code. |
| AddressCountry | String | False |
The address's country, in ISO 3166-1-alpha-2 format. |
| AddressFirstName | String | False |
Optional first name when it's representing recipient. |
| AddressLastName | String | False |
Optional last name when it's representing recipient. |
| Birthday | String | False |
The birthday associated with the customer profile, in RFC 3339 format. The year is optional. Timezone and times are not allowed. |
| CreatedAt | Datetime | True |
The time when the customer was created. |
| UpdatedAt | Datetime | True |
The time when the customer was last updated. |
| ReferenceId | String | False |
A second Id you can set to associate the customer with an entity in another system. |
| Note | String | False |
A note to associate with the customer. |
| Preferences | String | True |
The customer's preferences. |
| Segments | String | True |
The Ids of segments the customer belongs to. |
| CreationSource | String | True |
The method used to create the customer profile. The allowed values are OTHER, APPOINTMENTS, COUPON, DELETION_RECOVERY, DIRECTORY, EGIFTING, EMAIL_COLLECTION, FEEDBACK, IMPORT, INVOICES, LOYALTY, MARKETING, MERGE, ONLINE_STORE, INSTANT_PROFILE, TERMINAL, THIRD_PARTY, THIRD_PARTY_IMPORT, UNMERGE_RECOVERY. |
| GroupIds | String | True |
The Ids of customer groups the customer belongs to. |
| Version | Int64 | False |
The version of the object. When updating an object, the version supplied must match the version in the database, otherwise the write will be rejected as conflicting. |
| TaxIds | String | False |
The tax Id associated with the customer profile. |