Jump to content

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


GarthT

This topic is 2727 days old. Please don't post here. Open a new topic instead.

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
Link to comment
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

Link to comment
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.

Link to comment
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

Link to comment
Share on other sites

This topic is 2727 days old. Please don't post here. Open a new topic instead.

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
×
×
  • Create New...

Important Information

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