skcah
Writing / Netsuite

SuiteQL Customer Address Tables Explained

Understand Customer, CustomerAddressBook and CustomerAddressBookEntityAddress and join them correctly in SuiteQL.

Ryan Sheehy2 minute read

NetSuite customer addresses are split across a customer record, an address-book entry and an address-value record. A useful mental model is:

Customer
  -> CustomerAddressBook
       -> CustomerAddressBookEntityAddress

Each table has a different responsibility.

Customer

This is the owning entity:

customer.id
customer.entityid

Joining directly from the customer gives the account or entity information, not the street-address fields.

CustomerAddressBook

This represents an entry in the customer’s address list. It contains relationship-level values such as:

customeraddressbook.id
customeraddressbook.entity
customeraddressbook.label
customeraddressbook.defaultbilling
customeraddressbook.defaultshipping
customeraddressbook.isresidential
customeraddressbook.addressbookaddress

Use its id when you need to identify a particular address-book row. The label is editable display text and is not a reliable unique key.

CustomerAddressBookEntityAddress

This contains the address values reached through addressbookaddress:

customeraddressbookentityaddress.addr1
customeraddressbookentityaddress.addr2
customeraddressbookentityaddress.addr3
customeraddressbookentityaddress.city
customeraddressbookentityaddress.state
customeraddressbookentityaddress.zip
customeraddressbookentityaddress.country

Join it through its key:

LEFT JOIN customeraddressbookentityaddress address_value
  ON address_value.nkey = customeraddressbook.addressbookaddress

Complete join example

SELECT
  customer.id AS customer_id,
  customer.entityid,
  address_book.id AS address_book_id,
  address_book.label,
  address_book.defaultbilling,
  address_book.defaultshipping,
  address_value.addr1,
  address_value.addr2,
  address_value.city,
  address_value.state,
  address_value.zip,
  address_value.country
FROM customer
LEFT JOIN customeraddressbook address_book
  ON address_book.entity = customer.id
LEFT JOIN customeraddressbookentityaddress address_value
  ON address_value.nkey = address_book.addressbookaddress

The LEFT JOINs retain customers without an address. Change them to inner joins when an address is mandatory for the result.

Why field discovery matters

Available records and fields can vary with the execution role and account features. Use the Records Catalog with the same role used by the script or integration, then test a small query before building the full export.

When diagnosing a join, select the relationship keys:

address_book.entity
address_book.addressbookaddress
address_value.nkey

Seeing those values makes a broken join much easier to distinguish from a missing or restricted address.