I have posted a similar question on this topic before, but maybe
someone can help me out with this one...

1.  I understand that I can create a "unique ID" in one table, such as
an auto-populated serial number, and make a relationship with that
field to another table.

2.  In the second table, I understand that I can type in my unique ID,
and establish a relationship with the first table.

3.  I also understand that if I don't want to deal with ID numbers,
then I can create a value list or a drop down list and associate that
with some other field in the table that contains the Unique ID (such as
Name).

4.  Here's my question - suppose I don't want to deal with Unique ID
numbers... they are non-intuitive to the end-user.  But suppose my
table is so large, that I also don't want to deal with drop down lists
or value lists.  Is there a way, using Auto-Complete functionality,
that I can start putting in an entry, FileMaker would check if it is
matching with something in my Name field from the other table, make
auto-complete entries as matches are made, and let me accept an entry
to establish the relationship with?

4a.  Now suppose that no matching entry is found - ie - I am typing in
a new name.  In this case, I would want FileMaker to *create* a new
entry in my other table, and give it a new Unique ID.  (This seems like
it can be done by enabling the allow-to-create-entry-in-related-table
feature).

4b.  Now suppose that two people in my first table have the same name.
I would need a way, either through a portal or some other means, to
select which matching entry I wish to establish the relationship with
(based on Unique ID or some other field, such as Address).

4c.  Finally, suppose again that two people in my first table have the
same name.  But instead of selecting one of the two, I would need a way
to create a brand new person with the same name.

Any ideas?  The key here is that I want to use auto-complete
functionality to create a match with a record from another table.

Thanks!

I will answer only partially to your questions, just to talk about basics.
My advice is to stick on FMP own way to create sequential numbers that will 
be used as a key in a relationship in another table that uses that key. Why, 
because it is the safest way to avoid any problem particularly with 
duplicates. If ever you delete one record, just leave that number out, and 
don't bother.
Now, I agree that for most users such a meaningless figure does not appeal 
much. But it can be made used in the relationships and in the scripts and be 
made hidden to the user. Only you will know it. Then you add an 'ID' the way 
you want that will have a better look, and show it.
With that basic selfgenerated key you can have access to anything existing 
in the corresponding record of that table.
Another piece of advice from me would be to absolutely avoid any 
relationship based on names, because of redondancy, spellings, add-on etc. : 
it can't be trusted as being unique, even if you complicate in adding Zip 
code, telephone number or ... (e.g. say both the wife and the husband are in 
the same data base, and what about McAlley and MacAlley, Du Barry or 
Dubarry, etc.)).
A last point : say you make invoices and you want to keep the address of the 
time of billing, while allowing the firm to move and change its address and 
keep its original ID key. In the script making the invoice, just add a few 
steps in the invoice table using the key : say Address (in Invoice table) = 
Set Field (YrRelationship::address), etc.
Just my 2 cents.
Remi-Noel

<parora2000@gmail.com> a �crit dans le message de news: 
1143391924.080030.59500@i39g2000cwa.googlegroups.com...
>I have posted a similar question on this topic before, but maybe
> someone can help me out with this one...
>
> 1.  I understand that I can create a "unique ID" in one table, such as
> an auto-populated serial number, and make a relationship with that
> field to another table.
>
> 2.  In the second table, I understand that I can type in my unique ID,
> and establish a relationship with the first table.
>
> 3.  I also understand that if I don't want to deal with ID numbers,
> then I can create a value list or a drop down list and associate that
> with some other field in the table that contains the Unique ID (such as
> Name).
>
> 4.  Here's my question - suppose I don't want to deal with Unique ID
> numbers... they are non-intuitive to the end-user.  But suppose my
> table is so large, that I also don't want to deal with drop down lists
> or value lists.  Is there a way, using Auto-Complete functionality,
> that I can start putting in an entry, FileMaker would check if it is
> matching with something in my Name field from the other table, make
> auto-complete entries as matches are made, and let me accept an entry
> to establish the relationship with?
>
> 4a.  Now suppose that no matching entry is found - ie - I am typing in
> a new name.  In this case, I would want FileMaker to *create* a new
> entry in my other table, and give it a new Unique ID.  (This seems like
> it can be done by enabling the allow-to-create-entry-in-related-table
> feature).
>
> 4b.  Now suppose that two people in my first table have the same name.
> I would need a way, either through a portal or some other means, to
> select which matching entry I wish to establish the relationship with
> (based on Unique ID or some other field, such as Address).
>
> 4c.  Finally, suppose again that two people in my first table have the
> same name.  But instead of selecting one of the two, I would need a way
> to create a brand new person with the same name.
>
> Any ideas?  The key here is that I want to use auto-complete
> functionality to create a match with a record from another table.
>
> Thanks!
> 

Too many 'suppose's' for me to follow all your questions, but  stopped at:

"suppose I don't want to deal with Unique ID numbers... they are 
non-intuitive to the end-user. "

ID numbers, acting as primary keys, however they are generated, provide the 
central conceit of relational databases. They are, in fact, not expected to 
be exposed to the end user, but that is no reason to avoid them; there is, 
in fact, no reason to avoid them. Every record in every table in a 
relational database system should have a unique ID. After that, arguments 
begin about the best way to generate them, but all sides point to the same 
goal: uniqueness and inviolability (you can't change the value). My choice 
is auto-entered serial numbers. Most arguments against them involve very 
high performance deployments across many clients.

Once that basic structure (unique primary keys and not-necessarily unique 
foreign keys) is safely in place, the rest of your issues about similar 
names et al should fade away. Drop down lists and auto-complete are 
essentially formatting tools to failitate data entry, but they have nothing 
to do with the underlying sturcture.

-- 
John Weinshel
Datagrace
Vashon Island, WA
Associate Member, Filemaker Solutions Alliance
Certified for Filemaker 7



<parora2000@gmail.com> wrote in message 
news:1143391924.080030.59500@i39g2000cwa.googlegroups.com...
>I have posted a similar question on this topic before, but maybe
> someone can help me out with this one...
>
> 1.  I understand that I can create a "unique ID" in one table, such as
> an auto-populated serial number, and make a relationship with that
> field to another table.
>
> 2.  In the second table, I understand that I can type in my unique ID,
> and establish a relationship with the first table.
>
> 3.  I also understand that if I don't want to deal with ID numbers,
> then I can create a value list or a drop down list and associate that
> with some other field in the table that contains the Unique ID (such as
> Name).
>
> 4.  Here's my question - suppose I don't want to deal with Unique ID
> numbers... they are non-intuitive to the end-user.  But suppose my
> table is so large, that I also don't want to deal with drop down lists
> or value lists.  Is there a way, using Auto-Complete functionality,
> that I can start putting in an entry, FileMaker would check if it is
> matching with something in my Name field from the other table, make
> auto-complete entries as matches are made, and let me accept an entry
> to establish the relationship with?
>
> 4a.  Now suppose that no matching entry is found - ie - I am typing in
> a new name.  In this case, I would want FileMaker to *create* a new
> entry in my other table, and give it a new Unique ID.  (This seems like
> it can be done by enabling the allow-to-create-entry-in-related-table
> feature).
>
> 4b.  Now suppose that two people in my first table have the same name.
> I would need a way, either through a portal or some other means, to
> select which matching entry I wish to establish the relationship with
> (based on Unique ID or some other field, such as Address).
>
> 4c.  Finally, suppose again that two people in my first table have the
> same name.  But instead of selecting one of the two, I would need a way
> to create a brand new person with the same name.
>
> Any ideas?  The key here is that I want to use auto-complete
> functionality to create a match with a record from another table.
>
> Thanks!
> 

Thank you John.  I agree 100% with you that unique ID numbers -
assigned to every record in every table - are an essential ingredient
to good database design.  My point, however, was to make this invisible
to the end-user - so I guess you could say it was a question about how
best to create an interface where ID numbers wouldn't be a worry for
the end-user to have to type in unless absolutely necessary to
differentiate between two very similar records.

Like you, I too use auto-entered, locked serial numbers, but prefer to
keep them in the background, as little involved with the user as
possible.

In article <1143435969.351666.62520@v46g2000cwv.googlegroups.com>,
 parora2000@gmail.com wrote:

> Thank you John.  I agree 100% with you that unique ID numbers -
> assigned to every record in every table - are an essential ingredient
> to good database design.  My point, however, was to make this invisible
> to the end-user - so I guess you could say it was a question about how
> best to create an interface where ID numbers wouldn't be a worry for
> the end-user to have to type in unless absolutely necessary to
> differentiate between two very similar records.
> 
> Like you, I too use auto-entered, locked serial numbers, but prefer to
> keep them in the background, as little involved with the user as
> possible.

I gave you an answer in your other thread, suggesting that you use a 
drop-down list, with the Person ID number being the first field, and the 
calculated NameLastFirstSuffix as the second field and the only field 
visible to the user. The user does not even see the first field that is 
the reference for the list, and the second field is sorted alphbetically.

You seem determined to avoid this approach as being too cumbersome.

It is not too cumbersome. I have a list of more than 2,000 people. I can 
very easily find a name in the drop-down list. I just type the first few 
letters of the name, and the list immediately jumps to that point, from 
which it is very quick and easy to find the person.

But maybe you should be going from the Person to the task. The same 
approach works, but now with a portal of tasks in the laryout of the 
person. The TaskID field in the would be the first field for the 
drop-down list of tasks, with the task name being the second and only 
visible field. MIght be easier going that way.

You can also have a dynamic list taken from a field by way of a 
relationship, which can reduce the number of entries in the list to just 
those that are pertinent to the selection.

You can also script the whole thing, as Remi Noel suggested in his 
response in the other thread where you asked the same question.

Bill Collins

Bill wrote:
> In article <1143435969.351666.62520@v46g2000cwv.googlegroups.com>,
>  parora2000@gmail.com wrote:
> 
>> Thank you John.  I agree 100% with you that unique ID numbers -
>> assigned to every record in every table - are an essential ingredient
>> to good database design.  My point, however, was to make this invisible
>> to the end-user - so I guess you could say it was a question about how
>> best to create an interface where ID numbers wouldn't be a worry for
>> the end-user to have to type in unless absolutely necessary to
>> differentiate between two very similar records.
>>
>> Like you, I too use auto-entered, locked serial numbers, but prefer to
>> keep them in the background, as little involved with the user as
>> possible.
> 
> I gave you an answer in your other thread, suggesting that you use a 
> drop-down list, with the Person ID number being the first field, and the 
> calculated NameLastFirstSuffix as the second field and the only field 
> visible to the user. The user does not even see the first field that is 
> the reference for the list, and the second field is sorted alphbetically.
> 
> You seem determined to avoid this approach as being too cumbersome.
> 
> It is not too cumbersome. I have a list of more than 2,000 people. I can 
> very easily find a name in the drop-down list. I just type the first few 
> letters of the name, and the list immediately jumps to that point, from 
> which it is very quick and easy to find the person.
> 
> But maybe you should be going from the Person to the task. The same 
> approach works, but now with a portal of tasks in the laryout of the 
> person. The TaskID field in the would be the first field for the 
> drop-down list of tasks, with the task name being the second and only 
> visible field. MIght be easier going that way.
> 
> You can also have a dynamic list taken from a field by way of a 
> relationship, which can reduce the number of entries in the list to just 
> those that are pertinent to the selection.
> 
> You can also script the whole thing, as Remi Noel suggested in his 
> response in the other thread where you asked the same question.
> 
> Bill Collins

My apologies for the previous dud post! Shouldn't try to press so many 
keys at once :(

Bill, you say "the user does not even see the first field ... "

I must be doing something wrong ... I have a value list for a drop down 
field, with OrgID as first value, and OrgName as second value, with only 
OrgName visible - so far so good. This is used by a field in another 
table (not Org), which stores the OrgID. The problem is, though, that 
when I select the OrgName I want from the drop down list, then the OrgID 
shows up (as you'd kinda expect since the field is linked to OrgID). How 
do I get it to only display the OrgName, whilst actually storing the OrgID?

Thanks so much in advance,
julie



Bill wrote:
> In article <1143435969.351666.62520@v46g2000cwv.googlegroups.com>,
>  parora2000@gmail.com wrote:
> 
>> Thank you John.  I agree 100% with you that unique ID numbers -
>> assigned to every record in every table - are an essential ingredient
>> to good database design.  My point, however, was to make this invisible
>> to the end-user - so I guess you could say it was a question about how
>> best to create an interface where ID numbers wouldn't be a worry for
>> the end-user to have to type in unless absolutely necessary to
>> differentiate between two very similar records.
>>
>> Like you, I too use auto-entered, locked serial numbers, but prefer to
>> keep them in the background, as little involved with the user as
>> possible.
> 
> I gave you an answer in your other thread, suggesting that you use a 
> drop-down list, with the Person ID number being the first field, and the 
> calculated NameLastFirstSuffix as the second field and the only field 
> visible to the user. The user does not even see the first field that is 
> the reference for the list, and the second field is sorted alphbetically.
> 
> You seem determined to avoid this approach as being too cumbersome.
> 
> It is not too cumbersome. I have a list of more than 2,000 people. I can 
> very easily find a name in the drop-down list. I just type the first few 
> letters of the name, and the list immediately jumps to that point, from 
> which it is very quick and easy to find the person.
> 
> But maybe you should be going from the Person to the task. The same 
> approach works, but now with a portal of tasks in the laryout of the 
> person. The TaskID field in the would be the first field for the 
> drop-down list of tasks, with the task name being the second and only 
> visible field. MIght be easier going that way.
> 
> You can also have a dynamic list taken from a field by way of a 
> relationship, which can reduce the number of entries in the list to just 
> those that are pertinent to the selection.
> 
> You can also script the whole thing, as Remi Noel suggested in his 
> response in the other thread where you asked the same question.
> 
> Bill Collins