Drop Down Lists containing certain data from other fields

[richardson.cr]

I'm really new to filemaker pro and am struggling to figure out how to create a drop down list that displays only certain data from another field in another form.

 

I have a contacts form in which I enter people's surname, first name and an instrument they play.

 

I then have a form for listing band members I want to perform on a show. I want to create a field for say "drummer" and a drop down list to appear with all the people in my contacts list who are drummers. In the list I want it to show their surname and first name.

 

I then want a field for "guitarist" and the drop down list shows me all the guitarists in my contacts and I pick one, and so on.

 

I can't figure out how to do this at all. If anyone could give me a simple explanation that I could follow I would really appreciate it.

 

Many thanks

[AHunter3]

Presumably in your Band Members table you have some field such as "Role". Create a calc field for each role for which you need a role-specific dropdown valuelist, e.g.,

 

Drummer, calc field, result type "text", = Case (Role = "Drummer", Band Member)

 

Guitarist, calc field, result type "text", = Case (Role = "Guitarist", Band Member)

 

etc.

[richardson.cr]

Many thanks for your reply, unfortunately it doesn't seem to work.

 

I have a contacts form I designed which basically has 3 fields, "Surname", "First Name" and "Main Instrument". I managed to figure out how to make main instrument a drop down list which contained all the instruments people play - so that works.

 

I then designed a new form called Show info. I did what you said and created a new field called "Drummer". I made it a calc field and when it asked me for the formula and says "Drummer = ", I put in:-

 

Case ( Contacts::Main Instrument = "Drums" ; Contacts::Surname)

 

Once I clicked OK it says

 

Unstored, = Case ( Contacts::Main Instrument = "Drums" ; Contacts::Surname)

 

I then changed the field setup just to drop dwn box but didnt change anything else (like when it asks display values from, I left it as unknown). I then clicked to have an arrow to expand the drop-down box.

 

I click OK, and when I go to my show info form and click on the Drummer drop down box it is empty.

 

Clearly my lack of experience is showing here, but any advice again would be really apprecaited.

 

Also, on my drop down list how can I make it display Surname and First name as the result, not just surname.

 

Thanks a lot for your time.

[AHunter3]

They can't be unstored; fields used to generate value lists have to be indexed, therefore stored. That, in turn, means that the calculations can't reference data in some other table.

 

Examine your structure. Your table that has name and main instrumen is the table that needs to have the calculation field.

 

You keep using the term "form" as if it meant "table". Do NOT create a new table just in order to create a new layout!

[richardson.cr]

Hi,

 

I don't follow at all I'm afraid.

 

Firstly in the example "Event Management" They use different tables for different layouts ie contacts. Why shouldn't you do this? And If you shouldn't How do I create different layouts? Do you mean you should only use 1 table for everything?

 

I did what you said though and created my "Drummer field this time in the same "Contacts Table" as where the Main Instrument and Name are stored.

 

I put in the calculation as you instructed. I then inserted the field on to my Show Layout Form (all other data on this form is from a "show table"). I made it a drop down box again but it won't drop down.

 

Is there any way I can attach the file to this thread, it may be a quicker way to see what I'm doing wrong. It's really frustrating!!

 

Are there any good books written about how to use filemaker for people who have never used it before, or any really simple tutorials to get people started?

[AHunter3]

Hi,

 

I don't follow at all I'm afraid.

 

Firstly in the example "Event Management" They use different tables for different layouts ie contacts. Why shouldn't you do this? And If you shouldn't How do I create different layouts? Do you mean you should only use 1 table for everything?

 

 

You should not always / necessarly use 1 table for everything, but you also should not always / necessarily use a different table for each layout.

 

If I have a layout that is a Human Resources checklist of stuff to check off as having been attended to for new employee Joe Blow, and I have a different layout that describes Joe Blow (first name, last name, soc sec number, office number, phone) and I have a third layout that shows all of Joe Blow's assigned not-yet-completed tasks, those 3 layouts are all going to belong to the Employees table. (The latter one is going to have a portal to the Tasks table, showing tasks related to the Employee, but the layout itself is "of the employee")

 

If I have a layout of Customers, that layout is obviously NOT going to be native to the Employees table, though.

[AHunter3]

Is there any way I can attach the file to this thread, it may be a quicker way to see what I'm doing wrong. It's really frustrating!!

 

If you join up & pay your annual dues here, you can post a file; and you can download any example files that folks here (me or otherwise) set up for you to examine.

 

It's worth it. Folks in here are very helpful, and to be able to show someone instead of only describing it verbally can make all the difference.

[richardson.cr]

Thanks for the advice, I'll join in a bit - think I'm going to need the help!!

 

I do have a couple of quick questions that I hope are simple!!

 

1. How do you put an icon on a button, and where to you find the icons? I notice all the buttons in the templates have nice pics instead of the grey box??

 

2. If you have a drop down box that gives you a choice of, for example, "3 piece band" or "5 piece band". Is it possible (and easy) to make it so that when you select "3 piece band" you get 3 drop down lists on your next tab to specify which performers they will be. And likewise if you select "5 piece" you get 5 lists on your next tab. Ie depending on which option you select in one list determins how many fields appear in the next?

 

3. On my attempt at a database so far, when you click a field, all other fields get a dotted line around them. Is there a way to stop this? I've tried giving the fields borders but it still does it!

 

Thanks again

[AHunter3]

1. How do you put an icon on a button, and where to you find the icons?

 

Most layout objects, including pasted-in or imported graphic objects, can be buttons. Fields can be buttons. Words (text objects) created in layout mode can be buttons. Right-click anything and assign it a button function.

 

I design all of mine, other than the standard greys, in Photoshop.

 

 

2. If you have a drop down box that gives you a choice of, for example, "3 piece band" or "5 piece band". Is it possible (and easy) to make it so that when you select "3 piece band" you get 3 drop down lists on your next tab to specify which performers they will be.

 

a) Let's leave TABS out of this. Tabs have nothing to do with it.

 

b) If you have selected "3 piece band" you could, theoretically, have 37 fields (to accomodate a hypothetical 37 piece band) and using a trick hide all but the first three fields. I could tell you how to do that, but instead I'm going to tell you not to do that, because you should not have 3 (or 5 or 37) fields for your band members; you should have a portal to another table, and in that portal just ONE field for Band member. Trust me on this.

 

 

3. On my attempt at a database so far, when you click a field, all other fields get a dotted line around them

 

When you've "entered" a record like that, all the enterable fields do tend to highlight like that. If you intend that those fields be user-editable, that you should be able to click into them or tab from field to field and enter data into them, my advice is "deal with it" — format your fields on your layout so that this appearance does not distract you, and accept it as "data entry mode's appearance".

 

For fields that should not be accepting direct cursor input, you can stop the behavior by setting Field Behavior to not allow cursor entry in Browse Mode.

[richardson.cr]

you should have a portal to another table, and in that portal just ONE field for Band member. Trust me on this.

 

 

Trouble is I need to specify who makes up my band, ie who the drummer will be for a particular gig, who plays keyboards, etc. For a 5 piece band therefore I would need 5 fields. How could I do it with just one?

 

I presume you mean when I select 5 pc band it opens another layout of a different table. I haven't learnt about portals yet and so have no idea how they work!

[richardson.cr]

Hi,

 

I know I've mentioned this above but I've hit a same problem again.

 

I have a table for recording info on venues that a band play in. One of the bits of info I need to record is the number of dressing rooms the venue has. My idea was to have a field into which I enter the total number of rooms the venue has. Let's say 5.

 

 

I would then like to be able to enter specific info on each dressing room ie whether it has a toilet, shower, it's size, etc.

 

So ideally I'd like to specify "5" as total number of rooms,

 

and then 5 rows of fields to appear below which let me specify the details of each room.

 

Obviously I would like this to be variable so if a venue has 10 rooms I get ten rows of fields below.

 

my row of fields would be "size", "toilet (Y/N), shower (Y/N), etc

 

I know it was mentioned above using another table for this, I just don't quite understand how I could get this to work in the same way I have described above.

 

Advice and any example you could give would be really appreciated. Many thanks

[Techphan]

I just did this for another thread in FM9. Is this what you are talking about?

[richardson.cr]

Hi,

 

I don't think it's quite what I need.

 

I have attached an example of sort of what I want.

Basically though it you were to pick say 4 as the total number of dressing rooms I want 4 rown to appear underneath to store the details for each of the 4 rooms.

 

Many thanks

[Techphan]

I set up a little example based upon the sample you posted.

 

I turned the entry fields you had a the top into Global entry fields and wrote a simple script to save the record (in the Booking Table) and then return you to a Global Layout with a portal to Booking based upon the Relationship Global::Search to Booking::Search.

 

I treated the database as if you were going to book engagements (events) for the performance group. For every booking ("gig") you will need a minimum of 3 records (in the Booking Table) to cover the "Main Artist," "Band," and Support staff. I added dates and the like.

 

If you want to look at ONLY the bookings for one event you can

1) Run a search in the Booking Table Layout

2) Type the location name in the Search field on the Global Layout