Jump to content
nsns1972

Populate a value list or drop down list with Field names

Recommended Posts

nsns1972

I would like to populate a value list or drop down list with the field names from a related table. Is this something that is possible?

Share this post


Link to post
Share on other sites
AHunter3

With the field NAMES from a specified table? Unusual, but yeah, easy enough if you don't mind running a script to populate a local field before entering the field with the desired value list.

 

 

Set Field [LocalTable::TextField, FieldNames(filename; tablename) ]

 

where filename is the name of the file that the table is defined in and tablename is the name of one of the table's table occurrences.

 

(In most folks' solutions there will be at least one occurrence that has the exact same name as the table itself, but if not choose an occurrence name).

 

(Also: be wary of a TO name that is also the name of a layout; the function oddly enough looks first for layouts of that name and only looks beyond that for TOs of that name if there are no such named layouts)

 

It has to be a stored value to be useful for value list purposes. You could use a stored-result calc field using the above formula and derive your value list from that, but then it won't update if you rename, delete, or otherwise change what constitutes the names of all fields in that table. So instead we create a field, RecID, = Get(RecordID), a number field, and selfjoin the table you're in to itself by that field (actually therefore joining the record to itself as well, since the field is unique to the record);that lets us define a VL as all RELATED values of NewSelfJoin::TextField. You just set that TextField to the above formula in a script that fired off when you clicked the field. The second script step puts the user in the field [select/play] and it should at that point display the value list of fields from the specified table.

 

Note that the table with the TextField (and therefore with the value list) does not even need to be related to the tables whose fields you want to populate the VL. It only needs to be related to itself so as to reference the local fields' values as related values.

Share this post


Link to post
Share on other sites
nsns1972

That sounds like just what I need. I'll see if I can do it. I am a novice though, so please be patient with me - I may need some additional guidance.

Share this post


Link to post
Share on other sites
nsns1972

I have tried this - but I can't seem to figure it out. Perhaps you could explain it more for me? I guess instead of Novice, I should have said beginner, because I am completely lost.

Share this post


Link to post
Share on other sites
AHunter3

a) Create field in the same table as the field that you want to have the dropdown list of field NAMES from some other table. It should be a text field. Give it the name "TextField A" for now.

 

b) Create a calculation field of result type "number", defined as Get(RecordID). Call it "RecID".

 

c) Go into Define Relationships. Click the "new relationship" icon 2nd from far left at bottom left corner of your screen. On the left hand side pick, from the dropdown list of possible table occurrences, the table that contains the TextField A that I had you create. On the RIGHT HAND SIDE, pick the very same table occurrence. For fields, select RecID on both the left and the right and click the "Add" button. Your operand should be "=" by default. Result should be

 

RecID = RecID

 

When you click "OK" you should be prompted to provide a name for the new table occurrence, let's simply call it "SelfJoin".

 

 

d) I don't know the name of the field that you wish to have the dropdown value list. Let's call it FormattedField for now, just so I don't have to keep saying "the field that you wish to have the dropdown value list". I also don't know the name of the Table that has the fields that you want to display in the value list. Let's call it "ForeignTable". Finally, I don't know the name of the table in which FormattedField was originally defined, which I assume is also the native table of the layout that has that field on it; for now let's call that table simply "Table". In your actual script, you'll use the real names for the field and the tables, OK?

 

In the ScriptMaker, define the following script and call it Setup & GoTo FormattedField:

 

Set Field [Table::TextField A, FieldNames(Get(FileName); "ForeignTable")

Commit Record/Request [no dialog]

Go to Field [Table::FormattedField, select/play]

 

 

e) Go into Define Value Lists. Define a value list, call it ForeignTableFields", and define it to use field values, not custom values; from the overhead dropdown list of possible tables to pick your "first field" from, pick "SelfJoin". From the list of fields within SelfJoin, pick the field TextField A. Ignore "also display values from..." on the right, you won't be using that. Look down and you should see "Include all values" versus "Include only related values starting from ___". Pick "Include only related values", then from the dropdown list of possible table occurrences pick the table "Table". Click OK everywhere you see an OK button until it gets you out of defining value lists.

 

f) On the layout that has your field FormattedField, go into Layout Mode; double-click your field FormattedField and you should see a choice of how to format it, probably defaulting to "Edit Box" unless you already tried to assign it a value list before. You want it to be a dropdown value list, and you want it to use the value list "ForeignTableFields", the VL that we just created in e) above.

 

g) While still in Layout Mode, right-click your field and select Button Setup. Assign the button-function "Perform Script" and from the list of scripts pick the script "Setup & GoTo FormattedField", the script we defined in step d) above.

 

h) Again while still in Layout Mode, right-click your field again, select "Field/Control" and from the list of choices pick "Behavior". Uncheck the box for allowing field entry in Browse Mode.

 

 

OK you should be all set. Go into Browse Mode and try it out.

Share this post


Link to post
Share on other sites
nsns1972

OK - I did everything exactly as you stated, except one step. The script step would not allow me to do it as you had written. I replaced those that needed to be replaced with the correct field/table name.

 

here is what my script looks like:

 

Set Field [Client Fields::TextFieldA = FieldNames ( Get (FileName);"Client Fields]

Commit Records/Requests [No Dialog]

Go to Field [select/Perform; Client Fields::TextFieldA]

 

When I did the formatting, a dropdown was not a selection, so I made it a pop-up list.

 

Now, when I went to test it in the browse mode, nothing happens.

Share this post


Link to post
Share on other sites
AHunter3
When I did the formatting, a dropdown was not a selection, so I made it a pop-up list.

 

The available choices for formatting should be:

 

Edit Box

Drop-down list

Pop-up Menu

Checkbox Set

Radio Button Set

 

 

If that's not the set of choices you're seeing, something is substantially wrong.

Share this post


Link to post
Share on other sites
nsns1972

The available choices are as follows:

 

Edit Box

Pop-up List

Pop-up Menu

Checkbox Set

Radio Button Set

 

I am working in FileMaker Pro 7 - those are the choices that I have available to me.

Share this post


Link to post
Share on other sites
AHunter3

They must've renamed it somewhere along the line. OK, Pop-up list. Silly thing to call it, the direction that it actually pops is definitely DOWN, wouldn't you say?

 

Yes that is the correct formatting choice.

 

 

Clicking on the field = nothing happening, huh? OK, put the field TextField A on the layout so it's visible and tell me what's in it. If it's empty, it failed to get set properly and we need to look at what's amiss.

Share this post


Link to post
Share on other sites
nsns1972

It's empty - just a blank void of nothingness.

Share this post


Link to post
Share on other sites
AHunter3

OK, then you need to retract your steps.

 

a) When you go into Layout Mode, is "Client Fields" the name of the table occurrence that this layout is native to? If not, you need to change where you've got Set Fields [Client Fields::TextField A, FieldNames ( Get (FileName);"Client Fields")] so that it's setting TextField A via the correct table occurrence.

 

b) Is "Client Fields" also the name of the table whose fields you want to appear in the dropdown (sorry, ahem, "pop-up") value list? That's surprising because in your OP you said it was a DIFFERENT TABLE. This would be the SAME TABLE. That won't hurt anything (It can work if it's the same table) but it has me wondering if you made the error there.

 

c) Did you check to make sure you don't also have a LAYOUT named "Client Fields"? As I said, it's a weird function and looks for layout names and only switches to table occurrence names if there are no matching layouts by that name.

Share this post


Link to post
Share on other sites
nsns1972

I am going to re- do this from step one and see where I went wrong.

Share this post


Link to post
Share on other sites
nsns1972

And yet again, I have proven my complete ineptitude. I don't know what I have done wrong, but clearly I need to go back to elementary school and relearn how to follow instructions.

 

I re-did everything that you listed, from step a through h, and still, I have nothing to show, but a lot of frustration. Shall I try again?

Share this post


Link to post
Share on other sites
AHunter3

If you could join up as a full member you could post a copy of your file, and/or I could post a copy of a mockup showing how it works for you to copy from. As a guest you can't upload or download files.

 

It's entirely possible I screwed up some aspect of the directions, although a quick perusal doesn't reveal any errors to my eyes.

Share this post


Link to post
Share on other sites
nsns1972

I did sign up as a full member this morning, but have not yet been given full access privilage. I think that you are amazing and want to support this site and your assistance! As soon as I get the privilage, I'll let you know.

Share this post


Link to post
Share on other sites
AHunter3

It is a good site, with many talented & helpful folks. Welcome aboard.

Share this post


Link to post
Share on other sites
nsns1972

I guess I should tell you what I am trying to accomplish, and there may be an easier/better way to do it.

 

Short version: I inherited a filemaker file when I started here several years ago. Not knowing anything about the program, I was unaware that what my boss called the database, was actually just one really long table (161 fields to be exact). Recently, we've been frustrated by the time it takes to get from one end of the table to the other, so I started looking at other options and found that we could change how it was set up to make it easier. As a book publicist, it's vital that we keep accurate records of things that the press people we are in contact with do with our books.

 

We have a great deal of information stored in this table - including the following:

 

Person, Publication, address, city, state, zip, phone, fax, email.

 

Beyond the standard contact information for every media person we deal with (around 15,000), we also have a field for each book that we work with (around 63 fields) that we keep notes regarding any coverage that was done by any particular person at any particular media outlet. We've worked in this as a table, so that we could move fields from the back to the front where it can be seen next to the name of who we are speaking with.

 

Basically, it looks like a giant spreadsheet.

 

Once I have full access (still have not gotten that yet, though waiting patiently), I'll post what it looks like currently.

 

I had thought to break the table into two separate tables - contact info (media persons) and client fields (books). My next thought was to filter the client fields so that when we pull up one person (or one publication) we would then be able to select one client (book) that they are interested in. That's where that script that you helped with came in.

 

So - that's the background. I hope I did not confuse you!

Share this post


Link to post
Share on other sites
AHunter3

I promise to reread this and reply tomorrow, I'm too out of it tonight.

 

bleah, stupid cold medicine.

Share this post


Link to post
Share on other sites
AHunter3
I was unaware that what my boss called the database, was actually just one really long table (161 fields to be exact). Recently, we've been frustrated by the time it takes to get from one end of the table to the other

 

Is the thing that you're calling a "table" a layout which is formatted for Table View, perhaps? Are you aware that you can have multiple layouts that are all different layouts of a 161-field table, and the layouts need not all have all 161 fields on them?

 

Beyond the standard contact information for every media person we deal with (around 15,000), we also have a field for each book that we work with (around 63 fields)

 

All in the same table? That's not good.

 

I had thought to break the table into two separate tables - contact info (media persons) and client fields (books).

 

Yes, you definitely should.

 

My next thought was to filter the client fields so that when we pull up one person (or one publication) we would then be able to select one client (book) that they are interested in. That's where that script that you helped with came in.

 

All you need is a value list, or possibly a set of value lists. Not a complicated script that extracts FIELD NAMES. You need a way to associate records in one table with the records in the other table. These clients are pertinent to these media persons. Pick from list, assign.

Share this post


Link to post
Share on other sites
nsns1972
All you need is a value list, or possibly a set of value lists. Not a complicated script that extracts FIELD NAMES.

 

Really? So I've been making myself nuts for no apparent reason? Doesn't that just figure :-) How do I do this then without a script?

 

And yes, my boss has all fields on the same layout, including all book fields - in a table format.

Share this post


Link to post
Share on other sites
AHunter3

Oversimplified version. Do this and then I'll have you make it a bit more complicated later in order to make it a bit more useful.

 

The simplest is a new field in Clients (which is also Books? That's confusing but whatever...) with a dropdown value list of all Media Persons. To do the value list part, assuming that there's a field called "Name" in Media Persons, you define the value list to use field values and the field you point it at is the Name field in Media Persons.

 

 

In Clients (Books), format the new field with that value list; pick from list, then Media person's name is in the new field. Create relationship on Define Relationships screen between that new field and the Name field in Media Persons, which is the field you used for the value list.

 

Now, from Clients, you can reference any value of the media person that you specifically associated with that Client (Book). Like if you want to display the media person's cell phone number, you drag a new field onto the layout and specify that it should be Media Persons::CellPhoneNumber. You do that by clicking at the TOP of the "Specify Field" dialog box and it will drop down to let you select either Clients or Media Persons. You pick Media persons and then the list of available fields shows fields from Media Persons. find CellPhoneNumber and click "OK". You'll have to make adjustments due to these not being your actual field names or table names, but did that make sense?

 

 

This oversimplified model will not let you assign more than one media person to a given Client (Book) record. It has other problems (like what happens when media person "Sue Jones" changes her name?). But better to understand the concepts first.

Share this post


Link to post
Share on other sites
nsns1972

OK - I apologize that I had not responded sooner, but it's been a very crazy month.

 

I tried that, but because of the way we need to use the information, I don't think that will work.

 

I'm now a full member, so I could send you a copy of the database that I am trying to simplify and see what you think. Let me know if that would be easier.

 

Nikki

Share this post


Link to post
Share on other sites
NikkiS

Here is the file that I am trying to work in. I have two Tables - unrelated at this point - one is the Media Contacts Table, which details each media person's contact information and the other is the Client Books table, where each media's coverage or information about any particular book would be housed. I hope.

 

I have yet to figure out the whole relational database thing and how that works, as well as how to have only one Client Book field show without having to go into the layout every time. I would like to simply have a way to select which field it is that I need from the Client Book table instead of either having to scroll through all of them to find it or having to change the layout every time.

 

Any suggestions?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use