value list questions

[poetnabotl]

Little by little, I'm understanding more about filemaker. I read the missing manual (and understood a whole, like, 2/3 of it) and it's slowly dawning on me that I need to buy the bible. Although I'm learning more about the program's functionality, I'm still struggling with how to actually make it do things.

Here's where I'm stuck at the moment. (Tomorrow it's bound to be something else.):

 

I have a pop-up in a Students table displaying related IDs from a CurrentClasses TO of a Classes table (related from students::g.status ("active") to currentclasses::status ("active" or "inactive"). Currently, "active" or "inactive" must be manually selected in the CurrentClasses table so it can be stored so it can be used in the value list relationship. I'd rather have it be a calc that references classes::endDate and get(currentdate) but can't seem to make that happen. So cest la vie.

 

1. How do I make it so that I can assign a student to an active class via the pop-up, but that, if the currentclasses::status field changes to "inactive", the pop up in the students table goes blank? I've tried making it a text field with a calculated value, If (currentclasses::status = "active"; classSelectionField; ""), but that don't work. It displays the classID regardless of currentclass::status's value. I've tried the same calc in validation, but given its non-boolean result, I'm not really shocked that it didn't work.

 

Hm. I swear there was a question #2 but I can't think of it and I won't push my luck.

I appreciate any help.

Jason.

[Ender]

Define a field in the interface table and set it to be globally stored, maybe call it gActive. This will be a filter on the parent side of a new relationship. Create another table occurrence of the Classes table, and define a relationship between the filter and the status field:

 

Interface activeClasses =

Interface::gActive = activeClasses::status

 

Then in Browse Mode, insert the text "Active" into the gActive field.

 

Now you can define a value list based on a field, using the new relationship (select the classID field in activeClasses, showing "Related values only" from the context of the Interface table).

[poetnabotl]

Ender, I appreciate the quick response.

I bet I didn't explain my structure very well, but I think I already have the set up you've described. The part that's been tricky for me (and perhaps I've got something wrong) is that, when the field in activeClasses::status switches from "active" to "inactive", I'd like the interface::classList field to then go blank, letting the user know that that student is no longer enrolled in an active class. As it is, the value list changes (which is rad), but the values that have already been selected (i.e. the classIDs to which each student has already been assigned) stay, even if the class is over. This may be a job for a script. What do you think? Am I misunderstanding?

Thanks again.

[Ender]

You're (necessarily) setting a local key with the value list (classID), and that cannot change automatically when something in the Classes table changes.

 

You could use a script to check/reset all the related Students when your class fields change, but I think a passive approach is better. Try adding an unstored calc to the Students table to be a status flag:

 

If(Classes::status≠"Active"; "Inactive Class")

[poetnabotl]

I think I will. A bright red reminder will probably be good for me anyway, because there are a bunch of things that need to be done around the office when a class ends. So I can manually reset when the flag comes up.

Because building this database is as much about learning how, to me, as it is actually building it, how would I have done the scripted reset of that field, if I'd wanted to? Does that question belong in a different thread?

Also, any ideas on how to do the same related value list trick that you outlined above except allow my classes::status field to be an unstored calc? Or a stored calc/lookup that will reevaluate when the file opens?

No sweat if you don't have time to answer. Thanks for your help.

[poetnabotl]

Strike that. Don't answer that scripting question. Usually I don't ask until I've tried it and gotten truly stuck. I haven't done that yet.

I'm still open to suggestions about the status field, though. smiley_cool

[Ender]

Match keys in relationships must be stored and indexed. I don't see why this is an issue in this case.

[poetnabotl]

Well, it's a feature I can live without, to be sure. But our classes are staggered. Of about 40 classes per weekend, I'd say one or two ends every week and one or two starts every week. The way we currently track that is that we have one excel sheet per class with a cell that says "over" in red when the class's "end date" cell is less than the current date. When I print out the week's sign-in sheets, I notice the "over", go into the excel workbook, save a copy (in case we need to reference old classes) and then pull the inactive class's sheet out of the workbook and save.

And I can continue to do it that way when we switch to filemaker, if I have to (set a flag to let me know when a class is over, then manually switch the "active" field to "inactive"). It just seems that, since every class has an "end date" and since filemaker can reference today's date, that it would save me a teeny bit of trouble each week just to automate it. The database would know when a class was over so I could spend more time goofing off at work.

And if I could base relationships on the class's automated status, it seems like a number of other things in the database could be taken care of as well (inactive sheets wouldn't print, for example. As it is, an expired class has to print once before I notice that it's over which seems to me a ridiculous waste of paper. )

Again, it's not a huge deal. I can set a flag in the classes table and then, every day when I get to work, flip through the classes to see if any classes should be set to "inactive", then do it manually. Or even make a new layout whose function is to show me classes whose status field needs to be switched from "active" to "inactive. Then all the other functionality, mentioned above, would work properly. Just thought there had to be another way.

Hope I'm explaining well enough.

[Ender]

You can relate directly to a date range using a date-to-range multi-criteria relationship:

 

Interface ActiveClasses =

Interface::cDateToday ≥ ActiveClasses::ClassStartDate

AND Interface::cDateToday ≤ ActiveClasses::ClassEndDate

 

In this case, cDateToday is an unstored calc on the parent side of the relationship using Get(currentDate).

[poetnabotl]

Awesome. That's exactly what I'm looking for. Seems so simple when you put it that way.

So, I'm trying to wrap my brain around this. I couldn't make a relationship...

 

Students CurrentClasses =

Students::g.active = CurrentClasses::cStatus

Where g.active's value is "active" and CurrentClasses::cStatus = if(CurrentDate

 

So, that didn't work. Or, at least, I couldn't use it to populate my value list (CurrentClasses::classID, evaluated from Students.)

But...

 

Students CurrentClasses =

Students::cDateToday ≥ CurrentClasses::ClassEndDate

Where cDateToday = Get(CurrentDate), unstored calc

 

...works great. Does it have something to do with the unstored calc being on the side of the relationship from which the value list is evaluating?

Is it that I couldn't make a relationship via an unstored calc or was it that I couldn't populate a value list via an unstored calc?

Anyway, your solution is really helpful, Ender. I appreciate it.

[Ender]

Students CurrentClasses =

Students::cDateToday ≥ CurrentClasses::ClassEndDate

Are you sure that's what you want? That will show classes that are no longer active.

 

Anyway, these relationships work because the unstored calc is on the parent (one) side of the relationship. You can use it for value lists, portals, or whatever, as long as you're looking at it from the context of the parent side.

[poetnabotl]

Ha. Typo from when I copied your text and then tweaked it. Didn't pay attention to who was greater than whom.

Thanks for the explanation, you've been really helpful.

One problem down, eighty six thousand more to go. On to confusing myself more!