Record Navigation using a Drop-Down List?

[drtree]

Hi All,

First, thanks in advance for any help I may receive. I have a database full of job applicants and the application process consists of several steps. My boss has asked me to put together a form that will allow the HR people to track this process using FileMaker Pro. I put the form together without any problems (using a series of radio buttons) but he has also asked me to set it up so that the HR people can get to records by going through the records and also by using a drop-down list (of applicant names). I have set up a drop-down list and have it connected to a value list (so a complete list of names comes up) but I can't figure out how to set it up to go to the record that matches the person that is selected from the drop down list. Should I be using a scripted button (with a Go To Related Record step) or is there an easier way to do it?

Thanks,

Doc Tree

 

[CryoGen]

Hey Doc, which version of FileMaker are you using? How to approach that may vary based on what you have.

 

[drtree]

Cryo - Thanks for the response. We're using FileMaker 8.5

 

[CryoGen]

Others please feel free to chime in, but you'll probably achieve this best using a portal with a Go to Related Record script. I apologize ahead of time is it sounds like I've oversimplified this or appear to suggest steps that are obvious to you ... I don't know your familiarity with the Filemaker.

In this process you create a relationship so that you view all the records associated with your applicant names in a scrollable list. This is done simply enough.

1. Create a global text field called "g_applicant_names" in the same table as the one with the applicant records you want to be directed to. Make it a text-formatted field and select the Options tab, then choose "Storage" and choose "Use global storage (one value for all records)". Place it on a new blank layout that points to the table with the applicant names that you're drawing from. Then attach the value list you've already created to that field. You'll be prompted to select the proper table when you create the layout.

2. Go to your Define/Database from the File Menu and go to the Relationships tab. Find the table occurrence that has the records (you'll need it soon). Create another occurrence of this table by clicking the green plus sign at the bottom left of the table.

3. Here's where you'll need to know your own data. From the ORIGINAL table scroll down by clicking the little down arrow in the bottom middle of the box until you've found the new text global record you created (g_applicant_name). Now go to the new table occurrence you created and scroll down until you've found the original applicant name field, which is probably the name field that your value list is based on (don't worry, the other table won't jump back to the top when you leave it). Drag the name field from the ORIGINAL table over the applicant name field in the new table occurrence box. When you're done a line will be drawn between the two (and it will have a little box with an equal sign in the middle). When you've done that you've completed the relationship.

4. Next go back to your blank layout and create a portal -- just below your global field is probably a great place -- by clicking the portal tool, which is in your status area toolbox above the button tool and below the circle. When you're done drawing it the Portal Setup dialog box will show. Find the name of the new table occurrence you created and select it. Choose your options from the dialog box. Your list is probably long, so be sure to click the "Show vertical scroll bar" checkbox. When you click OK, the next dialog box will ask you what fields you want in the portal. Choose the ones you want, but make sure that the fields can adequately describe what's in that record (a title or a date).

5. Click "Done" and you now have your portal set up. Go to browse mode and have a look. Sadly you'll see that there is nothing in it. That's because the relationship wants to match the values in the global field with the values in match field in the table. And, of course, you haven't set a value yet. Sooooo, go up to your global field and click in it. Your drop-down list should appear. Select a name and all the records that match that name should appear in the portal.

6. OK, you're almost there. Now you need to create a script that will take you to the detail page for that applicant's record. Here's how: Go to Scriptmaker in the menu bar and click "New". Name your script "Go to Related Applicant Name". From the script options at the far left of the dialog box, double-click "Go to Related Record" to make it a script step. You have a few choices here. First, under "Go to Related Record From" choose the new table occurrence/relationship you created. Next, under "Show record using layout" choose the detail page you have for those applicant records (you may need to set one up if one doesn't already exist). Finally, under "result options" choose "Show only related records", so that only those records appear. If you want to limit those records to just the single associated record, choose "Match current record only". Close Scriptmaker.

7. Now all that's left to do is attach that script to the appropriate field in the portal. Click to select the portal field that you want to attach the script to so that the four handles appear on the corners, then go to the Format menu and select "Button setup". Click the "Perform Script" option on the left and click "Specify". Now just find the script named "Go to Related Applicant Name", select it and click OK, then OK again to close the Button setup dialog box and then go to back into Browse Mode. whn you click the scripted field in the portal you should go to the related record's detail page.

I don't think I've left anything out, but let me know if it doesn't work for you and we'll look closer at what's amiss. I hope this helps.

CG

 

[drtree]

CryoGen - I didn't have time to try out your solution yet, but I just wanted to say thanks. I am kind of familiar with FileMaker - I've been using it off and on for a few months and was thinking a portal was the way to go, but wasn't sure if there was another way.

Thanks Again,

Doc Tree

 

[MythosRaconteur]

Hi CG,

I have this EXACT same requirement, however I am building an internal security matrix to complement FMP's.

I have a set of "security levels" which are essentially just names for the sets of privileges. A privilege is CRUD (create, read, update, delete) for a given module in the system.

What I am trying to do is make a UI to maintain these privilege sets, such that, a drop-down list shows all security levels, and choosing one will populate the matrix of checkboxes showing which CRUD functions apply to which modules.

It is very simple but seems to be difficult to do in FMP.

You explanation above to Doc has gotten me pretty far, but I am still having problems understanding both the data architecture and the implementation. Would you be so kind as to illuminate a couple of things for me?

1) Data architecture-wise... I have one table called SecurityMatrix, and it has the following fields (simplified for this example):

kp_security_level_id
security_level_name
flag_con_c
flag_con_r
flag_con_u
flag_con_d
flag_active
g_security_level_name (per your instructions above)

I have the following rows in that table:
ROW 1
security_level_name = DBA I
flag_con_c = 1
flag_con_r = 1
flag_con_u = 1
flag_con_d = 1
flag_active = 1

ROW 2
security_level_name = Seasonal Help
flag_con_c = 1
flag_con_r = 1
flag_con_u = 0
flag_con_d = 0
flag_active = 1

It seems to me that from what you are saying, I need to create two instances of the table in the relationships graph
and link the g_security_level_name field of instance #1 to the security_level_name field of instance #2. Is that correct?

Also, my value list that populates the drop-down should just "Use values from field..." on the SecurityMatrix::security_level_name field, right?

I have that set up but, I do not get any values in my value list, and, needless to say, the portal (per your instructions) is not being populated.

What am I missing?

Thanks a million! This is driving me nuts... I am an OO architect used to RDBMS like Oracle and SQL Server as well as OODBMS like GemStone and Versant, so this entire paradigm is a vast departure for me.

Cheers,

Chris

 

[JeanW]

Just as question...

Why do you want to bring the hidden, strong security of FileMaker to the front end of your application, where what you want is builtin the FileMaker security?.

>> a drop-down list shows all security levels....
will open the door wide...

 

[MythosRaconteur]

Additional requirements, maintainability, and customization to name a few things. On top of that it needs to be maintained by non-techinical people who have no idea what a security system even is, let alone should be mucking about in the depths of the FMP system tools.

Any help or input on the actual question?

 

[CryoGen]

MR, yes the scenario you described should be correct. One thing that always catches me off-guard on these things is the placement of the global field in the relationship. For whatever reason I'm always fumbling that. It is probably worth a check.

Go to your relationship graph and double-click the equal sign between the original table occurrence and the second (that will bring up the relationship definition). Take a look at where the global field is positioned. It should be under the ORIGINAL table, not the second table occurrence. If it's backwards the relationship is pointing the wrong way and there will be no data to populate the portal.

If that doesn't work we'll look elsewhere.

 

[JeanW]

>> ...On top of that it needs to be maintained by non-techinical people who have no idea what a security system even is...

I hope for you you're not serious about this....

 

[MythosRaconteur]

Hi CG,

Ok, I re-did everything and have it working for the most part.

Now, what I get is all of the CRUD values in the portal. How do I just show the one set (attached to checkboxes) for the record I selected? Or is that what the script is doing (I sorta skipped that part as it seemed to be more related to Doc's specific problem).

Thanks for the help!

 

[CryoGen]

Yep, the "Go To Related Record" script step will get you to that data (assuming you've made the layout that points at that table).