Please Help: Portal to Portal data entry and relationship mapping

[jumpysven]

Hi,

I am in a huge mess. I need to be able to create the ability to enter information in one portal that is transfered into another portal and vice-versa.

Let's say that I am taking notes on different developments in the New York City area. one of the things I need to do is note which banks financed each of the developments, and what type of financing each bank did for each development. so basically, I want 2 layouts. 1 (developments layout) with a portal in which I can enter the different banks that financed the project and what kind of financing they did. This then needs to create that occurence in the Banks layout (portal because each bank finances many different developments at different points in financing stage) showing all the different banks and the types of financing they did. However, it becomes further complicated because I also want to bounce from bank to bank from a button within bank portal to show the profiles of other banks. Make sense?

What happens at this point is that whenever I enter some information in one of the portals, it either tells me that the info is not modifiable or has to be unique and so I make the I.D. modifiable and then instead of adding to the related portals, it creates an entirely new record. I am going crazy trying to fix this... Please HELP.

[jumpysven]

And furthermore, whenever I change my specified field for bank ID to the bank id in the join table, It populates my entire portal with one number...

[David Jondreau]

You situation isn't entirely clear, but it sounds like you have two tables, Banks and Developments. You need a third, Financing. Financing will hold a combination of the Bank ID and the Development ID and the Financing Type.

It can be related to each of the other two tables.

[jumpysven]

Well, the real problem is that I have two tables: developments and banks. each needs to have a portal that shows all the information in the other table's portal and has buttons that will bring me back and forth between those records.

[David Jondreau]

The real problem is you don't understand how FileMaker's elements really work.

I'll say it again...

A Bank can finance more than one Development and a Development can be financed by more than one Bank. You need an intermediary table, Financing.

That table needs fields to capture the unique ID of the Bank and of the Development. Relate that table to each of the other two by that ID. Then you'll have the framework upon which you can get the interface you want...which is the step after making that third table.

[jumpysven]

Ok. Thank you. I have the third table, the ID's for banks, Finance, and developments are unique and auto enter serial numbers when they are in their own table while they are just normal numbers when they are in the finance table. I guess my next question is, do I have to populate the finance table with records or does it do that on its own? One of my confusions is that the database I am working with already has a thousand records in developments table and when I go to the finance layout in browse mode, it doesn't have a thousand records, it just shows 60 records: 1 for each bank, and the finance ID is always equal to the bank ID. what could be my possible mistake? Thanks again, I am sure I sound like an imbecile. I am a finance major and have been asked to create the filemaker database for the marketing company I work for...

[David Jondreau]

OK. So now you've got three tables.

The new table, Financing, won't populate with the initial data on its own, you'll have to import that, but future data will be easily entered. How to import the data? Well, I'm confused on how it has been stored since you haven't had a place to store it. Maybe a return-delimited list of values? Hard to say without looking at the file itself.

You relate the Development table to the Financing table via the DevelopmentID and relate the Banks table to the Financing table via the Bank ID. Make sure "All creation of related records" is turned on.

Every record in Financing is a combination of a Development ID and a Bank ID. (I suppose you can have multiple of that if a bank finances a development multiple times.) Then your portal on the Development layout should show the related records from the Financing table and the portal on the Bank layout will show records from the Financing table too.

You will then enter the Bank ID into the portal on the Development layouts and that will create the join record in Financing and that record will also show up in the portal relevant Bank record.

[jumpysven]

Well, the data is already stored because there existed a database already that had some information on different developments. What I did when I showed up was add a bunch more fields to the developments table and added a bunch of other tables like general contractors, banks, Architects, Attorneys, Designers, etc. and linked them by development I.D. Banks was special because it was a many to many. (I first had to create a development I.D. to each development using a function whose name I forget) I am Wondering if I have to do that with Finance table...

I will let you know what happens when I follow your instructions. Thanks again.

[jumpysven]

When I choose to specify my fields when I am in layout mode, should bank id in developments field specify bank id in finance table or the bank id in the bank table? Basically, that question is the same for all of the fields in all tables?

[David Jondreau]

Not sure what you mean by specify fields in layout mode. But you don't want a Bank ID in the Development table or a Development ID in the Bank table or a Finance ID in either Developments or Banks. When creating the portal in Developments, you want the portal to show records from Financing and the BankID should be from the Financing table.

Developments

DevelopmentID (number, auto-enter serial)

Banks

BankID (number, auto-enter serial)

Financing

DevelopmentID (number)

BankID (number)

[jumpysven]

Awesome. but now, whenever I enter a number in the developments portal for the bank ID, it changes all of the bank ID's in the portal to the same number...

[comment]

Perhaps you should take a look at the demo I have posted here:

http://www.fmforums.com/forum/showpost.php?post/246136/

It's basically the same thing, only the names are different:

• Contacts == Developments;

• Organizations == Banks;

• Affiliations == Financing

[Fenton]

Also, whenever you see all the same value in a portal, and it doesn't make sense, check that the field actually is assigned properly, that it's pointing at the right field. An ID field for entry would generally be IN the table of the portal, ie., in the join table if that's what the portal's relationship is pointing to.

[jumpysven]

haha. Great. now it won't allow me to have more than one record in the portal at one time. I am positive that I have the I.D. fields pointing at the right place. I have two options, either have a bunch of records saying same thing, or just have one record in portal. Any way to force this S.O.B. to work?

[David Jondreau]

Post an example file.

[jumpysven]

is there any way that I can post the database on this system so somebody can tell me what the heck is wrong? I would really appreciate it.

[jumpysven]

Furthermore, when I try to delete the occurence in the join table, it deletes the Record from the Bank Table, how do I avoid this?

[Fenton]

Yes, you can post the file. You do that that by clicking the Preview button at the bottom, below your post. Then click the Manage files link, choose the file, click Add, then Finish.

If it is very big, you can use Save a Copy As Clone, which will save a copy with no records, no data. We do not really need to see date to see what's wrong. Using a join table is not a real complex thing, but it's kind of hard to grasp the concept the first time.

The reason it's deleting the main record is either: 1. The portal is not pointing to the join table, but to the main table, or, 2. You have [x] Delete related records checked for the main table. It is supposed to only be checked for the join table; so that it's only deleting the "link" record.

[jumpysven]

Well in case anybody cares anymore, I finally figured it out. it was really simple, but not only do you have to specify which tables and fields the fields in the portal are taking information from, but you also have to specify that the portal is specifying the right table. I just assumed that by specifying the fields, the portal knew where to draw that info from.