Updating a contact Database

[amidyne]

I have 3 tables:

Company

Addresses

Contact

The tables are connected by

Company:_CompanyID_ ->Addresses::_CompanyID_

Addresses::_AddressesID_->Contact::_ContactID_

Right now there are about 6000 Contacts, 4500 Compnies and about 5500 Address associated with the Companies

What I'm trying to setup is to have the user (when in the Contact Table) to be able to select a company that the desired contact works for (from the Company table).

I then would like to have the user be able to select from one of several locations that the company operates out of (from the Addresses table) without seeing the addresses associated with the other companies

At the moment I'm having trouble trying to set this relationship up so that it works correctly. I would very much like to avoid using a portal to display the necessary information

Are there any ideas?

[Fitch]

If you avoid portals, I assume you want to use a drop-down value list? This will be unwieldy with 4500 companies.

[amidyne]

That was a design concern I had, but I was thinking of auto-completing the company field on the contacts layout.

But if I were to use a portal, would I be able to keep the same look of my layout? I have included a screen capture to compare.

...Sorry if my posts aren't making too much sense, I've been staring at fileMaker too much today

[Fitch]

The problem with auto-complete is that it's based on previously entered values in the same table, but you're wanting company names from the company table. Also, it's really the companyID you want to store in the contact record, not the company name.

Once you have the companyID it's easy to make a value list that shows only related addresses for that company.

But to select the company, you may have to rethink it. Maybe script it to go to a company list layout, in a new window or not, and select from there.

[amidyne]

Fitch great insight onto what i needed to do, it works great. I just have one question, about committing my addresses. I have the value list to display the relevant address information for the desired company, but currently when I select a companies address it replaces the address for all records present in the database. I'll post my database setup and explain what's going on.

Company::pkCompany ID-> Addresses::fkCompany ID

Addresses::pkAddresses ID-> Contact::fk Addresses ID

Then I have my table occurrences to set up my value list they are in the form 'Assign Company_ORIGINAL TABLE'

Assign Company_ADDRESSES

Assign Company_COMPANY

Assign Company_Company_ADDRESSES

These are connected by

Contact::Global Company ID |- Assign Company_COMPANY::pkCompany ID

Contact::fkAddress ID <- Assign Company_ADDRESSES::pkAddress ID

and finally

Assign Company_COMPANY::pkCompany ID -> Assign Company_Company_ADDRESSES::fkCompany ID

My value list is as such:

Use Values from Field

Assign Company_Company_ADDRESSES::Addresses ID

Use Values from second field

Assign Company_Company_ADDRESSES::Address

Include only related values starting from

Contact

On the Contact Layout I am using the data related through Assign Company_Company_ADDRESSES to display my address information which displays the data that I want, but won't commit it like I want (problem as explained above).

Should I be adding a setFields script step here to set the address info? Or do you think I'm completely off base with my assesment

Any help/comments would be greatly appreciated

[Fitch]

That's a bit much to visualize, could you post the file with a few records of sample data? I can't promise to look at it any time soon, sorry, I'm going to be busy for a couple of weeks, but maybe someone else will have time.

[amidyne]

What my previous post boils down to is this:

I'm looking to run a script after I select my address from the pop up list, that will set the address fields in the contact layout

Put in a different way:

My pop up list has addresses for the selected company.

When I click on the address I want to run a script to populate the empty address fields on my contact layout.

Is this possible?

[Fitch]

You can trigger a script when a field is exited using a plugin such as zippScript.

[comment]

When I click on the address I want to run a script to populate the empty address fields on my contact layout.

I don't see why you would want to do that. After you select which company the contact belongs to, you can select which address (of the selected company's addresses) he/she belongs to. Once you have done that, the address details are available directly from the related address record - no need to duplicate them into the contact's record.

[amidyne]

Thanks for bringing that up Comment. I had initially thought that was what would happen, but I had no displayed records after I selected the appropriate address. So I thought there may have been a design issue with the way I set up the database. That was the reason I am trying the plug-in route, however I agree that being able to display the already available records would be more desirable, considering I would have to install the plugin on all user computers

[comment]

You need a relationship between Contacts and a second occurrence of Adresses, based on AddressID. Then place fields from this occurrence on a layout of Contacts.

[amidyne]

That's actually what I have.

Assign Company_Company_ADDRESSES is the occurrence that pop-up menu gets the addresses from and Assign Company_ADDRESSES is the occurrence that I set up to pull the full address info from. But it won't display, so that's when I thought a script step may work better

[comment]

It should work quite easily with relationships, and I would fix that before looking for more complex alternatives (with the further disadvantage of redundant duplication of data). Unfortunately, I cannot tell what you've done just by the TO names. I think all you need is 3 relationships:

Companies -< Addresses

Companies::CompanyID = Addresses::CompanyID

Companies -< Contacts

Companies::CompanyID = Contacts::CompanyID

Contacts >- Addresses 2

Contacts::AddressID = Addresses 2::AddressID

You select a contact's AddressID from a value list showing only related records from Addresses, starting from Companies. And you put fields from Addresses 2 on the Contacts layout.

[amidyne]

I'll work on getting a sample database up tomorrow

[amidyne]

Comment I just re reviewed your last post and what you have is different than my relationship setup currently. I'll try your method and then get back with the results

[amidyne]

I've included two versions of the database. Version 1 has the relationships setup the way Comment suggested.

Version 2 has the relationships setup the way I tried to communicate.

In both databases the Add Company script has been slightly changed. (I'm starting to think that this is where my problem lies)

Also for some reason with my database with the existing contact information already in, you can select the desired addresses.

I haven't given the user the ability to create all the information from the Contact layout yet, so it may be tedious if you want to add companies and the addresses

Test_Database_V1.fp7.zip

Test_Database_V2.fp7.zip

[comment]

If (as it seems) you want a contact to belong to an address first, and only through the address to a company, that's fine too. In such case I would make the relationships as shown.

Watch your value lists, I don't think they are correct in either version (haven't looked in any depth, though).

[amidyne]

A note about my value list. It is referenced from the missing manual for FMP9. As far as I can tell I set it up in accordance to what they mentioned in the book, but I could be wrong

[comment]

I've taken another (brief) look at your V1 file. Your relationship between Companies and Adresses is based (correctly) on CompanyID - but no address record has a value in the CompanyID field.

[amidyne]

Thanks for pointing that out comment the lack of company id field was definitely limiting the display of data. However now I can't display a different address from the company (I can see address information from company ABC at 123 Fake Street, but not when Gofora Drive is selected)

I have also changed the referring table for city/state/Zip and country to Addresses from Addresses 2

I will continue to work on this and get back if I can find a solution

[amidyne]

I've been a bit busy this past week and have not been able to work on the database. I'll post what I've got shortly (read when I have time) but if anyone has any ideas in the meantime that would be great

[amidyne]

Ok I'm still having issues with this Contact database.

I'm pretty sure the problem is either my Assign Company Script or my Addresses Value List.

I've checked my table Occurrence Address2 and everything that needs to be there is there. But whenever I associate a company with a contact, no addresses show up. There is an association on the Addresses Layout.

I've included my most recent attempt at getting this thing rolling

I should also note, that the way the database is supposed to work is to associate a company to a contact, and then be able to select an address from the addresses that belong to that company

So if anyone is still reading this thread any help/comments would be greatly appreciated

Test_Database_V1.fp7.zip

[comment]

Your "Addresses" value list needs to show values from the Addresses TO, not Addresses 2. I think once you fix that, it should work as expected (kinda hard to see the forest through all the unnecessary fields you have there).

[amidyne]

Comment, you've saved me again. It works exactly like it should. However, I apologize for all these database questions I've been making over the past two months. I'm slowly trying to get my boss to send me on proper training to avoid my messes

[comment]

And what will become of this forum then? :