If Not In The List

[Fabio Melo]

I'm a new FM user.

I have two tables: TblContacts ( ContactID, Contact, DOB, City) and TblRegister (RegisterID,Contact, Date,Obs), my problem is when I add a contact to the TblRegister using a drop-down-list to the Contact and its not registered yet how to jump to the Contacts layout and add all the informatiom about the specific Contact that hasn't been registered? ,like the event in the MSAccess ˜'fNotInList'.

[Fabio Melo]

This means

When i populate field Contact in the Table Register, and register a new contact that is not in tha Table Contact, how do I populate all the Contact data "Name, DOB, Adress,City....."

It means after a type the name of the contact it should check if it exists and if not go to the layout Contact and create it.

Thanks.

[bcooney]

I would start in Contacts to create a new Register record (whatever that is).

Have the user Find in Contacts, and go to a list view for foundcount>1 and form view for foundcount=1.

On the form view, have a button New Register record.

So, if they do not find a matching Contact, they're in the right table to create one, and then Register if they want.

[Fabio Melo]

It would'n be necessary to script it?

I'm moving from MSAccess to FileMaker, and in MSAccess there is an event (IfNotInList) where it can check if the related table has the "Contact" registered and if not do a new register.

[bcooney]

What I understood from your first post is this workflow:

1. User creates Register record.

2. User "populates" foreign Contact ID key.

You need to provide a way for the user to create a Contact "on the fly" while they're in the middle of creating a Register record.

I suggested that you start them in Contacts. Find a contact or create New and then from a Contact form view, create a new Register record. The creation of the Register record is scripted.

But, to answer your question, if you want to start in Register, you'd need to provide a way for the user to create a new Contact. I suggest a popup window of Contacts in list view that provides a way to find a contact and create new. Once they are on the contact that they wish to register, provide a Select button which would set the ContactID as a foreign key in Register.

Also, in FM, using "If not IsEmpty (relationship)" is the equivalent of Access' (IfNotInList).

I'll do up a demo when I have more time.

[Fabio Melo]

That's it. The databank em Acces works like this. In the Register, on the fly, I type the Contact Name and it returns the Contact's data or open a pop up layout to create a new one.

[bcooney]

Hmm. So in Access, you'd match on Contact Name (NOT ID) and if it doesn't find one it creates a new Contact? What if you have two Contacts with the same name? How is Register related to Contacts (by some combo of Name, ugh)? What if Mary Jones gets married and you change her last name--are all records that were related to her now orphaned?

It's not that you couldn't do this in FM, but you really shouldn't. Tables should be related by IDs, not text values.

So, you're back to how do you populate a foreign key.

Edited April 27, 2009 by Guest

[Fabio Melo]

Yes it is related by ContactID, but it "lookup" by Name and if there is no Name that match the criteria it open a popup layout to add a New Contact, and after the New Contact is added it returns to Register layout to complete the data. When there is any change to Contact it is edited. When there are Contacts with the same name it is sorted by DOB or IdentityNumber.

What I intend to do is:

1 type the Contact Name in the related table Register;

2: after lookup at the Contact table if there is a name that match with,

3: If OK continue...

4: If Not open a Contact popup layout and add a new Contact, using the Name typed in the Register,

5: Return to Contact layout and continue adding the data.

Edited April 27, 2009 by Guest

[bcooney]

I understand what you want to do, but Step#2 will leave you with trouble. How are you going to match on name? Certainly last name isn't guaranteed unique. So, you might lookup the wrong ContactID.

Why isn't starting at Contacts a feasible alternative? Have you explored a popup list select window for Contacts from Register?

Here's a demo of a Popup Select with a New Contact option. Still, I'd start in Contacts and make a new Register record.

PopupSelectNew.fp7.zip

Edited April 28, 2009 by Guest
Added demo file

[Søren Dyhr]

What I intend to do is:

1 type the Contact Name in the related table Register;

2: after lookup at the Contact table if there is a name that match with,

3: If OK continue...

4: If Not open a Contact popup layout and add a new Contact, using the Name typed in the Register,

5: Return to Contact layout and continue adding the data.

A way to accomplish this is the way I solved the matter in this thread:

http://fmforums.com/forum/showtopic.php?tid/202995/post/324003/fromactivity/myposts/#324003

...but I'm fully with Barbara's integrity cautioning!

--sd

[Fabio Melo]

OK Barbara but...

According to your sample, it wouldn't be a little complicated after 100 ... 1.000 Contacts, where I would have to look for each record? Wouldn't be better to Find just all names that match the criteria? And select a specific one, is it possible?

If I index Contact_Name it would be possible to create a "Drop-down List" and Find the Contact by the Index field ou list all the Contacts that match the criteria?

[bcooney]

Yes, my demo needs a find capability. I'll add one and repost.

EDIT: This demo requires FM10 bcs there's a script trigger on the Select Contacts by Last Name.

PopupSelectNew2.fp7.zip

Edited April 28, 2009 by Guest

[Fabio Melo]

Hi Barbara

Thank you very much for your help