Community
Showing results for 
Search instead for 
Do you mean 
Reply

Lookup field by another field's value

New Member
Posts: 4
Country: United States

Lookup field by another field's value

[ Edited ]

I'm trying to do a lookup where one field is equal to another field, without hardcoding any values.  Let's say I have two fields in the same record, one is called President and the other is called CEO, both contain names.  I need to get all the records where the President name is the same as the CEO.  For example:

 

The President's name is John Smith and he is also the CEO.

Lookup:     President

Search For: John Smith

Another Lookup: CEO

Search For: John Smith

 

I can't afford to spend time doing this for every President name in the database.  Is there a way to lookup fields by matching another field?  Something like:

 

Lookup:    President

Search For: [CEO]

 

Thank you,

NICK

 

EDIT:  We are using ACT! 2005 Premium.

Message Edited by inLighten on 01-27-2009 12:55 PM
New Member
Posts: 4
Country: United States

Re: Lookup field by another field's value

Roy?  ghollister?
Platinum Elite Contributor
Posts: 6,662
Country: USA

Re: Lookup field by another field's value

The query you want can't be done with the ACT! lookups. I do have a suggestion. In your example, when the president and CEO were the same person, only place the name in one field, for example president then you could use a simple lookup of CEO = [empty] and you would have all of the records where the same person filled both jobs.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
New Member
Posts: 4
Country: United States

Re: Lookup field by another field's value

Roy,

 

Thanks for the reply!  Unfortunately, I receive all the data with the fields already filled in.  It looks like my only solution is to export the contacts to Excel and run some conditional formatting or to just hire some people to do the grunt work?

 

Thanks,

NICK

Platinum Elite Contributor
Posts: 6,662
Country: USA

Re: Lookup field by another field's value

You might try this. Export the company, contact, phone, president and CEO fields to Excel. Then delete the contents of the CEO field from all contact records. In Excel, delete all the records where President and CEO are the same. Then import the Excel file back into ACT! With dup checking set for company, contact and phone number. what should happen is that the CEO field in those records where the CEO was different from the president will be filled. Just make a backup of your database before you try this.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Bronze Elite Contributor
Posts: 2,546
Country: New_Zealand

Re: Lookup field by another field's value

If this is an ongoing need we could create a menu item that would do that for you. You would choose the fields you need to compare and just run it and it would give you a list of records where content of field A = content of field B where you choose what fields A and B are from a dropdown.

 

 

 

 

Graeme Leo
Xact Software - consultants and developers
Follow us on Twitter and check out our Blog


New Member
Posts: 4
Country: United States

Re: Lookup field by another field's value


gleo wrote:

If this is an ongoing need we could create a menu item that would do that for you. You would choose the fields you need to compare and just run it and it would give you a list of records where content of field A = content of field B where you choose what fields A and B are from a dropdown.


If you could create an option that allows me to check multiple fields against a single field, that would be awesome!  My ultimate goal is to run a Lookup where none of the desired fields' contents are the same and then use Replace Field to set a checkmark next to the distinct field.  For example:

 

SELECT * FROM Contacts

WHERE (FieldA != FieldB) AND (FieldB != FieldC) AND (FieldC != FieldD)

 

SET FieldAisUnique = TRUE

 

Something along those lines... how do we go about making this happen?

 

Thanks,

NICK

Bronze Elite Contributor
Posts: 2,546
Country: New_Zealand

Re: Lookup field by another field's value

Message sent to you via this forum, refer your new message icon.  

Graeme Leo
Xact Software - consultants and developers
Follow us on Twitter and check out our Blog