Is this standard practise to populate fks from pks in layouts?

[fmow]

It seems rather clumsy. So here's what I do. Two tables one is employees and the other companies, pk for companies has to populate its fk counterpart in employees. On my layout I choose to show Company Name to populate a fk_Company_Serial no, and then I have the company name looked up and auto inputed. I 'd like to know if there's a more elegant way to do this. Or any other way for that matter. 

 

Regards.

 

 

[bcooney]

You can accomplish this in many ways. In all cases you want the CompanyID in the Employee record, not its name.

 

1. Create a portal based on an employee TO on the Company form for showing/adding related employees. Turn on "Allow Create" in the relationship (Company>Employee where _kP_CompanyID=__kF_CompanyID). The foreign company key will auto populate as you add records via the portal.

2. On an Employee form, format the _kF_CompanyID field (the company foreign key field) to be a popupmenu that uses a value list consisting of __kP_CompanyID and Company Name, show just the second field (company name).

3. Layer two fields on the Employee form: _kF_CompanyID and the related Companies::Company Name field. Format the key to be a drop down list, and the Company Name to not allow enter in Browse mode.

4. Use a popup selector (I have a demo posted on here somewhere) to pick a company from a popup window, and carry the ID back into the foreign key.

 

And that's just off the top of my head. FM supports many techniques for each need and depending on your UI goals, one may be better than another.

 

hth,

Barbara

[LaRetta]

Since you have inserted the CompanyID into your Employees table, there is no need to look up the Company name.  Simply cross place your field, i.e. since they are now related, place the Company::CompanyName field directly onto your Employee layout.

 

Also, if you have created a value list based upon all values in the Companies table (base it on the CompanyID and also show second value of the Company Name), setting below to only display values from the second field, you can just place the Employees::CompanyID on your Employee layout, select the company and when you exit the field, it will display the company name instead (if you use pop-up menu instead of drop-down list).  

 

Your method of looking up the company name is problematic if the company name changes (from the Company table) because Employees will not recognize the change.

[fmow]

Barbara and LaRetta please accept  my warm thanks for your much valuable help. 

 

@Barbara.

Many thanks for laying down and clearly describing the most common approaches to my task at hand. 1-3 are perfectly clear, and I 'll need to look up no. 4. I am quite possibly going to go with the first approach. Having said that, I 've heard that, when displaying a portal to a child record fm will output and additional blank record in the portal list that can confuse the user, so I am a bit worried about that.

 

 

@LaRetta.

Hi LaRetta. Thanks for the heads up about cross placing the field, that's what I'll be doing in the future. 

 

Thanks also for the tip on using a pop up menu instead, so fm will keep showing company name as opposed to company id no. My problem with this is that either via a pop up I cannot have the user use autocomplete which will enable them to quickly input the Company name, in a list that might grow to be really large. I am unsure though why the autocomplete is greyed out when I use the dropdown list. This behaviour is only noted with the particular list which is showing only the second field. Can't such lists feature autocomplete and if so why?

[bcooney]

#4 see this demo -  http://fmforums.com/forum/topic/73737-value-list-based-on-another-field/?p=349257

To me, this is the way to go for a long list of choices.

[David Jondreau]

I often use a "Company Name" field that the user types into. A script trigger runs a script that looks up the PK of the company (now using ExecuteSQL) and, if 1 found, sets the fk field. If none or more than one found, the user gets a "Picker" layout (a new modal window).

[fmow]

Many thanks to all who contributed. My apologies for taking some time to reply, but I prioritize my replies based on my workflow and what I am dealing with at the moment.

 

Ok, I 've gone through a couple of approaches. One I found pretty good, no. 3 from what bcooney suggested, (and I have doubts if I should use it after all) is to work with a value list with two fields showing the second (name) and populating the fk via the first (pk), and include the NAME from the related table. In terms of formating NAME is first then adjacent to it, in a much shorter formatted field the fk to be populated via the value list. I use conditional formating to white out the fk on the right of the name once it's completed, to not confuse the user with key serials and to not clutter the page. I feel rather happy with this, even if no autocomplete is available, , but I will probably end up scripting it as follows:

 

If anyone catches any mistake in my script, please do say.

 

This particular field populat

 

I am in a projects layout, I have the user input the name of the associate for the project (there can be many associates in one project). I grab that and go to the Clients table and find the pk via it, which I grab in turn in another variable, then I go to the JointTable (between Projects and Clients), create a record, and paste the last two variables respectively in the fk fields, and then go back to my original layout.

 

• Set Variable [ $Var; Value:Projects:Associate1 ]
• Set Variable [ $Var2; Value:Projects::_pkProject_Serial_Number ]
• Go to Layout [ “Clients" ]
• Perform Find [ Specified Find Requests: Find Records; Criteria: Clients::Name and Surname: “=$Var” ] [ Restore ]
• Set Variable [ $Var3; Value:Clients::_pk_Client_List_Serial_Number ]
• Go to Layout [ “JointTable” (JointTable) ]
• New Record/Request
• Set Field [ Joint Table::__fk_Project_Serial_Number; $Var2 ]
• Set Field [ Joint Table::_fk_Client_List_Serial_Number; $Var3 ]
• Go to Layout [ original layout ]
• Go to Field [ Projects::Associate1]

My only problem with this is that I have to include a superfluous Associate1-10 set of fields in the Projejcts table, to use for user input. I could use a global field as a placeholder and script a button instead to avoid that, but then the layout will be different, and I am not sure I 'd like it to look that way.

 

There's also an issue about duplication, that is if a user re-enters a different Associate value the old one isn't deleted in this scipt. I 'll solve that soon, and promptly, I hope. Shouldn't be too hard. 

 

Regards to all.