Value List Help!

[JimmyCarl]

Hi--

I have a FM7 DB with several tables. One table (admin) allows a user to enter in details about a program, including individual fields which indicate all the days during the year the program will operate.

I have a second table in the DB which users will be able to access to enter in positive attendance days for students. I would like the users to be able to see a value list of all program available dates (defaulted to todays date) which will allow them to edit/enter past attendance only for the dates that the program operates. I would like to be able to get these dates from the admin table in a relationship.

FM7 does not directly alloe the creation of a value list with values from more than 2 different fields (I will have 20-30 date fields). I have tried some calculation fields/scripts with little success. Is there a way to do this?....Is my logic wrong?...should I be trying a different angle ??...

Please let me know!

Thanks,

JimmyCarl

[Fenton]

I would guess that your logic is not very relational. Whenever you find yourself creating field1, field2, field3, etc., it's time stop and think, "Could this be a field its own table?" Because then: 1. You only need 1 field, and 2. You can do things easily, such as a value list.

[You could also use a repeating field. I seldom use them for real data, having been bitten before by the difficulties of working with them. For a value list this would be sufficient. But not for a relationship.]

These dates (in their one field) would be a separate table from the Program Details, related by ProgramID if you have more than 1 program. It would be easy to add, edit or delete dates. They're just records.

[JimmyCarl]

Fenton,

Thank you for the quick response --and yes--there was a mistake in my relational logic! However, I do not think that my explanation was entirely clear. What I still cannot do is the following: In separate table(s) I want a way for an admin to enter dates that a program operates (as you helped with) and the name of the program and some other details (in a nother related table). In the USER table (attendance clerk) I would like for them to be able to SELECT the name of the correct program and then have VALUE list of all available dates for THAT program autmatically pop-up. Getting a value list of programs (thru a relationship) to pop-up in a value list in the user DB is NO problem for me -----getting a value list of all DATES associated with THAT program to pop-up as a value list in the user table is a MAJOR problem for me ----

[Fenton]

I said: "These dates (in their one field) would be a separate table from the Program Details, related by ProgramID if you have more than 1 program."

So, you have a Programs table. It has ProgramID (auto-entered serial ID), and ProgramName; and whatever else.

Then you have a ProgramDates table. It has 2 fields (at least): Date_ and ProgramID.

There is a relationship from Programs to ProgramDates, based on ProgramID, with "allow creation of related records" in ProgramDates.

A long skinny portal in Programs, with 30 rows, based on the relationship to ProgramDates, with the date field. Type a date in the first empty row. This creates a record in ProgramDates WITH the correct ProgramID (you don't need to see or worry about it).

A value list in any other table, the user file (Attendance; I don't know exactly what it is), is "filtered" by a relationship from a ProgramID field to the ProgramDates table's ProgramID. Only dates for that program will show.

You would also need a way to choose the ProgramID, probably using a value list to all ProgramIDs, also show ProgramName (currently will only sort by ProgramID, 'cause of a bug; but that's probably what you want anyway). Or use a portal showing all ProgramNames, via a Cartesian [X] "all matches" relationship.

[JimmyCarl]

Fenton,

Thanks for your help ---and I do get and did do the first 5-6 steps from your previous post... I still get lost in the "filtered" part of your post .....I still cannot get a value list of dates, for speicific program, to show up in the ATT _ENTRY table in the DB.....it must be something silly and idiotic .....I jus don't get it!

I have attached the file so you can take a peek ----if ya have the time ---

BTW--use to live in SanDiego down LaMesa way -----years ago ---

for_fenton.fp7.zip

[Fenton]

Try this.

ProgramsDates.zip

[JimmyCarl]

Fenton,

Well....it took me 1/2 hr. to figure out "how" it works----by going into layout and value lists--and moving things around. But I really do understand it now! I would have never gotten the "filtered" concept --so ty very much for that. The field over field thing is very ingenious and I would have never gotten that either......How do ya learn this stuff??......

thanks so very much....

[Fenton]

You learn it by spending a lot of time, by looking at other people's work (who've already figured it out), mailing lists, example files, books, and magazines, and trying it yourself. There's no escaping the last, 'cause you're bound to come up with your own custom problem. One learns to be patient :-)

Related or "conditional" value lists (I call 'em "filtered", it seems more descriptive) were a big deal when they came out, back in 5.0 (I think; it was glitchy at first about sorting on the 2nd field, just like it is now in 7).

Here's another variation on the theme. Rather than using a drop-down list, this shows the program's dates in a portal. The dates "float" as buttons (the portal and line are the same color as the background). So they can just click one to set the date.

I had to create a new table occurrence. Because the existing has "allow creation," which you need from Programs for new dates; but caused a blank button at the bottom. I made another TO without creation.

ProgramsDates2.zip