Relationship or Value List...FM101 question

[jlarmen]

I'm creating a simple job application tracker, one for practice, secondly for practical application for some students.  I created two tables, Company and Position.  Primary key is CO ID in Company with the foreign key in Position.  I included screenshots.

 

I simply want to be able to when in the data entry for Position to be able to use a drop down to select a company, then have the CO ID populate.  I for the life of me cannot get the drop down to work properly or populate the CO ID.

 

I'm new to FM, so be gentle please, but understand relational DB's.  One thing I'm also not sure of, when doing the layout, should the Company Field in the Position Layout point to the company name from Position or Company?

 

Thanks

 

[comment]

Your value list should be defined to use values from field Company::CoID, also displaying values from Company::Company Name.

 

One thing I'm also not sure of, when doing the layout, should the Company Field in the Position Layout point to the company name from Position or Company?

From Company. You should not have a Company Name field in the Position table.

See also:
http://fmforums.com/forum/topic/89771-passing-data-across-layouts/?p=412030

[jlarmen]

Your value list should be defined to use values from field Company::CoID, also displaying values from Company::Company Name.

From Company. You should not have a Company Name field in the Position table.

See also:

http://fmforums.com/forum/topic/89771-passing-data-across-layouts/?p=412030

Thank you comment for you quick response. I did as you mentioned, and see the company names (in Company layout) in the drop down. I have the Company Name as the first dropdown with CO ID as the 'show also". What I don't understand is why when a company name is selected from the drop down, the field I have on the layout, CO ID (from the Position Table) doesn't auto populate with the CO ID from Company when they have a relationship.

In the picture, you see the dropdown with a company and the id associated with it (from Company). To the right of the drop down is the field CO ID from Position table. Shouldn't that auto populate with the related ID since it's connected? Can I make it populate with the matching CO ID?

[comment]

Please refer to the demo file attached to the post I linked to earlier. It seems you have your fields and drop-downs mixed up and that file will show you exactly how it needs to be set up.

[jlarmen]

Thank you.  I looked at the files you provided.  I think I have it setup the way it makes sense and looks like it should work.  The drop down for the company is working, it's the CO ID that isn't populating.  I noticed you used portals.  I use on the Company table to show the current positions for that company, but it doesn't show any records until I manually enter the CO ID in the Company template.

 

I attached the file, maybe you can see where I'm going wrong.  Thank you.

 

 

Please refer to the demo file attached to the post I linked to earlier. It seems you have your fields and drop-downs mixed up and that file will show you exactly how it needs to be set up.

 

Job Tracker.zip

[jlarmen]

Ok, I looked more in depth into it and figured out I was using the Company Name for the drop down list, not he Co ID which is the foreign key.  I made that change, added a portal to the Company layout to show any related positions that have been applied to.  The problem I'm running into is on the layout, I have Company Name which is actually the CO ID and I have the value list set to display the company name as well so you can see which company your're selecting.  After selecting a company, it now properly shows up in the related portal/company record.  The problem is after selecting, the drop down list displays the CO ID, not the company name. I am clueless as to get the actual Company Name to display after selecting the CO ID.  Do I need an additional field that looks up the CO ID?

 

I included the file again in case anyone else might be lost or might have a solution.

 

 

Thank you.  I looked at the files you provided.  I think I have it setup the way it makes sense and looks like it should work.  The drop down for the company is working, it's the CO ID that isn't populating.  I noticed you used portals.  I use on the Company table to show the current positions for that company, but it doesn't show any records until I manually enter the CO ID in the Company template.

 

I attached the file, maybe you can see where I'm going wrong.  Thank you.

 

 

Job Tracker.fmp12.zip

[bruceR]

This is a long standing issue with the difference in behavior between drop down lists and popup menus.

 

One approach is to change the control style of the field to be a pop-up menu.

 

However, you can't tab into a pop-up menu (on the Mac anyway)

[jlarmen]

I actually got it work using a Lookup Field next to the drop down list.  Then changed it to pop-up as you suggest and voila, worked perfectly.  Much easier solution.  Thank you!

 

 

This is a long standing issue with the difference in behavior between drop down lists and popup menus.

 

Change the control style of the field to be a pop-up menu.

[comment]

I actually got it work using a Lookup Field next to the drop down list.