asked on 2/14/2011

How do i auto populate a field to a related table in FileMaker Pro 11

Hey everyone,

I have three tables set up in my database. Caseworker Information, Client Information, and Mail Log. What im trying to accomplish is :

Step1 on caseworker information form...Enter the municipalities that the worker supports. <----- I did this by setting a value list and having check boxes.

Step 2 Once case worker is enrolled go to the client information section. Within the client information form... Have a drop down list of municipalities. select the manicipality the client lives in <---- I did this with the same value list that i used in step1 for manicipalities.

Step3 Once selected on manicipality...I have two other fields on the client form.. Caseworker code which is the primary key in the Caseworker Table and another field case worker full name.

Now what i want to do is... after selecting the municipality for the client... have two fields... Case worker Name and Case Worker Number. The case worker number field would be sorted by the selection of what manicipality you selected . For Ex. If say Mr Smith did manicipality A and Mrs. doe did manicipality B. An within the form you selected manicipality A. Then Mr. Smith would be the only one to show up under the caseworker code section. After the case worker code section is filled in...i would like the case worker name section to automatically populated from the field caseworker code...since everycase worker has a unique number.. Please help ive spent 5 hours on this and drank 3 red bulls and cant figure out what scripting to do on this? Thanks

attached to this message is a screen shot of the relationships. Thanks again. Ray Z
Relationships.jpg

FileMaker Pro

Last Comment

Tocacar

4/11/2011

North2Alaska

2/14/2011

This can easily be done if you understand "Context" and relationships. First, create a relationship between ClientInformation::Municipality and CaseWorkerInformation::Municipality. Once you have completed step 2, the relationship will display all the CaseWorker records that have been assigned to that Municipality. Now you can create a Value List using the context of that relationship to produce a list of CaseWorkers.

Unless the Case Worker Number is a know data element that everybody just "Knows", the I would only use the name and let FM fill in the number for you.

Here is a little sample I through together for another question. You may have to study it a little to get the gist of it, but I think it is what you are looking for. Focus on the Part Picker layout (if it would help, I could rename it to CaseWorker Picker) and look at the field ui_rev (this could renamed to Case Worker). What it allows you to do is pick a value and have it return the key.
Relation-Selection.fp7

North2Alaska

2/14/2011

Now, if I could only spell Threw.. LOL

Ray Zuchowski

2/14/2011

ASKER

Thanks for help but still so lost... I thought you only make one relationship to each table ? I posted in my above message my current table setup.

Ray Zuchowski

2/14/2011

ASKER

Ok so say i create a relationship between ClientInformation::Municipality and CaseWorkerInformation::Municipality and i make it look like this...look at picture pasted to this for relationships. Now my value list in clientinformation for municipalities is built off the master value list used in caseworkerinformation. What i want to happen is... you enroll your case worker to the municipalities they cover. Then in clientinformation... when you select the municipality the client lives in.. it then triggers in the field worker code on that same form to shower only the workers that serve that municipality.
relationships2.png

ASKER CERTIFIED SOLUTION

North2Alaska

2/14/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Tocacar

4/11/2011

This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.