Layout issue - Failure to comprehend

[shinjiikari0601]

Hello all,

First, a bit of an intro since this is my first time posting... Typical computer geek. I'm a programmer and network admin by trade. Typical languages are Visual C#, C, PHP, and Perl. I'm very familiar with SQL and in particular MySQL. Most of my database projects are MySQL and PHP based. This FileMaker stuff is very new to me.

I was given a project recently. It's a fairly simple database. At least at this point. I wanted to do it in FileMaker this time since I wanted to make it a Windows based application instead of a web interface like I usually do.

Right now I have three tables:

company_info

site_info

contact_info

Each table has an auto-generated id.

The site_info and contact_info table each have their own unique id, as well as a field for the id number of a particular company. This is because each company can have multiple sites and contacts, and I was using the company_info id field as the key to join all the tables.

Like I said, pretty basic at this point.. My problem is that I don't fully comprehend FileMaker's presentation layer... I understand that you make a layout, and you can design them however you want and place the fields wherever you want in the body section...

One of my layouts was going to be used to enter site and contact data... What I wanted to do was allow the user to enter the name, address, phone, etc, and I wanted a drop down menu filled with the company names retreieved from the company_info table... This would allow the user to select the company to associate the contact with...

I cant seem to get a drop down box to be populated with the entries from the company_info table... It just sits there, blank, when I go back to browse mode... I've got some sample data in the company_info table, so I would think it wouldnt be blank...

In SQL I would just do something like SELECT company_name FROM company_info and loop through that data to populate the drop down...

But, this isn't MySQL and PHP... It's FileMaker and I'm not grasping how to do the most basic stuff in it...

I'm not even sure if I'm describing what I want to do clearly enough for anyone to help me... I want to make basically an 'add contact' layout that allows me to enter data into the contact_info table, but has a drop down box populated with the company names selected from a different table, company_info. The drop down would allow the user to select the company that the contact belongs to, and will be used to update the owner_id column in the contact_info table.

Arrrrghhhhh. I'm sorry for the long drawn out and confusing post. I'm just stuck and I'm not comprehending FileMaker very well. I'm used to PHP and MySQL where I can grab data from any table and do anything I want with it at any time...

If anyone could please give me some advice (other than go back to MySQL and PHP), I would really appreciate it...

Thanks,

Brad

P.S. - I've watched some training videos on sites like lynda.com, etc, so I kinda understand the very basics of FileMaker.

[Kris M]

Here is a crude implementation of a drop down control/value list.

It only shows how to populate a drop down from the values in a table.

Relationships are required!

Their are other way to do this, and other considerations when deciding on a method, but this is the simplest.

Hope it helps

demo.zip

[shinjiikari0601]

Thats explains it all! Thank you for the sample!

I was setting up the field as a drop down and such, and on the main setup page was selecting what table field I wanted the data to come from.

What I wasn't doing was clicking on the Define Values drop down, and working in the Manage Values List area...

For some reason, I had it in my head that the Manage Values List thing would allow me to define STATIC data to place into the pulldown (which it apparently does that as well), not dynamic data from another table (which it does)...

So, that got me up and running on that portion.

I thank you very much for taking the time to send me over that demo file. It really helped me understand what I was doing wrong.

Thanks,

Brad

[shinjiikari0601]

Ok, so, another question related to this problem...

I have a layout now with the drop down as described above. It also has the fields from the contact_info table, such as name, address, city, state, zip, phone, etc..... The drop down menu is from the company_info table, company_name field.

I hit ctrl+n for a new record... I select the company that 'owns' the contact... Then I proceed to fill out the form with sample data... I hit enter to submit it...

In my contact_info table, I get all the data, including a number (the ID from the company_info table) !! Yea!! Exactly what I wanted. Then I check the company_info table, and it's added another entry into that table, with ONLY the company_name field filled in, and of course the auto-assigned ID which matches the # entered into the contact_info table under company_id...

When I setup the relationships, I joined based upon the SQL statement (for lack of the proper FileMaker notation for explaining it) of company_info.id = contact_info.company_id ...

I checked the box that said allow creation of records in this table based on this relationship on the company_info table... This allows me to see the items in the dropdown, but allows the creation of these improper entries.

When I uncheck that box, and check the same said box but under the contact_info table (which is where I would think it should be anyway, but i must be wrong), the drop down box is empty on my layout.

I'm so lost. Maybe I should just go back to MySQL and PHP... I'm just not wrapping my head around FileMaker very quickly.

Frustratedly and ineptedly yours,

Brad

[Fenton]

You might want to look at the recent (today) thread "Mysterious Contact Creation", which is very similar to what you're asking about. Basically, if you're a beginner, you shouldn't try to create Companies from Contacts, in my opinion.

There is an advanced technique, invented by David Graham, which uses 3 tables, a central table which joins the two, in a flexible way. It does have [x] Allow creation on both sides of its relationships to the others.

[shinjiikari0601]

Thank you for the pointer to the other link. I will go view it now.

[shinjiikari0601]

I found the post you were talking about, and it did help me some. Thank you.

For some reason, I cannot get my drop down to display any information in the Add Contact layout unless in the table relationships, I checkmark "Allow creation of records in this table by this relationship" under the COMPANY_INFO side. I don't understand why that would make a difference of whether the drop down displays the data or not. But if that's checked, it displays. If its clear, it doesn't.

Anyway, I've taken enough of everyone's time for one day/project. I'll try to figure it out on my own by playing around for a while longer.

Thanks again everyone for the assistance.

Brad

[Fenton]

Or upload a small example, with directions as where we should look and how you think it should work. Because there are several common beginners' mistakes with this setup. Sometime people put related fields from the local table in the portal instead of the foreign ID local to the portal's table, which makes for not-so-good results, etc..

[shinjiikari0601]

Well, I'm taking your advice and uploading a sample with this post... It's just a little example I make to demonstrate the issue I'm having with the project. Totally different solution, but exactly the same problem.

I'm sure what I'm doing wrong is so simple that I'm going to kick myself for a week once it's figured out.

Anyway, if anyone has a moment and wouldn't mind helping out a neophyte, I'd appreciate any advice.

Thanks,

Brad

sandbox1.zip

[comment]

The drop-down field ("Pick a person") should be the person_id field from the current table ("places"). That's how you establish a relationship: you enter the selected parent's primary key value into the child's foreign key field.

[Fenton]

And if you want it to show only the name after you're done (as is it set up in the Value List options), you need to use a popup menu instead of a drop-down list, or cover the id field with the related People::Name field (see the example file from the other thread).

[shinjiikari0601]

Thank you both for checking out my example and giving me some advice.

That seems to have gotten me under way.

I was told that the FileMaker community was pretty helpful and friendly. You both have proven that it is.

Many thanks,

Brad

[Kris M]

Just remeber that almost everything in Filemaker is context sensitive. The table occurence to which your layout is pointed to is the way into your data structures.

Good luck and welcome.