Create record in related table using a drop down value list when value is not present in value list!

[GarthT]

Hi - I have tried to do this before and although I was give a script that did what I wanted (which I could not really understand!) I still wonder if my problem can be solved using FileMaker features without scripting.

I have three tables People--<Employment>--Occupation

In other words I want to record a person's job history.

I have set up Employment as a join table, with allow creation of records in this table checked.

I have made a portal to record the employment history and this seems to work; populating an employment field in the portal via the Occupation foreign key using a value list based on the Occupation Name from the Occupation table.

What I would like to do - but don't know if it possible (and/or sensible), is use a simple technique to create a new record (occupation) via the portal in the occupation table, if the occupation does not exist in the current value list based on the current Occupation table

I know I could do something similar using a simple value list, but I understand that if I base the value list on a table, this will have benefits if I need to change something in the future, say all cab drivers have to be rebranded as taxi drivers! Similarly, I could go to the occupation table and add a new occupation there. However, it seems more elegant to create the new occupation via the portal if this is possible.

I hope that makes sense

I have attached the database - hopefully that will help.

Thanks for any guidance

Garth

OccupationEmploymentTest file.fmp12

Edited November 28, 2016 by GarthT
change title

[webko]

Not too hard - in Layout mode, just under where you define which Value List to use, is an option to Allow Entry of Other Values - seems to work nicely if you tick that

[Steve Martino]

@Webko  Yes but does that create a new record in Occupation?

[webko]

From my testing of the given file, yes

[GarthT]

Thanks Webko

I had tried this technique and it is not working correctly for me. Whilst I can create an occupation in the portal window this does not transfer to the occupation table. Also in the Employment table this creates a fkOccupationID that is the name of the occupation rather than the serial number represent a new record.

When I look at your screen shot you have an option ticked that is not available to me, "over ride data formatting with value list" - could this be the difference? I have attached the file again set up as you suggested and a screen shot of my window to show the settings

Garth

OccupationEmploymentTest file.fmp12

[bruceR]

Webko: try again. Looks like you're wrong, it does NOT create an Occupation record.

[Wim Decorte]

Not sure what your expectation is.  By using the "other" option in the value list you will NEVER create a record in the occupation table.  By entering data in the portal itself, you are forcing the creation of a related record but only if you use a field that exists in the Occupation table.

[bruceR]

Note that this topic is an XPOST and is listed on FileMaker Community.

Here my mod, with script, also posted to FileMaker Community.

OccupationEmploymentTest MODBFR.fmp12

[GarthT]

Thanks for your input everyone - I now understand that I cannot do what I want using "simple" relationship techniques and need to use a script.

[webko]

6 hours ago, BruceR said:

Webko: try again. Looks like you're wrong, it does NOT create an Occupation record.

It doesn't... and it does - I also filled out the next field in the portal, which does then create the record in Occupation

But as noted, the FK is whatever was put in the Other option, and the display is whatever was put in the next field - so scripting it to work correctly is definitely the way to go

[GarthT]

Hmmm - interesting - kinda makes me think there could be a way............