How to restrick a Lookup to only related records

We are in the experimenting stages of CRM and have come across a 
customization we would like to implement.  Many of or accounts have multiple 
contacts.  We can obviously set the primary contact and all is well.  The 
problem that we have is that when we wantt o change the primary contact and 
click on the look up we see all of the contacts in the system and not just 
those that have the account as their parent account.   We are wondering if 
there is a way to customize so that when we click on the lookup contact from 
the account form it will display only those contacts that have the account as 
their parent account.

Thanks

Scott

0
11/28/2009 9:53:01 PM
crm 35858 articles. 1 followers. Follow

8 Replies
749 Views

Similar Articles

[PageSpeed] 14

Yes, this can be done, there are two parts to making this work.
Firstly, customise your Contact entity > Lookup view > click on "Add 
Find Columns" > tick Parent Customer
Save changes and don't forget to publish.
So, now when you do a lookup for primary contact (or any other lookup 
for a contact) you can search by parent company name as well as the 
person's own name. This is useful on it's own (eg to assign a Case to a 
contact it might be easier to find them by searching for the company).

So the second step is to do this lookup automatically.
Customise the Account entity main form, edit the form properties and add 
an onLoad event (don't forget to enable it as well by ticking the box) 
with the following single line of code (this might be broken across 
lines in the newsgroup depending on your word wrapping, just enter as 
one line):
crmForm.all.primarycontactid.additionalparams = 'search=' + 
crmForm.all.name.value;

This adds a parameter to the primarycontactid field to pass to the 
search box when the lookup is invoked, the parameter being the field 
"name" which is the Account Name of the Account you are looking at.
Save and close, publish Account entity.

Now try and use the primary contact lookup from an Account form and you 
should only see contacts who you have already identified as working for 
that company (ie this is their parent account).
Notice that you can see the account name in the search box at the top 
right, this is just a normal search, you have done nothing really odd 
here other than saved time and ensured accuracy of entry.

If you are certain the primary contact should be someone not associated 
with the account you can still add them (maybe they just don't have 
parent account set yet or set incorrectly, or the primary contact works 
for a parent / child / sister company but is still the main decision 
maker for this subsidiary). Simply clear the search box and look for a 
name in the usual way. In other words, the customisation outlined above 
changes the default behaviour but does not 'lock you in' or break the 
normal built in behaviour.

I hope this helps
Adam Vero
MCT, MBMSS, MCSA

BladesAway wrote:
> We are in the experimenting stages of CRM and have come across a 
> customization we would like to implement.  Many of or accounts have multiple 
> contacts.  We can obviously set the primary contact and all is well.  The 
> problem that we have is that when we wantt o change the primary contact and 
> click on the look up we see all of the contacts in the system and not just 
> those that have the account as their parent account.   We are wondering if 
> there is a way to customize so that when we click on the lookup contact from 
> the account form it will display only those contacts that have the account as 
> their parent account.
> 
> Thanks
> 
> Scott
> 
0
nospam966 (79)
11/28/2009 11:30:14 PM
Thank you.  That worked perfectly!

"AdamV" wrote:

> Yes, this can be done, there are two parts to making this work.
> Firstly, customise your Contact entity > Lookup view > click on "Add 
> Find Columns" > tick Parent Customer
> Save changes and don't forget to publish.
> So, now when you do a lookup for primary contact (or any other lookup 
> for a contact) you can search by parent company name as well as the 
> person's own name. This is useful on it's own (eg to assign a Case to a 
> contact it might be easier to find them by searching for the company).
> 
> So the second step is to do this lookup automatically.
> Customise the Account entity main form, edit the form properties and add 
> an onLoad event (don't forget to enable it as well by ticking the box) 
> with the following single line of code (this might be broken across 
> lines in the newsgroup depending on your word wrapping, just enter as 
> one line):
> crmForm.all.primarycontactid.additionalparams = 'search=' + 
> crmForm.all.name.value;
> 
> This adds a parameter to the primarycontactid field to pass to the 
> search box when the lookup is invoked, the parameter being the field 
> "name" which is the Account Name of the Account you are looking at.
> Save and close, publish Account entity.
> 
> Now try and use the primary contact lookup from an Account form and you 
> should only see contacts who you have already identified as working for 
> that company (ie this is their parent account).
> Notice that you can see the account name in the search box at the top 
> right, this is just a normal search, you have done nothing really odd 
> here other than saved time and ensured accuracy of entry.
> 
> If you are certain the primary contact should be someone not associated 
> with the account you can still add them (maybe they just don't have 
> parent account set yet or set incorrectly, or the primary contact works 
> for a parent / child / sister company but is still the main decision 
> maker for this subsidiary). Simply clear the search box and look for a 
> name in the usual way. In other words, the customisation outlined above 
> changes the default behaviour but does not 'lock you in' or break the 
> normal built in behaviour.
> 
> I hope this helps
> Adam Vero
> MCT, MBMSS, MCSA
> 
> BladesAway wrote:
> > We are in the experimenting stages of CRM and have come across a 
> > customization we would like to implement.  Many of or accounts have multiple 
> > contacts.  We can obviously set the primary contact and all is well.  The 
> > problem that we have is that when we wantt o change the primary contact and 
> > click on the look up we see all of the contacts in the system and not just 
> > those that have the account as their parent account.   We are wondering if 
> > there is a way to customize so that when we click on the lookup contact from 
> > the account form it will display only those contacts that have the account as 
> > their parent account.
> > 
> > Thanks
> > 
> > Scott
> > 
> .
> 
0
12/2/2009 2:15:01 AM
You're welcome!

BladesAway wrote:
> Thank you.  That worked perfectly!
> 
> "AdamV" wrote:
> 
>> Yes, this can be done, there are two parts to making this work.
>> Firstly, customise your Contact entity > Lookup view > click on "Add 
>> Find Columns" > tick Parent Customer
>> Save changes and don't forget to publish.
>> So, now when you do a lookup for primary contact (or any other lookup 
>> for a contact) you can search by parent company name as well as the 
>> person's own name. This is useful on it's own (eg to assign a Case to a 
>> contact it might be easier to find them by searching for the company).
>>
>> So the second step is to do this lookup automatically.
>> Customise the Account entity main form, edit the form properties and add 
>> an onLoad event (don't forget to enable it as well by ticking the box) 
>> with the following single line of code (this might be broken across 
>> lines in the newsgroup depending on your word wrapping, just enter as 
>> one line):
>> crmForm.all.primarycontactid.additionalparams = 'search=' + 
>> crmForm.all.name.value;
>>
>> This adds a parameter to the primarycontactid field to pass to the 
>> search box when the lookup is invoked, the parameter being the field 
>> "name" which is the Account Name of the Account you are looking at.
>> Save and close, publish Account entity.
>>
>> Now try and use the primary contact lookup from an Account form and you 
>> should only see contacts who you have already identified as working for 
>> that company (ie this is their parent account).
>> Notice that you can see the account name in the search box at the top 
>> right, this is just a normal search, you have done nothing really odd 
>> here other than saved time and ensured accuracy of entry.
>>
>> If you are certain the primary contact should be someone not associated 
>> with the account you can still add them (maybe they just don't have 
>> parent account set yet or set incorrectly, or the primary contact works 
>> for a parent / child / sister company but is still the main decision 
>> maker for this subsidiary). Simply clear the search box and look for a 
>> name in the usual way. In other words, the customisation outlined above 
>> changes the default behaviour but does not 'lock you in' or break the 
>> normal built in behaviour.
>>
>> I hope this helps
>> Adam Vero
>> MCT, MBMSS, MCSA
>>
>> BladesAway wrote:
>>> We are in the experimenting stages of CRM and have come across a 
>>> customization we would like to implement.  Many of or accounts have multiple 
>>> contacts.  We can obviously set the primary contact and all is well.  The 
>>> problem that we have is that when we wantt o change the primary contact and 
>>> click on the look up we see all of the contacts in the system and not just 
>>> those that have the account as their parent account.   We are wondering if 
>>> there is a way to customize so that when we click on the lookup contact from 
>>> the account form it will display only those contacts that have the account as 
>>> their parent account.
>>>
>>> Thanks
>>>
>>> Scott
>>>
>> .
>>
0
nospam966 (79)
12/3/2009 4:03:05 PM
Hey Adam - thanks for the nugget - I just had a requirement that the customer 
opt out of the filter, and this is a perfect solution!

Michael D. Mayo

"AdamV" wrote:

> You're welcome!
> 
> BladesAway wrote:
> > Thank you.  That worked perfectly!
> > 
> > "AdamV" wrote:
> > 
> >> Yes, this can be done, there are two parts to making this work.
> >> Firstly, customise your Contact entity > Lookup view > click on "Add 
> >> Find Columns" > tick Parent Customer
> >> Save changes and don't forget to publish.
> >> So, now when you do a lookup for primary contact (or any other lookup 
> >> for a contact) you can search by parent company name as well as the 
> >> person's own name. This is useful on it's own (eg to assign a Case to a 
> >> contact it might be easier to find them by searching for the company).
> >>
> >> So the second step is to do this lookup automatically.
> >> Customise the Account entity main form, edit the form properties and add 
> >> an onLoad event (don't forget to enable it as well by ticking the box) 
> >> with the following single line of code (this might be broken across 
> >> lines in the newsgroup depending on your word wrapping, just enter as 
> >> one line):
> >> crmForm.all.primarycontactid.additionalparams = 'search=' + 
> >> crmForm.all.name.value;
> >>
> >> This adds a parameter to the primarycontactid field to pass to the 
> >> search box when the lookup is invoked, the parameter being the field 
> >> "name" which is the Account Name of the Account you are looking at.
> >> Save and close, publish Account entity.
> >>
> >> Now try and use the primary contact lookup from an Account form and you 
> >> should only see contacts who you have already identified as working for 
> >> that company (ie this is their parent account).
> >> Notice that you can see the account name in the search box at the top 
> >> right, this is just a normal search, you have done nothing really odd 
> >> here other than saved time and ensured accuracy of entry.
> >>
> >> If you are certain the primary contact should be someone not associated 
> >> with the account you can still add them (maybe they just don't have 
> >> parent account set yet or set incorrectly, or the primary contact works 
> >> for a parent / child / sister company but is still the main decision 
> >> maker for this subsidiary). Simply clear the search box and look for a 
> >> name in the usual way. In other words, the customisation outlined above 
> >> changes the default behaviour but does not 'lock you in' or break the 
> >> normal built in behaviour.
> >>
> >> I hope this helps
> >> Adam Vero
> >> MCT, MBMSS, MCSA
> >>
> >> BladesAway wrote:
> >>> We are in the experimenting stages of CRM and have come across a 
> >>> customization we would like to implement.  Many of or accounts have multiple 
> >>> contacts.  We can obviously set the primary contact and all is well.  The 
> >>> problem that we have is that when we wantt o change the primary contact and 
> >>> click on the look up we see all of the contacts in the system and not just 
> >>> those that have the account as their parent account.   We are wondering if 
> >>> there is a way to customize so that when we click on the lookup contact from 
> >>> the account form it will display only those contacts that have the account as 
> >>> their parent account.
> >>>
> >>> Thanks
> >>>
> >>> Scott
> >>>
> >> .
> >>
> .
> 
0
bayareacrm (90)
12/9/2009 10:33:21 PM
That's what I like about public forums - an answer to help one person 
can carry on being useful to others who come along too.

bayareacrm wrote:
> Hey Adam - thanks for the nugget - I just had a requirement that the customer 
> opt out of the filter, and this is a perfect solution!
> 
> Michael D. Mayo
> 
> "AdamV" wrote:
> 
>> You're welcome!
>>
>> BladesAway wrote:
>>> Thank you.  That worked perfectly!
>>>
>>> "AdamV" wrote:
>>>
>>>> Yes, this can be done, there are two parts to making this work.
>>>> Firstly, customise your Contact entity > Lookup view > click on "Add 
>>>> Find Columns" > tick Parent Customer
>>>> Save changes and don't forget to publish.
>>>> So, now when you do a lookup for primary contact (or any other lookup 
>>>> for a contact) you can search by parent company name as well as the 
>>>> person's own name. This is useful on it's own (eg to assign a Case to a 
>>>> contact it might be easier to find them by searching for the company).
>>>>
>>>> So the second step is to do this lookup automatically.
>>>> Customise the Account entity main form, edit the form properties and add 
>>>> an onLoad event (don't forget to enable it as well by ticking the box) 
>>>> with the following single line of code (this might be broken across 
>>>> lines in the newsgroup depending on your word wrapping, just enter as 
>>>> one line):
>>>> crmForm.all.primarycontactid.additionalparams = 'search=' + 
>>>> crmForm.all.name.value;
>>>>
>>>> This adds a parameter to the primarycontactid field to pass to the 
>>>> search box when the lookup is invoked, the parameter being the field 
>>>> "name" which is the Account Name of the Account you are looking at.
>>>> Save and close, publish Account entity.
>>>>
>>>> Now try and use the primary contact lookup from an Account form and you 
>>>> should only see contacts who you have already identified as working for 
>>>> that company (ie this is their parent account).
>>>> Notice that you can see the account name in the search box at the top 
>>>> right, this is just a normal search, you have done nothing really odd 
>>>> here other than saved time and ensured accuracy of entry.
>>>>
>>>> If you are certain the primary contact should be someone not associated 
>>>> with the account you can still add them (maybe they just don't have 
>>>> parent account set yet or set incorrectly, or the primary contact works 
>>>> for a parent / child / sister company but is still the main decision 
>>>> maker for this subsidiary). Simply clear the search box and look for a 
>>>> name in the usual way. In other words, the customisation outlined above 
>>>> changes the default behaviour but does not 'lock you in' or break the 
>>>> normal built in behaviour.
>>>>
>>>> I hope this helps
>>>> Adam Vero
>>>> MCT, MBMSS, MCSA
>>>>
>>>> BladesAway wrote:
>>>>> We are in the experimenting stages of CRM and have come across a 
>>>>> customization we would like to implement.  Many of or accounts have multiple 
>>>>> contacts.  We can obviously set the primary contact and all is well.  The 
>>>>> problem that we have is that when we wantt o change the primary contact and 
>>>>> click on the look up we see all of the contacts in the system and not just 
>>>>> those that have the account as their parent account.   We are wondering if 
>>>>> there is a way to customize so that when we click on the lookup contact from 
>>>>> the account form it will display only those contacts that have the account as 
>>>>> their parent account.
>>>>>
>>>>> Thanks
>>>>>
>>>>> Scott
>>>>>
>>>> .
>>>>
>> .
>>
0
nospam966 (79)
12/9/2009 11:30:26 PM
Hi Adam,

I have also tried your suggestion, and while it works in most cases, if 
fails if the Customer Name contains an "&" sign.
There might be other character that makes it fail as well, but this one was 
one I saw last week.Maybe there is a way to get around this?


-- 
Regards
Steen Schl�ter Persson (DK)


"AdamV" <nospam@meteorit.co.uk> wrote in message 
news:%23C8oXeSeKHA.4636@TK2MSFTNGP04.phx.gbl...
> That's what I like about public forums - an answer to help one person can 
> carry on being useful to others who come along too.
>
> bayareacrm wrote:
>> Hey Adam - thanks for the nugget - I just had a requirement that the 
>> customer opt out of the filter, and this is a perfect solution!
>>
>> Michael D. Mayo
>>
>> "AdamV" wrote:
>>
>>> You're welcome!
>>>
>>> BladesAway wrote:
>>>> Thank you.  That worked perfectly!
>>>>
>>>> "AdamV" wrote:
>>>>
>>>>> Yes, this can be done, there are two parts to making this work.
>>>>> Firstly, customise your Contact entity > Lookup view > click on "Add 
>>>>> Find Columns" > tick Parent Customer
>>>>> Save changes and don't forget to publish.
>>>>> So, now when you do a lookup for primary contact (or any other lookup 
>>>>> for a contact) you can search by parent company name as well as the 
>>>>> person's own name. This is useful on it's own (eg to assign a Case to 
>>>>> a contact it might be easier to find them by searching for the 
>>>>> company).
>>>>>
>>>>> So the second step is to do this lookup automatically.
>>>>> Customise the Account entity main form, edit the form properties and 
>>>>> add an onLoad event (don't forget to enable it as well by ticking the 
>>>>> box) with the following single line of code (this might be broken 
>>>>> across lines in the newsgroup depending on your word wrapping, just 
>>>>> enter as one line):
>>>>> crmForm.all.primarycontactid.additionalparams = 'search=' + 
>>>>> crmForm.all.name.value;
>>>>>
>>>>> This adds a parameter to the primarycontactid field to pass to the 
>>>>> search box when the lookup is invoked, the parameter being the field 
>>>>> "name" which is the Account Name of the Account you are looking at.
>>>>> Save and close, publish Account entity.
>>>>>
>>>>> Now try and use the primary contact lookup from an Account form and 
>>>>> you should only see contacts who you have already identified as 
>>>>> working for that company (ie this is their parent account).
>>>>> Notice that you can see the account name in the search box at the top 
>>>>> right, this is just a normal search, you have done nothing really odd 
>>>>> here other than saved time and ensured accuracy of entry.
>>>>>
>>>>> If you are certain the primary contact should be someone not 
>>>>> associated with the account you can still add them (maybe they just 
>>>>> don't have parent account set yet or set incorrectly, or the primary 
>>>>> contact works for a parent / child / sister company but is still the 
>>>>> main decision maker for this subsidiary). Simply clear the search box 
>>>>> and look for a name in the usual way. In other words, the 
>>>>> customisation outlined above changes the default behaviour but does 
>>>>> not 'lock you in' or break the normal built in behaviour.
>>>>>
>>>>> I hope this helps
>>>>> Adam Vero
>>>>> MCT, MBMSS, MCSA
>>>>>
>>>>> BladesAway wrote:
>>>>>> We are in the experimenting stages of CRM and have come across a 
>>>>>> customization we would like to implement.  Many of or accounts have 
>>>>>> multiple contacts.  We can obviously set the primary contact and all 
>>>>>> is well.  The problem that we have is that when we wantt o change the 
>>>>>> primary contact and click on the look up we see all of the contacts 
>>>>>> in the system and not just those that have the account as their 
>>>>>> parent account.   We are wondering if there is a way to customize so 
>>>>>> that when we click on the lookup contact from the account form it 
>>>>>> will display only those contacts that have the account as their 
>>>>>> parent account.
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> Scott
>>>>>>
>>>>> .
>>>>>
>>> .
>>> 


0
steen1 (38)
12/15/2009 3:02:48 PM
On Dec 15 2009, 10:02=A0am, "Steen Schl=FCter Persson"
<st...@REMOVETHISasavaenget.dk> wrote:
> Hi Adam,
>
> I have also tried your suggestion, and while it works in most cases, if
> fails if the Customer Name contains an "&" sign.
> There might be other character that makes it fail as well, but this one w=
as
> one I saw last week.Maybe there is a way to get around this?

This works to eliminate the &:
crmForm.all.primarycontactid.additionalparams =3D 'search=3D' +
encodeURIComponent(crmForm.all.name.DataValue);
0
jdwight (7)
1/6/2010 5:24:38 PM
Nice tip, thanks for the extra detail, I'll add that to my 'bag of tricks'
Adam

jdubb wrote:
> On Dec 15 2009, 10:02 am, "Steen Schl�ter Persson"
> <st...@REMOVETHISasavaenget.dk> wrote:
>> Hi Adam,
>>
>> I have also tried your suggestion, and while it works in most cases, if
>> fails if the Customer Name contains an "&" sign.
>> There might be other character that makes it fail as well, but this one was
>> one I saw last week.Maybe there is a way to get around this?
> 
> This works to eliminate the &:
> crmForm.all.primarycontactid.additionalparams = 'search=' +
> encodeURIComponent(crmForm.all.name.DataValue);
0
nospam966 (79)
1/7/2010 11:43:34 AM
Reply:

Similar Artilces:

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

No record selection pleaseeee ...
Hello: I have a form with 2 sub forms in/on it. For whatever reason, on both sub-forms, 1 record on each is active or current ( the right triangle ). Is there any way to not have a current record selected or active? I guess there has to be a current record or BOF flag, but some of us are strange people and I was just wondering if it is possible not to have it. Thanks In news:eTb3edQ4HHA.5424@TK2MSFTNGP02.phx.gbl, John DOE <someone@somewhere.com> wrote: > Hello: > > I have a form with 2 sub forms in/on it. For whatever reason, on both > sub-forms, 1 record on eac...

Safe Pay
I need to place a hash total of the check numbers in the footer record of my Safe Pay file. This is per the file specifications from our bank. I have been unsuccessful in getting the Safe Pay configurator to handle this. Has anyone else had this issue? ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

question related to Data Vaildation
Dear all, I have two columns. Each column contains a defined value and each value carries some predefined details. I am trying to place a dropdown through data validation, so that i select any value, i should be getting all the details related to that value without any extended space. Like below-: A operations downtime client figure B ratings flow chart technical support If i select A, I want to get the details of A as dropdown, i select B, i want to get the details of B. Kindly help ! Refer the below by Debra Dalgleish ..(you will need to fill the blank cells ...

how to display single database records into forms for printing
I have info stored in a csv format, viewable by excel. I want to take each individual record of that database and put it into a form suitable for printing. And then print each record. I will also need to do this on a regular basis, using data in the same format, but with different numbers of records in each database. Is there an easy way to do this? It's called Mail Merge: http://www.officearticles.com/word/mail_merge_letters_in_microsoft_word.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "need help" <need help@discussions.micros...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

Opening a form and subform to a specific record
I am using the time and billing database and I would like to be able to open an employee timesheet data form only for a specific person. I would like for an employee to only be able to view their time card and not others how can I do that. Use the WhereCondition argument of the OpenForm command. What Time and Billing db? LT AREA wrote: >I am using the time and billing database and I would like to be able to open >an employee timesheet data form only for a specific person. I would like for >an employee to only be able to view their time card and not others how can I >do t...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Save Record
In a modal pop up form's subform I wish to save a record without having to exit it so that a report reflects the current data in a control in the subform. Can anybody help please. -- Grimwadec "Grimwadec" <Grimwadec@discussions.microsoft.com> wrote in message news:35553A46-0359-44E1-9494-40A730112C26@microsoft.com... > In a modal pop up form's subform I wish to save a record without having to > exit it so that a report reflects the current data in a control in the > subform. Can anybody help please. > -- > Grimwadec In code behind th...

Search for Record in Form, Report
I would like the capability of searching the table or query on which my forms and reports are based, to select a certain record in the source. It sounds like it's a pretty sophisticated thing to do. Is there a way I can do this? Preferably "on the way into" the form or report, now while I'm in it. Don. That is pretty basic, not sophisticated at all. In fact, you can do it via Access's own toolbar, click Edit then Find. You can also code your own Find function and in that case it could get quite complex depending on what you want to do. -- Dorian &...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Calling employee lookup from button through VBA code
Dear All, Can anyone show me how to call an existing GP employee lookup from a button of a modified form through VBA code. Thanks in advance. -- Developer Hi, If I'm understanding the question - you need to add the lookup button to your project and make sure your project provides that it runs on the modified form. Leslie "Dexdev" wrote: > Dear All, > > Can anyone show me how to call an existing GP employee lookup from a button > of a modified form through VBA code. > > > Thanks in advance. > > -- > Developer Hello Dexdev As per...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

Relative sheet reference
Is there a way to reference sheets not by itn's name but by its relative position? 1st sheet name = 'Report' 2nd sheet name = 'Data1' 3rd sheet name = 'Data2' I'd like to refernece data in sheet 'Data2' relatively. Somthing like Sheet(3)!A4 /Morten Hi try .... dim rng as range set rng = worksheets(3).range("A4") msgbox rng.value .... -- Regards Frank Kabel Frankfurt, Germany hansiman wrote: > Is there a way to reference sheets not by itn's name but by its > relative position? > > 1st sheet name = 'Report' > 2...

Recording a spin-off in MS Money 2007 de luxe
When I try to record a spin-off using the function that purports to do that, in the investment account Money "removes" the parent shares then "adds" back the parent shares with their assigned basis and the spun-off shares with their share of the basis. All well and good, if a little bit clunky (my guess is that the new holdings will be deemed to be acquired on the spin-off date rather than the original acquisition date). But here's my problem. For some reason Money seems to think that it should also decrement the associated cash account by the amount of the cos...

Lookup #4
I am trying to label my bank info that I import into Excel. I have a column with the charge amount and a column with the recipient. I think a lookup table would work for this, but I need to have it check for wildcards. For example, the grocery chain is numbered here, so one might be GROCER 5454 and one may be GROCER 8724, so I need the function to look for GROCER*, right? Also, if it does not fit any category, how can I get it to use "Other"? Do I make that one just "*"? Mine is not working very well. Maybe my syntax is wrong. Any ideas? TIA Is there anyway you can st...

Filter message by reverse domain lookup
Hello all, I am kinda new to Exchange and was wondering if there was a function built into Exchange 2000 that would "look" at the source domain of the email message and then perform a "reverse" DNS lookup to verify that the domain actually exists and if it doesn't then have Exchange discard the email without sending an NDR? Is this possible strait out of the box? It seems like I remember iMail gateway having this feature. I am trying to filter out some of the spam that fills up my users inboxes. Thanks in advance. Jeremy jsteger@bellsouth.net (Jeremy Steger) wrote...

Record Selectors & Navigation Buttons: Set BackColor?
Anybody know how to coerce the colors of a form's RecordSelectors section and NavigationButtons? I'm guessing there's an API call somewhere, but don't have a clue beyond that. -- PeteCresswell "(PeteCresswell)" <x@y.Invalid> wrote in message news:mlqgr318v13atugcricjmnhbd7fhq4i1qa@4ax.com... > Anybody know how to coerce the colors of a form's RecordSelectors > section and NavigationButtons? > > I'm guessing there's an API call somewhere, but don't have a clue > beyond that. You can replace the standard navigation buttons with...

Can you change the default lookup from Account to Contact?
Is it possible to set the lookup for a customer field to default on "contacts" rather then "accounts?" We do more business with contacts so it is a pain to have to change this all the time. there is no supported way to do this in the current release -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Martin Flaherty" <mflaherty@techpg.com> wrote in message news:ucdNM74uEHA.3416@TK2MSFTNGP09.phx.gbl... > Is it possible to set the lookup for a customer field to default on > "contac...

Formula to lookup named reference based on value
I cant' figure out what the exact words that I'm looking for and if I did I would most likely find the answer. I've got a spreadsheet that has a series of numbers in say column A and it has the numbers 1,2,3,4,5 Further on in the spreadsheet I have named values like CA.1, CA.2, CA. 3 What I want to do is in the formula go: =(X1/SUM(X:X,X,X:X)*CA.2) (for the named range CA.2) Is it possible to have something that goes.... =(X1/SUM(X:X,X,X:X)*"CA"&B4) where B4 = 2 Thus the outcome would be CA.2 ????? =(X1/SUM(X:X,X,X:X)*INDIRECT("CA"&B4)) -- Kind...

lookup drop down
I am making a invoice sheet. With materials on two or more sheets. Materials are plumbing supplies like 2" ABS 90 and 2" ABS coupling with prices the materials, size, type will all be different. I would like to have 3 or more drop downs to pick material, size, and type to get to price. I have looked at vlookup, validation, and dependent drop downs. But I can't figure out what is the best and how to pull info from more than 2 drop downs Thank Roy Hi Roy, I have a workbook example that pretty much does that. Would of course need to be adjusted to your data layout...

Lookup
i am using excel 2 calculate the amount of bonus that an employee i entitled to. i do not know what function to use (i have been told 2 us v lookup but i do not know how 2 go about it). the exact problem is this: there is a basic wage and on top of that an employee gets paid a bonus this bonus is calculated by how many sales r made. the table of th bonuses r given (below): Commission on Qtrly Sales Sales per Qtr Percentage �200,000 and under 0.50 �300,000 and under 0.75 �400,000 and under ...