Join table related value list (still) not working

[Steven Cappiello]

This is weird.. it was working last night and after zero changes to the database.. it's not working, and I can't seem to get it to work again... arg

I have three tables:

1-Students

2-Seminars

3-Seminar Enrollments

In my "Seminars" table, I have a "Date" field which can be future or past,

and I created a "Current_Date_minus_2_weeks" calculated global field which I want to use in a self-join relationship in order to create a value list that only shows seminars that have occurred either within the last two weeks or in the future. The relationship is from "Seminars" to a T.O. called "Seminars_date_relationship".. and is that "Seminars:Date" is greater than or equal to "Seminars_date_relationship:Current_Date_minus_2_weeks"

I created a value list called "Future_Seminars_minus_2_weeks" which shows related Seminars:Date values from the context of "Seminars_date_relationship"

I thought this should work, since the global field is effectively a constant, and should relate to all seminars that have a date which is equal or later than two weeks before the current date.

But, my value list shows nothing !!

Could anyone tell me why ?

[RalphL]

In my "Seminars" table, I have a "Date" field which can be future or past,

and I created a "Current_Date_minus_2_weeks" calculated global field which I want to use in a self-join relationship in order to create a value list that only shows seminars that have occurred either within the last two weeks or in the future. The relationship is from "Seminars" to a T.O. called "Seminars_date_relationship".. and is that "Seminars:Date" is greater than or equal to "Seminars_date_relationship:Current_Date_minus_2_weeks"

I created a value list called "Future_Seminars_minus_2_weeks" which shows related Seminars:Date values from the context of "Seminars_date_relationship"

I thought this should work, since the global field is effectively a constant, and should relate to all seminars that have a date which is equal or later than two weeks before the current date.

"Current_Date_minus_2_weeks" calculated global field should be unstored caculated field. The relationship from "Seminars" to a T.O. called "Seminars_date_relationship" should be Seminars::Current_Date_minus_2_weeks <= Seminars_date_relationship::Date

[Steven Cappiello]

thanks Ralph.. then how should I define the value list ?

[Steven Cappiello]

arg.. it seems like I'm doing it right but the value list just won't generate..

so, i changed the calculated global field to a regular calculated field that is unstored

then, I defined the value list to be: based pull from this field: Seminars_date_relationship::Date from the context of the Seminars table.

isn't that right ?

[RalphL]

I made a little file to test this. This is the way I defined the Value list:

In File menu choose Define Value List

New Give it a name

Select "Use values from field" Click on Specificy Field

From the TO "Seminars_date_relationship" I selected Serial Number with Date as second field. Sorted on first field.

Click on "Include on relaated values starting from"

"Seminars"

I used different names in my little file and I make an unstored calculation field to display the Value List VL = ValueListItems ( Get ( FileName ) ; "Events" ). Events is the name I gave the Value List.

[Steven Cappiello]

Thanks Ralph, It seems to be working... almost... it works if on the Seminars layout, but not on the layout (in a portal) in which I need it to work. When I click in the field , I get a blinking cursor, even though the field is set to display values from the value list.

I'm trying to use the value list in a portal that shows records from a join table: Seminar Enrollments

I want to display this portal on the Students layout..

so, I have a portal showing records from the Seminar Enrollments table (the relationship is set to allow creation of records)

I entered the field Seminar_ID (FROM the Seminar enrollments table) into the portal.

Since the portal is on the Student's record, the new portal row should automatically insert the student_ID into the Seminar Enrollment record.

in any case.. when I click into the field, a new record is not created in the portal, (because a new portal row doesn't show up underneath)...

and get this... if I type in a Seminar ID that I know is correct... then exit the field, a new portal row is created.. AND THEN... if I click back on that seminar ID that I entered, THEN the value list shows up !!

so it seems that the field will not show it's value list until a new portal record is created in the join table..

any ideas why my portal is not creating a new record ?

or ideas about what is going on ??

[RalphL]

I don't know what is happening but it sounds like the related record has to be commited before you can use the popup.

I don't normally use the allow creation of related records with a portal. I use a button and a script to create the related record.

In the many to many relationship, I use a concatenated field of the two foreign keys as the primary key. This is not a calculated field. My script does the calculation and places the value in a global field which I use when I create the related record.

I posted a new demo of Separation Model of a Many to Many today. You may find that helpful.