Solved

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

Posted on 2011-02-13
7
1,344 Views
Last Modified: 2012-05-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
0
Comment
Question by:SirusComputers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 12

Expert Comment

by:North2Alaska
ID: 34885245
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
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 34885248
Now, if I could only spell Threw..  LOL
0
 
LVL 1

Author Comment

by:SirusComputers
ID: 34885380
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:SirusComputers
ID: 34885447
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
0
 
LVL 12

Accepted Solution

by:
North2Alaska earned 500 total points
ID: 34887322
OK, I think I see where you might be lost.  :-)  First, don't panic, but let's see if we can really blow your mind.  :-P

The relationships screen shot you sent is commonly called the Table Graph or TG and each table is a Table Occurrence or TO.  If you are familiar with any other database or design tools, you may think the TG is an ERD, but it's not.  You can have many TOs of the same table.  I tend to think of these as defined searches.  For example, here is the TG of a project that I'm working on.   Herd.pdf  As you can see there are only six actual tables but many TOs.  

So back to your database, I created a new demo to match your sample to give you a clearer picture.  First, I use the Anchor buoy method (there are lots of great examples of this method on the net).  So, your relationships always start on the left and move to the right.  I create three TOs for CaseWorkerInformation, one for the actual relationship; ci » CWI.  One for the Municipality value list; ci » CWI__ByMuniValueList.  And a third one for the technique we use for getting the Key but using the name; ci » CWI__Name.

Next I created two Value list.  The one for all Municipalities is pretty straight forward (I would consider creating a table of Municipalities and building the value list from there). The other we need the addition of using only those that are related.   ValueList.tiff

Finally, I create a script, AssignWorkerKey, that will be use by a trigger to assign the WorkerCode to the ClientInformation table:  Set Field [CI::_FKWorkerCode; ci » CWI__Name::__PKWorkerCode].

Now we put it all together.  I created a Layout, User Interface, that will allow you to create a new record (or change an existing one) and assign the worker to the client based on the workers that are in the same Municipality as the client.  Play around and see if you can follow what is happening.  Then fire back additional questions.

Note:  I use a site to help with naming conventions and the like.  It's worth a read...  http://filemakerstandards.org/

Good Luck...
Relation-Selection2.fp7
0
 
LVL 8

Expert Comment

by:Tocacar
ID: 35364704
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question