Using SuiteQL, How can I find the TRANSACTIONSHIPPINGADDRESS record when I have the internalid of the addressbook?

AI generated answer (using internal Slack threads):

Based on the conversations, it seems that you can't directly load the address record because it is a subrecord. You would need to load the Sales Order customer and from there load the address record. You can create a search to get what you need too, but it's not straightforward. Here is a code sample provided in the conversation that creates a search of the customer addressinternalid as a column compares and added the internal id with a join to get the address internal id:

javascript
var shipaddressSearch = search.create({
  type: recordtype,
  filters: [['internalid', 'is', recordid]],
  columns: [
    search.createColumn({
      name: "internalid",
      join: "Address",
      label: "Internal ID"
    }),
    search.createColumn({
      name: "addressinternalid"
    })
  ]
})

var shipipingAddressResults = shipaddressSearch.run().getRange({
  start: 0,
  end: 1000
})

shipipingAddressResults.forEach(function (shipaddress) {
  console.log("EXTRENAL IDDDD", shipaddress.getValue("addressinternalid"));
  if (shipaddress.getValue("addressinternalid") == warehouseLocation) {
    warehouseLocation = shipaddress.getValue({ name: 'internalid', join: 'Address' });
  }
})

In another conversation, a SQL query was provided to get addresses for a customer:

sql
SELECT customer.id, customer.entityid,
customeraddressbook.internalid AS addressbook_id,
customeraddressbookentityaddress.addrText

FROM customer
JOIN customeraddressbook ON customeraddressbook.entity = customer.id
JOIN customeraddressbookentityaddress ON customeraddressbook.addressbookaddress = customeraddressbookentityaddress.nkey

WHERE customer.id = 265

This query joins the

customer

,

customeraddressbook

, and

customeraddressbookentityaddress

tables to get the address details. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******