Creating new related record when user edits conditional value list

[mikedr]

I have three tables, patent applications, clients, people.

 

 

The people table lists name, person type, and client.  A person's type can be attorney or paralegal. 

 

Each patent application has an associated attorney and an associated paralegal, as well as a client.  These are defined as text fields.

 

To set up value lists listing just the attorneys for a given client and just the paralegals for a given client, I set up the following (I'll limit this to just the attorneys, but the same process occurs for paralegals).  In the database relationships, I have an instance of matters that is related by client to an instance of clients, and I have an instance of people that is related to the instance of matters by bothclient and person type.

 

For entry of an attorney within a patent application, I have a value list set up "Just attorneys of client."  The value list is defined as those names from the instance of people, where only related values starting from the instance of matters are shown.

 

All of this works perfectly.  If we have two clients, BigCo and SmallCo, and BigCo has attorneys John Smith and Alice Grables, and SmallCo has attorneys Frank Doe and Sarah Munsen, a patent application that is for BigCo, when using a dropdown list or a popup menu in the layout, will only let you select from BigCo's attorneys (Smith or Grables) and not from SmallCo's attorneys (Doe and Munsen).

 

The problem is as follows.  Say BigCo hires a new attorney, Kevin Kramer.  Right now, you have to go to the People layout, create a record listing Kevin Kramer by name, his type (attorney), and his company (BigCo), before you can assign him to a patent application via the patent application layout.

 

This is what I'd like to happen.  With popup menus -- but not for drop down lists for reasons that elude me -- I can say "allow editing of value list" for this type of value list.  The idea is, with a new patent application for BigCo, under the attorney field, if I see that Kevin Kramer hasn't been added yet, I can simply select "edit value list" on the popup menu, and after I enter his name, a record in People is automatically created with his company as BigCo and his type as attorney.

 

Right now, if I permit allowing editing of value list, and enter in Kevin Kramer's name if he has been entered in Individuals yet, I get an error that this is not a permissible value.

 

Is there anyway to achieve this type of functionality?  My kludge is a little button nearby to say "add new attorney," and after prompting for a name, I could have my script create a record for Kevin Kramer, and assign his company and type appropriately. 

 

Thinking out loud, perhaps a good kludge is to add a "fake" attorney name "Add new attorney," which if when selected would trigger the script?

[eos]

Right now, if I permit allowing editing of value list, and enter in Kevin Kramer's name if he has been entered in Individuals yet, I get an error that this is not a permissible value.

 

Since your value list is based on field values, the only method to edit the list is adding/deleting records in that field's table. 

 

Right now, you have to go to the People layout, create a record listing Kevin Kramer by name, his type (attorney), and his company (BigCo), before you can assign him to a patent application via the patent application layout.

 

[…] My kludge is a little button nearby to say "add new attorney," and after prompting for a name, I could have my script create a record for Kevin Kramer, and assign his company and type appropriately. 

 

As you have stated correctly, if want to use an attorney in your solution, he/she must either already exist, or be created. If you have a script that saves you a round-trip to another layout and creates the new record, using the minimum required information (name, person type and company), which can be entered/selected via a popover – why do you consider that a kludge?

 

You could amend that script by 1. setting a flag for this new record, signalling that it should be checked later, and 2. automatically create the related record for the patent using the new person – which was the purpose of the whole exercise.

[mikedr]

Oh very good. I feel like I'm beginning to understand how FM works! I guess I consider this approach a kludge only because what I want it to do would be cleaner in my eyes.

Follow-up question, however, as to creating a new record via a popover. I'm not entirely sure how this would work. The fields that would be present -- what table would they be a part of? That is, would I simply specify the related (people) table, and since no records have been related to the current patent application, it would automatically create a new record?

[mikedr]

I found a pretty good solution to this problem finally -- and for completeness, post the link.

 

http://fmforums.com/forum/topic/94624-new-record-via-drop-down/