VL based on self-join TO of a parent table - no values defined error message

[4Justme2]

I am able to find a number of examples on how to create conditional values list... but none with same type of relationships I need in my solution. I have a total of five tables and two TO in my database.

_PROGRAMS (parent table to all)

Events.PROGRAMS (TO of _PROGRAMS creating the relationship to generate the EventsONLYPrograms vl)

Services.PROGRAMS (TO of _PROGRAMS creating the relationship to generate the ServicesONLYPrograms vl )

_PROGRAMS_2_EVENTS (a join table)

_EVENTS (child table and layout where I need the EventsONLYPrograms value list to display)

_PROGRAMS_2_SERVICES (a join table)

_SERVICES (child table and layout where I need the ServicesONLYPrograms value list to display)

I have three forms in this database. The Programs form has just six records (used for testing purposes).

The enduser should be able to create a new record in the _EVENTS table, then pick the appropriate event from the drop-down value list based on the EventsONLYPrograms value list; the same type of situation when they are on the Service Forms layout pick only accessing the ServicesONLYPrograms value list. Upon picking the Program the appropriate related field, "Measures" should automatically populate from the the PROGRAMS table.

The value list and the related Measure field are not populating appropriately. Any help would greatly be appreciated. I'm attaching a copy of this tiny file for your review. I'm so completely stuck on this.

VLProblem.zip

[imoree]

The value list and the related Measure field are not populating appropriately. Any help would greatly be appreciated. I'm attaching a copy of this tiny file for your review. I'm so completely stuck on this.

The fundamental problem i see here is your ENTIRE structure is designed incorrectly!

you have calculated fields relating to calculated fields which are not even required for what you need.

A simple value list i have attached.

VLProblem2.fp7.zip

Edited April 5, 2012 by imoree

[Lee Smith]

The fundamental problem i see here is your ENTIRE structure is designed incorrectly!

Wow, that is rather harsh!

[4Justme2]

First of all, thank you so much for taking the time to look at this with me. It's actually one Program to many Events. But the solution you provided is certainly a nice simple one. Since the relationship is actually the opposite I'm finding I'm getting myself confused trying to transpose this solution. Given that, I'm trying to do everything in the opposite direction what what's in the zip?

My parent table will be PROGRAMS, the ID will be serialized. The child table will be EVENTS, with an appearance of the Prog ram ID as a foreign key. I'll place the value list drop down field on the PROGRAMS layout. I have a smaller about of Program related detail. The Event specific detail will contain a lot of data and a lot of records. I could place a portal to Events on the Program layout but really have way too many records to share the page with Programs. Given that, I need Events to be on it's on layout but with a dropdown value list based on Programs

An example of data contained in the PROGRAMS table: "Parenting Ed Classes"

At the Event table level there are multiple Parenting Ed Classes, for example. One might be named New Mother Program, Nurturing Program (all are specific events under the umbrella of the larger program called Parenting Ed Classes.)

On the Events layout I need a dropdown from the PROGRAM table based on ProgType. Another words, from the Events layout drop down list to ProgType I should only see a list of those programs that contain "Event-Based" in the ProgType field. I'll have a seperate layout for Services doing the same with ProgType = Service-based.

I've tried revising the zip file but I'm not getting anywhere. Your solution had the dropdown on the Events page but Events isn't actually the parent--it's the child. Can I have a dropdown of values from the Parent table displayed on the Child table? I know I can place a port of the child records (Events) on the parent (Programs) but can I have a dropdown list of values from the parent (Programs) appear on the child page?

[4Justme2]

Is there a way for me to do this or am I struggling over something that can't be done? One PROGRAM can have many EVENTS. I want the PROGRAMS to be the parent table. Within the table the enduser identifies whether the program is Event-based or Service-based. That's on the one side.

PROGRAMS

ID_PROG.pk

Program_Name

ProgType

On the many side

EVENTS

ID_EVE.pk

Event_Name

ID_PROG.fk (from drop down but on those values from ProgType that are Event-based)

Program_Name

On the Child Table, EVENTS the end user can create a new event and from a dropdown be able to see a list of only those Program names that are Event-based. Is there a way to get this result?

[imoree]

Wow, that is rather harsh!

]

yeah, sorry bout that. was having a hard day and that came out ... : ( :hmm:

[imoree]

Is there a way for me to do this or am I struggling over something that can't be done? One PROGRAM can have many EVENTS. I want the PROGRAMS to be the parent table. Within the table the enduser identifies whether the program is Event-based or Service-based. That's on the one side.

Give this a try for fundamental Understanding! BASIC!!

program_Events.fp7.zip

[4Justme2]

Excellent. Many thank yous once again. I see how this works, now. Tell me, would there be a way I could create a relationship between the PROGRAM table and the EVENT table based on the contents of ProgType = Event? Maybe it can't be done and if that's the case, that's still cool. But I know you can relate a table back to itself based on the contents of a field so that your value list is specific to that particular relationship. Which just makes me curious if there is a way set this up so that on opening the EVENTS layout the only drop down I need to supply because the relationship is only providing me with ProgramName with a ProgType of Event. I have no idea if that makes any sense. If FMP isn't meant to work this way, again, that's fine. But better that I ask and find out it's not doable than never to have asked at all and be wondering (or periodically trying to get something to work that is never going to happen).

[4Justme2]

]

yeah, sorry bout that. was having a hard day and that came out ... : (

No worries, I didn't take what you said the wrong way... but rather am appreciative that you are taking the time to help me with this. This is just an incredible FM community of people who are so willing to take the time to help newbies like me; and I thank you once again for your support. :hug:

[imoree]

relationship between the

PROGRAM table and the EVENT table based on the contents of ProgType = Event?

opening the EVENTS layout the only drop down I need to supply because the relationship is

only providing me with ProgramName with a ProgType of Event.

pretty much anything can be done in FMP as i am learning; I am not clear what you are asking here

[4Justme2]

Ok, let me try explaining it a different way.

I want to be able to enter my program information on one layout. I want to be able to go to the EVENTS layout or the SERVICES layout. When on the EVENTS the VL drop down should also produce those vaules should only show me programs that are service based.

i.e.

on the PROGRAMS layout

ID_PRO.pk, 1

ProgramName, Parenting Workshop

ProgType, Event

ID_PRO.pk, 2

ProgramName, Resource Library

ProgType, Service

When on the EVENTS layout, in my value list drop down I only want to see ID_PRO.pk, 1 (Parenting Workshop) as it is an Event (not a service)

When on the SERVICES layout, in my value list drop down, I only want to see ID_PROD.pk, 2 (Resource Library) as it is a Service (not an event)

It seems like this is a matter of establishing a relationship between the PROGRAMS table and the EVENTS (or SERVICES) table(s) based on if ProgType = Event or Service. I'm wondering if this can be done.

[imoree]

program_Events.fp7.zip

Maybe something like this?

[4Justme2]

By Jobe... that's it EXACTLY!!! Just the solution I was looking for. You are awesome Thank you sooooo much once again. This ties everything together for me so I can move forward. Thank you again.... mp

[imoree]

By Jobe... that's it EXACTLY!!! Just the solution I was looking for. You are awesome Thank you sooooo much once again. This ties everything together for me so I can move forward. Thank you again.... mp

YOu can tweak calculation even further if you like

Let ([

vl =  ValueListItems ( Get(FileName)  ; "Program Name")

];

Case

(

PROGRAMS::programType = "Event Based"  or  PROGRAMS::programType = "Service Based"; vl )

)

[4Justme2]

Ahhhh, I see. Another question. Is it possible for me to generate a value list of just Program Names where ProgType=Event Based) without the user having to pick the ProgType from the first field? Another words, only one value list--Program Name, but only those where ProgType=Event Based

I know how to do this when using a self-join on a table occurance where the value list appears on the same (parent) table. Getting this restricted value list on a layout based on a different context is driving me to drink.

[4Justme2]

I think I just need to do this the way you suggested above. I changed the way I'm presenting this data so the value-lists are displayed on the related table and all is good. Sometimes I get so focus on whating to do something a certain way... when it's never going to work that way.

All's good now on this. Once again, I appreciate all of your help. Take care... mp