Surgical database - populate a field with pop-up menu

[GarthT]

Hi - I suspect there is a straightforward way to do this but I can't find it!!

I am creating a solution to store then analyze some of my surgical workload.

I have a table called "Operations" and this is related to another table called "Occupations" via a join table called "OccupationAtOperation".

What I would like to do is have a field in the Operations table that is populated via a pop-up menu that is looked up from the relevant field in the Occupation table.

I can create such a menu using a value-list, but I can't figure out how to add to the list without going to the Occupation table itself.

Is such a thing possible? Can I enter the Occupation field in the Operation table and enter data that also creates a new occupation record in the occupation table?

Also, when I come to putting an occupation in the Operation table this needs to be a constant for the operation and NOT change in the operation table if the patient changes their occupation over time.

I hope that makes sense!!

thanks

Garth

 

 

[eos]

Look into the sample file; is that what you mean?

 OperationsOccupations_eos.fmp12.zip

[GarthT]

Thanks EOS. That's helpful, but not what I am looking for at the moment

I have moved on to the patients' occupations at the time of surgery

So what I want is a field called "Occupation", eg. Teacher, bus driver etc. in the table "Operation" that I can populate from a table called Occupations. I envisage this as a many to many relationship - a patient can have many occupations (over time) and an occupation can be done by many patients; thus I will connect the two tables with a join table; "OccupationAtOperation"

I want to be able look at the Occupations table and use this as a value list and, if the occupation is not present, add a new occupation (record) without having to go back to the Occupation table.

thanks

Garth

[eos]

So what I want is a field called "Occupation", eg. Teacher, bus driver etc. in the table "Operation" that I can populate from a table called Occupations. I envisage this as a many to many relationship - a patient can have many occupations (over time) and an occupation can be done by many patients; thus I will connect the two tables with a join table; "OccupationAtOperation"

In that case, you don't need a join table “OccupationInOperation”, because if we're talking about one patient with one occupation (at that time) per operation, then the Operation table already is that join table between People and Occupation:

People --< Operation (id_patient, id_occupation) >-- Occupation

The Operation table then is also your history of a patient's occupations over time.

And if you have nothing more to say about an occupation than that it exists, you wouldn't even need the Occupations table: create your value list based on Operation::occupation and display the field as a drop-down with that VL.

[GarthT]

Thanks. I was tying myself in knots with this. 

Is there anyway I can create a value list that "grows"with each new occupation that I add? Without having to open up the manage value list dialogue?

G

[eos]

Is there anyway I can create a value list that "grows"with each new occupation that I add? Without having to open up the manage value list dialogue?

create your value list based on Operation::occupation and display the field as a drop-down with that VL.

[GarthT]

Thanks - I was using pop-up menus!

I mixed this up with drop-downs!!

Cheers

Garth