Popup lists in layouts

[Budweiser]

Hi,

I'm a new Filemaker Pro user, having moved over from Microsoft Access on a PC.

I have a database with a number of tables, two of which are 'Companies' and 'Individuals'. The Individuals table has a 'Company' field that is related to the ref field in the Companies table.

When adding/editting Individuals, I want to be able to select the name of the company from a dropdown list and have the Company field automatically populated with the ref from the selected record.

This is the bread and butter of relational databases, but I can't find out how to do it in Filemaker. I can use a value list with a second field, but it is the numeric reference that is displayed once selected, not the company name, which is useless.

Can anyone help?

Thanks.

[J__]

OK, so what you are saying is that you want to pick a company from a drop down list and then when that is done, have some other fields copied over into the Individuals table, like maybe the companyID and companyPhone number?

1. Create a relation to the Companies table. Create a companyID field and make a join using the relationship tab in define fields dialog to go from Company_PK -> Individuals::Company_FK

2. Create a value list , but instead of entering values, specify by field, and select the Companies table to get the field from and pick Company Name as the field to display in the drop down.

3. Put a field on the layout, right click on it (or ctrl+click if on mac) pick from the popup menu "Field Format", in that dialog pick "popup menu", select the value list to be the one which you defined in #2 above.

4. for any other fields you want to pull over - say CompanyID for example, go into Define Fields dialog (again), click on the CompanyID_FK or whatever you named it. click options in the auto-enter tab, click "looked-up value" near the bottom.

5. In that dialog for Looked up related table, pick Companies

and select the field you want to copy, in this case probably Company_PK

Click OK... OK.

make sure - so you can see and test the results to put a field on the same layout, which displays the CompanyID (from individuals)

When you run the app, click on the Company field and a list will drop down, pick the company name

the companyID will be populated with the ID which is in the Companies table. You can create a lookup for each field you want to copy across.

hope i understood what you wanted right and i hope that helps,

sincerely,

J__

[Budweiser]

Thanks - thats sorted it.

[Budweiser]

No, actually that hasn't worked.

If I follow your instructions and then select a company from the popup list, the companyID field is not populated.

In my relationships, I've linked Company_PK with Individuals::Company_FK, but there's no direct link between the company names. So selecting a name from the popup list doesn't seem to link the tables??

Also, isn't it a little odd to have to store the company name twice (once in each table). In my experience, the primary/foreign keys should be used to link the tables so that you can access fields from each when you need them. Saves duplication of data.

[Joel C R]

You don't have to repeat the same values, unless you're using a lookup, which copies data from one table to another. If the tables are related, you can simply put the FOREIGN table's fields in the PRIMARY table's layout (using the INSERT FIELD function).

When you INSERT a field, you'll get a menu that will allow you to select from the current table and all related ones. Pick the table you desire, and then select the field that you want to be represented on your report.

Hope that helps.

Joel

[Rosales]

I followed the steps you outlined. I couldn't get it to work. Help!

In step #3, you were not specific about what field should be "put ... on the layout". I guessed this should be Companies::Company.

I am attaching a non-functioning example. I hope someone can look at this and post a working solution. In my example of this recipe, you can't actually get the pop-up menu to pop. Unless you enter a Companies_FK. The trouble is that the first time you select another value from the pop-up menu it trashes the value in Companies::Company of the record that was previously related.

Thanks,

D Rosales

Individuals.zip

[SlimJim]

I am posting back your file with a few alterations. The main problem was the comanies value list where the fields were the wrong way round you were trying to drop the company name into the ID field.

Individuals.zip

[Rosales]

SlimJim, that's not quite what I had in mind. I am currently using the method you provided in your modified example, as a work-around. What J__ described above doesn't make any mention of providing a pop-up menu with the PK and the Company name. Of course, it could be that he meant the Company name to be the PK. I was hoping J__ could elaborate on his solution.

My goal is to have a pop-up menu of Companies on the Individual form and to populate the form with values from the selected related record without exposing the Primary Key or Foreign Key to the user. FileMaker 7 does this nicely in the case of One-to-Many relationships via the use of portals and the "Allow creation of records in this table via this relationship" option. From this direction the user never needs to know that a PK or FK exist. But from the Many-to-One direction it doesn't seem that FileMaker 7 provides a simple and clean solution. By "clean", I mean the user doesn't see what they shouldn't need to understand.

SlimJim thanks for taking the time to respond. I do appreciate it.

So now that I've explained myself a little better, does anyone out there have a way of doing this? Script or something?

Please and Thanks,

D Rosales

[MoonShadow]

I haven't had time to follow this entire thread. But this might give you another idea. I use it frequently. Agreed ... no need for the User to see the IDs.

I believe it might be the same (or similar) to what J_ is suggesting but I'm unsure. The difference is point #1 where he specifies joining ID to ID. You will need to join Company to Company on the NAME.

[MoonShadow]

Like the attached. Be SURE to keep the Company name unique. I didn't add the validations ... that is critical here. Click the yellow Company popup and watch the Company ID fill in.

Individuals.zip

[SlimJim]

I totally agree with MoonShadow about validations - from bad experience.

I set up a database using id's and names and exposed the IDs so no real problems. However, I allowed the operator the option of entering a new company via the related file - Big Mistake (without the validation). If the operator did not recognise a company name then in went a new company. Fortunately not many before I was able to set up the validation. I couldn't remove the duplicates because invoices, receipts and letters had all been issued on the new company numbers.

[Rosales]

Thanks, that's what I was looking for. The general behavior is for the most part what I would want/expect.

But, I have found a small problem. If you modify the name of a company, in Companies::Company, the related Individual record doesn't show the updated name in the Individual::CompanyName field. Have you found some way of keeping this data consistent?

Thanks again,

D Rosales

[MoonShadow]

It should change for you. It changes in the demo and I have never had it NOT change appropriately. Is your CompanyID changing? Is it's lookup based upon the NEW CompanyName relationship? Also, be sure the Companies VL is based upon your PRIMARY relationship (not the one for the CompanyName popup).

[Rosales]

MoonShadow,

You misunderstood me. All that stuff works fine. The Company_FK and related fields change every time the user selects a different Company from the pop-up menu. And the Companies Value List updates correctly if you change the name of a company.

To see what I'm talking about follow this recipe.

1) From the Individuals layout make John Doe's company "ABC" (works fine).

2) Switch to the Companies layout and change the name of "ABC" to "ABC test".

3) Return to the Individuals layout and notice that the two company names don't match. The Companies::Company field updated correctly because it is pointing the related record via the Companies_FK. The field CompanyName field still says "ABC" and doesn't match anything in it's drop down list because the value list also updated.

That's what I meant by inconsistent. Any ideas how to keep this synchronized?

D Rosales

[MoonShadow]

If the field contains 'ABC', the data won't change to 'ABC test' until you select it and change it. Same thing would happen in a standard relationship using a pop-up.

Pop-ups are just a method to select; they don't affect data through a table or change data in other tables. You only notice it because you have both pop-ups on your layout at once. You would use your real relationship CompanyName field for all displays.

[MoonShadow]

It would be rare that it is noticed, but you can correct it. Create an unstored calculation (text) with:

If(not Exact(CompanyName; Companies::Company); Companies::Company; CompanyName)

... and place it on top of that pop-up (Arrange > Bring To Front). Make this calc's fill solid and set Field Behavior to not allow entry in Browse or Find. An unstored calc will update instantly across tables.

Remember that Individuals::CompanyName field isn't for anything except this pop-up functionality.

[comment]

Here's another way to look at it.

SelectCompany.fp7.zip

[MoonShadow]

Old habits sometimes die hard. As I was adding the unstored calc, it dawned on me that Auto-Enter (Replace) now in 7 might be used here to replace my old process. But I was short on time and dismissed it out of laziness. Thanks for the lesson (always review every process anew) and for the great technique.