Newbie seeks help and/or advice

[jontyhunter]

Apologies, but I couldn't decide exactly where to post this as I have a couple of questions covering several topics. If I'm in the wrong place, please advise and I'll repost where I should be.

First and foremost, I am a complete newbie so any help will be most appreciated no matter how small or seemingly insignificant.

I have attached my current WorkInProgress file. Most of it is working as I want it to, but there a few things I want it to do but can't seem to get right.

My file is the beginnings of a much larger database to handle a whole host of things at work, but for the time being is just a contact management database. There are two tables - contacts and companies. The user can enter in a contact and, if the company details are in the companies table, it looksup all the relevant information - address, postcode, country etc. This way if there is only one employee for a given company the user does not have to enter the company details first in the companies table, they can just enter them manually in the contacts table, but if the user knows there are going to be several contacts at a given company they can go to the companies table first, enter the details and then rely on lookup to enter the details for each contact in turn. Does this make sense? I hope so. I find writing about Filemaker as difficult as getting to grips with it.

Anyway, for the most part this is working well. The two tables are connected by buttons which switch between them (utilising scripts so that the windows resize to fit) and there is an 'update' button on the companies page that re-looksup all the details if any data gets changed on the companies table and needs to be updated on the contacts table.

And so to my first question. The 'update' button when pressed uses the relookup script function but this goes through every record in the contacts table and updates every lookup. I want it to only update the lookups that relate to the company being viewed. Will this require a more complicated script?

Next question. I have added a button to the companies layout that creates a new record in the contacts table. I want it to enter in the correct company name into this new record which should force a lookup of the details but it is just refusing to work. I thought the set field script function would do the job but it fails to transfer the information across. (I realise I ought to add in that it checks if the company name is blank and doesn't run the script if it is but I'm not worrying about that for now.)

Finally, my portals. Bit of background needed here. Several of the companies we deal with at work are based on more than one site. My colleagues requested that, no matter what site an employee was based at, they would all appear in the related contacts portal on the contacts layout. I have got this to work by making it so that if you add something in square brackets [ ] after a company name this extra bit is ignored for the purposes of the portal so that Mycompany and Mycompany [Australia] are treated the same. On the contacts layout this works perfectly, however on the companies layout I want it so that it only shows the employees at that site not every employee for that company. I know this is all to do with my relationships but I am really struggling to understand them and sort it out (I have lost track of how many times I have reread the section in the Missing Manual book without getting any closer to understanding it).

I would be very grateful for any help offered and any advice on what I may have done wrong or ways of simplifying what I am trying to do would be most appreciated.

Thanks,

Jontyhunter

:slightly bewildered by the depth of Filemaker after years in the wilderness using Excel:

[Ender]

Hi Jontyhunter,

And so to my first question. The 'update' button when pressed uses the relookup script function but this goes through every record in the contacts table and updates every lookup. I want it to only update the lookups that relate to the company being viewed. Will this require a more complicated script?

Not really. Use a Go to Related Records [ Show only related ] script step to jump to the correct related records before doing the Relookup.

I can't really answer the other questions based on the information provided (and the attachment won't open.) But you might take a look at the Business Tracker solution on filemaker.com. It does a good job of handling Company-Contact relationships with multiple sites.

[Fitch]

May I suggest that you don't need to lookup the company info -- you don't need to store the company info in the contacts table at all; simply display the related company fields on the contact layout. All you need to store is a primary key field in the company file (aka "comany id," a serial number that is automatically incremented) and a corresponding "company id" field in the contacts file. The relationship takes care of the rest.

As for company sites, I suggest you use a "site" field to tag each contact. You could then create a relationship in companies based on company id + site to filter your portals. It may be that you'd even want an additional table for company sites -- that way you wouldn't have duplicate company records, but that may be a whole other discussion.