Problem using a value list based on a table in a portal

[GarthT]

Hi - I hope someone can point me in the right direction.

I am trying to create a simple database (to help me understand a principle) before using the concept in a more complex solution I am trying to design

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. This also seems to work.

What I would like to do - but don't know if it possible (and/or sensible) is use the "allow entry of other values option" in the popup menu AND use this choice to create a new record (occupation) in the occupation table, if the occupation does not exist.

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! Or something like that.

I hope that makes sense

I have attached the database - hopefully that will help.

Thanks for any guidance

Garth

People and Occupation.fmp12

[bruceR]

Well, the first thing is to actually do what you claim you are doing: use the occupationID in the join table; not the occupation name. See corrected value list setup.

 

 

Edited July 6, 2015 by BruceR

[GarthT]

Thanks Bruce - I had got myself in a muddle!

I think I now have set things up properly, but I still cannot create a new record in the occupations table. If I try and use a popup menu with the option to created other values checked it creates a text entry in the join table in the _fkOperationID field, which is obviously incorrect.

Where do I go from here?

I have attached the latest version with this error included.

People and Occupation v.2.fmp12

[bruceR]

This doesn't solve the new list item issue.

But please look, once again, more carefully, at the value list setup from the previous post.

Your file still doesn't use the recommended setup; that is, it is still displaying the ID.

Edited July 6, 2015 by BruceR

[bruceR]

Also see:

Drop Downs Should be the UI of Last Resort

https://storify.com/lukew/drop-downs-are-the-ui-of-last-resort

[GarthT]

Hi again.  I  seem to be going  around in circles at the moment.

I think i have set things up as you have.

In the portal I have used the fkOccupationID, which is based on a value list based on the pkOccupationID and the OccupationName, from the Occupation table, but I have set this to only show the value from the second field, i.e. the OccupationName.

This seems to create the correct entries in the Employment join table, when I use one of the occupations that is already in the occupation table. The problem remains that if I use the option to create other values - it puts the new occupation name in the join table; where a foreign key should be.

What I would like to do, if possible, is create a new record in the Occupation table, that will then be available in future popup menus.

I have had a look at the link you sent. I like the concept, but with  a relatively long list of occupations, I think these will not be very practicable.

cheers

Garth

People and Occupation v.3.fmp12

[dwdata]

Hi Garth,

Attached is a bit of a HACK using a extra entry in the Occupations table and a trigger script, but I think it work in the desktop version of FMP. You'll have to test it out in WebDirect and Go though.

Good luck!!! 

vl_new_entry.fmp12

vl_new_entry.zip

[GarthT]

Hi Don

thank you, thank you, thank you .......

That is brilliant. I have worked through the script (admittedly in a copy and paste fashion) and it works with my own database fields and tables. I would never have got there on my own. 

Would you be able to go through the script and explain what each line does? I would love to understand this some more. Is there a good learning source where I can find out more about how these scripts are written - I have used Lynda.com to get some understanding of Filemaker.

Thanks again

Garth

[bruceR]

You say you are a surgeon, handling sensitive patient data, and you are asking these questions?

[GarthT]

Thanks - of course, I am aware of patient confidentiality. My database when it is finished will be held locally, for my personal use, to enable me to look at my outcomes. When it is complete there will be no demographics - just random reference numbers and the solution will be encrypted too. I have included demographics in the design as I have based the structure on the commonly used examples of customers and orders - it makes it easier for me to follow the tutorials.

 I understand some lines of code and see what it is doing, but some I don't, a long-hand explanation would have been helpful. 

I am a surgeon and there my expertise lies; I learnt my profession from experts and now pass on my knowledge to others. I am trying to learn filemaker from the ground up and I appreciate the experts here who share their knowledge and teach me.