Define a text calculation in Customer table, JSKey, that returns the text "Job Site"
Define a second relationship between Customer and AddressLineItem:
Customer::CustomerID = AddressLineItem 2::CustomerID AND
Customer::JSKey = AddressLineItem 2::Address Type
You'll need to create a second table occurrence of AddressLineItem to do this.
Now the calculation can be defined as:
AddressLineItem 2::Address Line 1
You can define this as a calculation field or put it in a script step:
Set Variable[$JobSite; Value: AddressLineItem 2::Address Line 1 ]
Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:
Table vs. Table Occurrence (Tutorial)
Thanks for the reply. I understand the difference between Tables and TO, thankfully.
I'm looking at your suggestion, but I dont see how this will deal with those customers who only have one address that is of type = "Mailing"...
Maybe I'm just blind... did I miss something?
The whole point of this is that when a salesperson creates a Proposal for a customer, the Proposal needs to show both a mailing and job site address. In those customers where the mailing address IS the job site address, then the Proposal needs to show the same address in both mailing and job site.
Sorry, I missed that detail.
In that case, I'd specify a sort order on the relationship that lists the addresslines in this order:
If these are you're only two values, simply specifying an alphabetical sort order based on this field will do the trick. (This is set in the Edit Relationship Dialog.)
Now a reference to your existing relationship will do the job:
Set Variable[$JobSite; Value: AddressLineItem::Address Line 1 ]
This works because a reference to a child table with more than one matching record will return the "first" matching record and the relationship's sort order can be used to determine what record is "first".