Using value list to make field appear

[NikkiS]

Hi, I'm a newbie and just beginning to learn about scripting in FM.

I have about 63 fields that I would like to use a value list to pull up, so that we don't need to scroll through them all to find the one we need.

Basically, would like one field that contains the value list, and the one next to it would then populate with the field requested.

Are there any suggestions?

[bcooney]

Let me restate...just to see if we're on the same page. You have 63 records that each have a different value in a field. For example, in the table, "States" in the field Name, in the first record you have "Alabama," in the second record, "Alaska", etc. for fifty records.

I'm confused by one field contains the value list and the next field uses it? Typically, a value list is created from the contents of one field in a table, and then is used to populate another field in another table. My suggestion, read about value lists in the online help.

[Søren Dyhr]

First off is Barbara making a nice subtle hint to you relational structure, 63 fields requiring the same valuelist needs to get split up into separate records.

Secondly doesn't it have much to do with the scripting you initiated your message with!

What you apparently are after is a way to strain the valuelist down to a certain focus or scope to prevent the user waste time getting the desired value. The topic as such is called dynamic valuelists, and includes some sort of relational definition or a vast amount of layout trickery which never can be generalized.

I would therefore strongly suggest to take the plunge into the relational definition of a valuelist, as well as getting a firm grip on the relational approach, which your post revealed you were slightly stranger to.

You should never be impressed by the cute and clever ways to deal with such matters, you need to understand what's going on!

--sd

[bcooney]

Here's a sample from the Database Pro's site of relational value lists (aka conditional value lists, hierarchical value lists).

Conditional Value List

[NikkiS]

Thank you for your suggestions - your time is truly appreciated! I guess what made sense in my head, not so much on paper. Let me try to clarify some information.

I inherited this database, so it's structure at this point is not something that I had any part in creating. I'm at the point of trying to simplify what is already there.

What we call a database, is just a table, with 163 (or so) fields. The person who set this up had no idea what they were doing or how to do it, so they treated it like an Excel spreadsheet.

These fields are fine:

Type

Category

Update

Person

Publication

Address

City

State

Zip

Zone

Phone

Fax

Email

Show

Notes, Notes on Call

Now - here's where it gets dicey: The following defined fields (obviously, not all are listed here) each contain unique information.

FoodPlay

BabyBites

Bingzy

Brain

Eagle

Hippo

I95

Infertility

Jewels

Now - there are many others, but for the sake of space, I did not include them.

What I would like to do is make it easier to get to each field, instead of having to scroll through so many. Whether a drop down or a different way, does not matter to me, just need a way to make accessing the information more user friendly, and less time consuming.

I hope I explained it better this time.

Nikki

[bcooney]

What I need to understand in order to answer your question is what you mean by "make it easier to get to each field." From where? Within Define Fields? On a data entry layout?

[NikkiS]

I need to get to them easier from a data entry perspective.

[bcooney]

Well, you have 163 different fields. I'm assuming that you have a layout designed that shows all 163 fields. What is the problem "getting" to them?

I think that your real problem is that you shouldn't have 163 fields. So, what are these fields for? What kind of data do they contain? What are your main tables?

I need context to give you a more thorough answer.

Edited November 13, 2007 by Guest

[NikkiS]

Each of the 163 fields is for each of the books that we work on. Because we have to update the information based on conversations pertaining to specific books, I think we have to keep those fields. What I would like to do is not have to scroll through so many to find the one field that I need depending on the media person that I am speaking to and the book that they are calling about.

[digitaljunkie]

I think what you want to do is have all the books in one table and the media contacts in another table, then you can relate the right books to the right people based upon a common denominator or 'interest'

that way you create a related portal on the contacts layout which will only show the books related to each contact.

You will need to factor in a few things though, a join table in order to relate the book and contact in many instances as I don't know if your notes are contact specific or book specific or even both. Creating a join table will enable both unique instances to have notes etc.

I may be wrong here but it does sound like your maybe over complicating the process slightly....

[Media Contacts] > [Join Table] < [books]

John > Unique Record < Jungle Book

Mary > Unique Record < Dummies Guide

Jane > Unique Record < Jungle Book

Jane > Unique Record < Haynes Manual

As you see jane now has unique (join) table record for 2 different books.

If Janes ID has a matching foreign key ID in the Unique record then the foreign key ID for the Book is present so your join table tells you this...

A record here is related to Jane (Foriegn Key) and at the same time is related to Jungle Book (Foreign Key)

But Also at the same Time John (FK) is related to Jungle Book (FK)

This then lets you represent the data in both tables or not depending on what you want to do.

It is what is commonly known as a many to many relationship, where many relationships occur in one or more places and relate the same data or records across many places or to many opposing records that they are related to.

Personally as you are taking the project on this is how I would approach it so that you future proof this example for later.

Now like I say, I may be completely off point here but it sounds like your saying the current state of the database is unrelated like a spreadsheet so you need to split the data out and make it related in order to restore some form of sensibility and not have to show 167 fields per record just becuase the data is unrelated and doesn't know what where and when.

Edited November 20, 2007 by Guest

[NikkiS]

Yes - I am very new at this. Though I have been working in FM for several years, I have never looked beyond the simple table that I inherited, so have no clue what I am doing.

I have separated out the contact names from the books(clients) and now have 2 tables. I have related the tables to each other via Contact ID field. Now that I have that, the next part is where I am lost.

What you suggested may work - but I am not sure.

Say Jane has received 15 of the books that we work with, But I only need to send her a completely new one, how would I get to that new field?

What I had been thinking was to use a value list (though I have since found that to be the wrong thing), so now I am thinking that a drop down list. The list would contain all the book field names, I could select the book I want to send her and it would bring up that field, so that I could make notes in it (such as when it was sent, if she is going to write about it, etc...).

I thought I could use a portal, but I found that I end up having to scroll there as well.

[digitaljunkie]

OK this is where scripting comes into play for you.

You would have a new book script this then goes off to the books table creates a new record and lets you fill in the details.

You click continue and the script then asks do you want to assign this book to media contact Jane.

You say yes and a record is created in the join table.

[NikkiS]

But wouldn't the record that it creates be different from the record that is already there?

[digitaljunkie]

No because the join table simply create the relationship and contains anything you want to be unique for that particular relationship.

If the book is already in the book table then simply create a relationship through the join table from the media contact Jane to the book.

If you want to send her that book you can at that point deal with that in the script at point of creating the relationship.

But you said a completely new book indicating it is new to the system, although either way its still the same, after creating the join record the script says do you want to send this book to Jane now? yes or no

If yes then do this....

in no then exit script, leaving the relationship built and ready to use later on.

[digitaljunkie]

if your new to join tables a good start woud be here...

As your new to join tables a good start would be here...

It should give you a better understanding of there purpose.

Watch the (conveniently) free section on relationships.

Click this link and whatch the tutorial section on RELATIONSHIPS

Many to many relationships part 1 and 2.

Edited November 20, 2007 by Guest

[NikkiS]

I posted this in another section, and I don't think that I explained myself the best.

I would like the names of the 63 fields to populate a value list or drop down menu. Then, by selecting the desired field I could pull up that field's record for a specific contact person. I want the actual field names to populate the list, so that selecting it will bring up that record.

Any suggestions?

[IdealData]

Sorry, does not compute.

You seem to be misunderstanding FIELDS and RECORDS.

A RECORD does not belong to a FIELD, so you cannot "pull up the field's record for a specific contact person".

Maybe try explaining your objective, instead of the method you want to use.

[NikkiS]

ok. Here is the whole thing.

I am trying to simplify the process of entering in data so have taken on the task of redesigning the whole database from one table with over 150 fields to an actual database with a couple of tables that are related.

I have the whole thing done EXCEPT the data entry part. We have 63 fields that are associated with a specific book title.

example:

Person Publication Address Book1 Book2 Book3

Each person is a record. These are my contacts. For each book, there are notes that let me know whether or not a book was sent to them, if they are going to write about it and when it may appear in their publication.

Because there are so many books that each contact person may be associated with, I want an easier way to get to a particular book field, instead of having to scroll through all of them to find the right one that I need to enter information into.

I had thought a drop list or value list or something similar would be a good way to do it, but I have found that to be nearly impossible.

So now I need another suggestion for how to do it.

[Lee Smith]

I merged your two Topics

[NikkiS]

thank you

[Fenton]

I'm going to more or less start from scratch, as digitaljunkie has already said much of it. You need 4 tables.

People

Books

PeopleBooks join (name it whatever seems best)

Notes

You will need to create a new join record in PeopleBooks (PB) for every unique connection between a Person and a Book. It will have the PersonID, the BookID, and other data such as a creation date that pertains to this unique connection. It will not contain data about either the person or the book, which properly belongs in their parent table.

A portal will exist on the Person layout, based on the relationship to the PB join table. Another similar will exist on the Book layout to the PB join table. So, when on a person, you can see their books, when on a book you can see its people.

In each of these portals the names of the person or book will appear by going 'through' the join table back to an instance of the parent table,* so you can see the name of the person or book in the portal, though it is still in the parent table.

Notes is a special kind of table. It's like a line items table, child table, but one with optionally 2 parents. You can have a note about a person, not particularly tied to a specific book, a note about a book, not particularly tied to a specific person, or a note about a book and a specific person. Or, you may simply want to see all the notes for a book, regardless of whether they have a specific person or not, or visa versa.

This can all be handled by having two ID fields in the Notes table, a BookID and a PersonID. Which gets populated depends on where you create the note from (from Books, always gets the BookID), and whether you then choose a secondary ID. Or, a button in the join table's portal could create a new Note with both IDs (it would have both in the row's record); this could open a new window, go to and create a new Notes record; most convenient.

*I say "instance" because, in an anchor-buoy relationship graph structure, it would not be the main table occurrence of the parent table, not the table occurrence of the parent table's main layout. That is how I'd do it, but it may be more than you want to think about right now. Anchor-buoy helps when later you want to extend the database.