Portal Assistance... Aaaaarggg!

[primitive_man]

I'm having real problems getting a portal system to work. Even when starting afresh from a base perspective I can't seem to get the relationships right... which seems to state that I have missed a fundamental step somewhere... but tracking it down is driving me crazy.

My Aim:

Small database saying which primary contractor worked on a particular job.

2 Tables. - Customers & Contacts

2 Layouts:

Customers with Buttons to delete/add Customer and a button to view related contacts on the contact Layout.

Contacts with a portal listing the contact type (Bricklayer, Surveyor, Painter etc) + Button to delete selected portal row contact.

Detail section. Name, Address, telephone number etc

When I click on a portal row I want the detail section to update - showing me the relevant details for that contact.

A button to Add a new contact on the same layout - using the detail section above, i.e. the detail section should start out blank.

Can anyone assist?

[imoree]

What is the relationship like> What you are saying actually makes no sense.

You want a

2 Tables. - Customers & Contacts

2 Layouts:

Customers with Buttons to delete/add Customer and a button to view related contacts on the contact Layout.

Contacts with a portal listing the contact type (Bricklayer, Surveyor, Painter etc) + Button to delete selected portal row contact.

1. Customer has Contacts?
   
2. Where is contacts database
   
3. customers and contacts share similar information ( Table Occcurence perhaps)
   
4. Customer Table can have
   

• custID
  
• fName
  
• lName
  
• contactID
  
• jobID
  

WHen you have relationships sorted or planned, then you can go to related contact by using A relationship.

you need to plan it out and ask questions so you can figure out your key value(s) and foreign keys.

hope that helps a little

[primitive_man]

Customers Table:

Field 1: _pk_customers_id ( Auto-Enter Serial, Can't Modify Auto, Unique ) (number)

Field 2: Name (Text)

Contacts Table:

Field 1: _pk_contacts_id ( Auto-Enter Serial, Can't Modify Auto, Unique )

Field 2: _fk_customers_id (number)

Field 3: Contractor (Text)

Field 4: Telephone_Number (text)

Relationships:

(Customers) _Pk_customers_id = (Contacts) _fk_customers_id

Allow Creation/Deletion of records on Contacts side of the relationship.

Customer can have many contacts. e.g. One customer can have many contractors who worked on their particular building project.

I have one additonal table. globes - with one field g_customers_id (global)

When Moving from the Customers Layout (based on the customers table) I invoke this script:

Set Field [globes::g_customers_id;customers::pk_customers_id]

Go to Layout ["contacts" (contacts)]

[efen]

You need a third - join - table to accomplish this.

See the third post of

http://fmforums.com/forum/topic/79711-child-record-with-multiple-parents/page__fromsearch__1

where there is a link to another thread which has a demo file by comment

[comment]

Customer can have many contacts. e.g. One customer can have many contractors who worked on their particular building project.

Can a contractor work on more than one project?

[primitive_man]

Sorry about the delayed reply... was watching Lee Evans at the Brighton Centre. Fantastic show. Highly Recommended!.

As to your question, Yes. A contractor can work on several projects. But that I can sort out quite easily, when I figure out how to get the portal & detail records on the same layout.

[comment]

If a contractor can work on several projects - i.e. for several customers - then you cannot have a _fk_customers_id field in the Contacts table. This is a many-to-many relationship, and you do need a join table to resolve it. This is a structural change - something you want to have in place before you start working on the user interface.

[primitive_man]

I simplified the customer field data because in actuality, each customer is represented by a specific job code that has a referential lookup on another database (mySQL).

The 'name' field in the customers table will only ever refer to that job code - i.e., kitchen extension - code 4182, bathroom upgrade - code 6619 etc... so this will be a one-to-many database relationship. I do not have to bother about integrating the other database into this one, I have in fact, been requested not to do so. Go figure....

[primitive_man]

MY CONCEPTIONS ARE FUNDAMETALLY FLAWED!!

I'm a certified Oracle database professional (800+ databases), a certified Microsoft Database professional (1000+ databases). A qualified - if only through shear experience - MySQL database designer (300+ databases) yet I am baffled - yes, it is a lovely word - baffled - by Filemakers logic.

Maybe - as the saying goes - my glass is too full - and I'll have to empty myself of previous input in order to grasp the idea, the logic, the basics of Filemaker. A part of me - says "Stuff it" (or words to that effect) - "I can do it in other (DB) languages" - but another part of me, a larger... bigger.. more aggressive barsteward... the same sod that led me through 27 years of army life... says, "Keep on lookin'... you'll kick it's ars*. At 61 though, I'm thinking... maybe I'm too old, too set in my ways, too confined by what I already know...."

[comment]

There is nothing special about Filemaker logic in this aspect. It's just good old plain relational DB design - equally applicable to Filemaker, MySQL, Oracle...

[imoree]

i think programming sql is different from "UNDERSTANDING RELATIONAL DATA". i am alos from the background of create datebase "sql", create table username , varchar(20),etc,etc

programming it but not understanding the relational model or structure of data.

When we need data we just ,

SELECT FROM USER "me" WHERE "companyname = white" INNER JOIN blah blah .

filemaker pro has all these visuals so if you dont understand the flow of data visually, why bother. HOwever Sql , mysql, oracle aint going no where anytime soon.

I do feel your pain though.. I get frustrated myself as Comment will attest to.

time will definately make teh difference, IF You have it!!