Jump to content
GarthT

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

Recommended Posts

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 by GarthT
change title

Share this post


Link to post
Share on other sites

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

Screen Shot 2016-11-29 at 9.42.26 am.png

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Screen grab VL.jpg

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

By using this site, you agree to our Terms of Use.