Value List Mind Twister

[ron G]

I have been grinding on this 'non functioning' value list for over a month and no matter what weird / exotic / unexpected methodology I try, it still acts weird.

Please check it out at: http://ronaldogordono.googlepages.com/valuelist2

The database is there for the downloading. I appreciate your thoughts.

[comment]

It's not a value list problem, but a relationships problem. You are trying to create a new option transaction directly from Stock, without telling Filemaker which option should be the parent option of this new record.

Since there must be a parent option, and since the relationship between Stock and Options is allowed to create new records, Filemaker "fills the gap" by creating a new option to serve as the parent of the new transaction.

[ron G]

Thanks for the reply. (Every thought helps). I suspected that might be a problem so I changed the Layout to be based on OPTIONS. This results in the dreaded "[color:red]No Values Define" in the optionTransactdions::optionid.fk field.

In analyzing what you said, about FM "filling the gap", since each option is based on (related to) a particular stock and since each stock can have numerous options, how do I structure a OptionTransactions field to indicate the Option upon which a particular OptionsTransaction is based? (I am now trying to do it through the OptionTransaction::OptionID.fk as a popup into Options::OptionID.pk and Options::optSymbol

How do I work around that?

Edited July 12, 2009 by Guest

[bruceR]

I have been grinding on this 'non functioning' value list for over a month and no matter what weird / exotic / unexpected methodology I try, it still acts weird.

Please check it out at: http://ronaldogordono.googlepages.com/valuelist2

The database is there for the downloading. I appreciate your thoughts.

That's because you have the relationship backwards. The OptionTransaction should be linked direct to Stock by a field you do not yet have in that table: stockid.fk

stockoptions.zip

[ron G]

WOW! It works as I wanted it to work. Thanks for your insight.

I am still somewhat perplexed as to WHY it works. Specifically, OPTIONTRANSACTIONS are based on OPTIONS. And, OPTIONS ARE BASED ON (related to) STOCKS.

According to your working relationship diagram, EVERYTHING (including OptionTransactions) is related to Stocks.

Perhaps OPTIONTRANSACTIONS::OptionID.fk is finding the right OPTIONS::optsymbol because the LAYOUT already has the right STOCKID.pk selected above the portal and then OptionTransactions just uses that ID to find the relevant OPTIONS::optsymbol?:?

I think this is the crux of what I don't understand.

Anyway, I really appreciate your insight and help.

ron

[comment]

since each option is based on (related to) a particular stock and since each stock can have numerous options, how do I structure a OptionTransactions field to indicate the Option upon which a particular OptionsTransaction is based?

You must select the right option before you can create transactions for it. You could place the selected option's OptionID in a global field in Stock, and base the creating portal on a new relationship:

Stock::gOptionID = OptionTransactions 2::OptionID

or simply go to the selected option's record and create the transactions there, using the existing relationship. In either case, the OptionID will be inserted automatically into the new transaction record - you do not need a value list for this.

[ron G]

Thanks BruceR. This worked great!