I feel like I must be missing something as this is such a basic thing, but I cannot find a way to do it without SuiteScript:
How can I join a transaction to a custom record that does not have a parent/child relationship?
The SQL equivalent is almost as basic as it gets: SELECT custom_record.field_2 FROM custom_record WHERE custom_record.field_1 = transaction.field
In my case, I am trying to lookup a custom record using the Ship To Zip on the Sales Order. Since Zip is not a list/record type in NS (seems like it is all completely hard-coded), how can I link the Sales Order's Ship To Zip to a custom field on the custom record called Zip in order to return the value of another field on the custom record, in this case, Delivery Route?
Example:
Sales Order Ship To Zip is 12345 with a custom field called Delivery Route
The Delivery Routes custom table has records:
Zip Delivery Route
54321 West
12345 East
When Ship To is selected (or if it needs to occur on After Record Submit, that is fine), the Sales Order Delivery Route field should be populated with the value 'East'.