Choose from a conditional list

[Emma in England]

My database is for organising and formatting text for various publications (sale leaflets etc). I have to expand the version I built for a single publication to allow many publications, many users.

I've created a table called Publication which will hold Publication_ID (auto enter serial), Publication_Name and Publication_Status (Active or Inactive).

When a user logs in for a session, I want them to be able to choose, somehow, from a list of names of active publications. If I can get that to work, I'll set a global to that publication id and use it to ensure they only see data that relates to their chosen publication.

But how do I do this? There is no meaningful relation between 'Publication' and 'Session' until they have chosen a publication, so I can't get the publication names to appear in a drop down list, let alone isolate the active ones.

I know this is my inexperience, there must be a way but so far I'm not seeing it. I'm reading Using Filemaker 8 from cover to cover, but so far a solution hasn't popped up (I'm only on p121!)

Any suggestions would be most welcome.

[Emma in England]

OK, I've sort of solved it:

I added a field to the 'Session' table with auto-entry 'Active'. Then I made a relationship between Session::Active and Publication::Status, and created a value list somehow or other, but it seems to work and only shows the active publications.

So the user can select their publication. Now I'm stumped again, but there's nothing like typing out the question to help find the answer.

[bcooney]

I'm probably missing something, but it sounds like you are making this more complex than it may need to be.

Why can't a user click a button from the main menu that goes to the Publication List layout and does a find on "Active" publications. Then they pick their publication, and go to its form view.

Also, I like using flag fields for keeping data such as "active" vs "inactive," because if you down the road change the words, it won't break anything. Therefore, status becomes a checkbox, with a value list of 1 and 0. On the layout, you can put field label text "Active" next to the checkbox (resize to only show one box).

What is the purpose of the Session table?

[Emma in England]

Flagging is a good idea, I'll do that now while I still can!

The user will be working in 3 main tables - Pages (i.e. one record per page, containing the raw info we are supplied by the customer for putting the page together), Products - one record per item, containing pricing and insurance details, and Text - one record per item, these are imported from Word documents and contain the descriptive text which is stripped down and has Quark tags attached to it.

When scripts are run, spreads of text output etc, I want filemaker to know which publication ID the current user is in, and include that in finds, export filenames etc.

It's not just a matter of being taken to the correct window. Or am I missing something?

Purpose of session table: Hmmm. I think it's partly a history (who worked on the job and when), and also to allow the user to choose a publication. I see what you mean - if all they're doing is setting a global, they don't actually need a session record. But I like the idea of the history.

Please understand I'm making this up as I go along, it really is new territory for me (but I LOVE it!)

[Fenton]

To me this sounds like a typical interface navigation issue. If they are presented with good "drill down" navigation from Publications, then they can get down to whatever details of that publication. You started off asking about Publications, but then you say the user will be working in 3 other tables. Somewhere in the middle you mention "Sessions", of which, as bcooney says, we have no idea.

You also say that you cannot show a list of Active Publications in a drop-down list, because there is no relationship. You can almost always produce a value lists, one way or another. If you create a "constant" field, in whatever table, a calculation, = 1, then you can create a relationship to the Publications Active field, "Show related values only, starting from" where you are.* That'll produce active publications (which hopefully have an ID?).

*If you're going to want to see this Value List from many different places, there's an alternative method. Which is to set the Storage of the constant calculation to Global. The relationship would be created from the Publications table occurrence, global calculation 1 to Active. That one value list would then be available to any other table, without additional relationships. Useful for things like "Active".

[P.S. Global storage of calculations. Was it added in FileMaker 8, or was it also in 7? I can't remember.]

Active_VL.fp7.zip

Edited May 25, 2007 by Guest
Global

[Emma in England]

I'm sorry my original question was so badly explained. It's the end of a long week. My learning curve over the last month has been very steep and I'm beginning to realise how little I still know. Filemaker is a hard topic to ask questions about as so much background information seems to be necessary.

The last month has also been one of the most exciting in my working life - starting to get to grips with writing a serious solution, I have no real background in databases, I'm a typesetter by trade. But I love what I'm doing now. It is very exciting, challenging and amazingly rewarding.

BTW, I have solved the problem pretty much as you described. And yes, of course my Publications have IDs - but the user will recognise the name. It is the ID that will track everything.

I'm trying! (very)

[Fenton]

In FileMaker 8 you can hide the IDs, by clicking (•) Show only values from 2nd field, in the Value List dialog. Be very careful that you NEVER have duplicate Publication names however; 'cause it will only see the 1st one.