3 Replies Latest reply on Dec 16, 2009 8:29 AM by philmodjunk

# Help me write this calculation....

### Title

Help me write this calculation....

### Post

I have two tables: Customer, and AddressLineItem. Customer has fields like CustomerID, FirstName, LastName, etc. AddressLineItem has fields like CustomerID (key), Address Type, Address Line 1, Address Line 2, City, State, Zipcode

A Customer may have multiple address records, or just one. For example:

Amy Smith

Mailing = 123 Happy St. Anytown NY 12345

Bob Barker

Mailing = 100 Maple Lane Boston MA 01234

Job Site = 5505 Oceanview Drive Hyannis MA 02673

I want to create a calculation that will do the following:

Look at all the address records for one customerID

Determine if an address record exists where type = Job Site - if one exists, set a variable named \$JobSite with that address

If not, set a variable named \$JobSite using the address record where type = Mailing

So in Amy Smith's case, the \$JobSite variable would = 123 Happy St.

In Bob Barker's case, the \$JobSite variable would = 5505 Oceanview Drive

Any and all help is greatly appreciated.

• ###### 1. Re: Help me write this calculation....

Define a text calculation in Customer table, JSKey, that returns the text "Job Site"

Define a second relationship between Customer and AddressLineItem:

You'll need to create a second table occurrence of AddressLineItem to do this.

Now the calculation can be defined as:

You can define this as a calculation field or put it in a script step:

Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

Table vs. Table Occurrence (Tutorial)

• ###### 2. Re: Help me write this calculation....

Hey Phil,

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.

• ###### 3. Re: Help me write this calculation....

Sorry, I missed that detail.

In that case, I'd specify a sort order on the relationship that lists the addresslines in this order:

Job Site

Mailing

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: